convert xls to csv with quotes for alpha fields
 

What do you use to convert an XLS file to to a CSV text file, that contains
double quotes around every alphnumeric field, but NO double quotes around
numeric fields?

Thank you.

convert xls to csv with quotes for alpha fields
 

I'd use a macro.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range
Dim myRow As Range
Dim myCell As Range
Dim fNum As Long
Dim myRec As String
Dim myVal As Variant

Set wks = Worksheets("sheet1")
With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

fNum = FreeFile
Open "C:\output.txt" For Output As fNum

For Each myRow In myRng.Rows
myRec = ""
For Each myCell In .Range(.Cells(myRow.Row, "A"), _
.Cells(myRow.Row, .Columns.Count).End(xlToLeft)).Cells
myVal = myCell.Value
If Application.IsNumber(myCell.Value) Then
'don't add the double quotes
Else
'double up any embeded double quotes
myVal = Application.Substitute(myVal, Chr(34), _
Chr(34) & Chr(34))
myVal = Chr(34) & myVal & Chr(34)
End If
myRec = myRec & "," & myVal
Next myCell
Print #fNum, Mid(myRec, 2)
Next myRow

Close fNum

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

majobojrod wrote:

What do you use to convert an XLS file to to a CSV text file, that contains
double quotes around every alphnumeric field, but NO double quotes around
numeric fields?

Thank you.

--

Dave Peterson

convert xls to csv with quotes for alpha fields
 

Dave,

Perfect! Thank you so much.

John Rodrigues

"Dave Peterson" wrote:

I'd use a macro.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range
Dim myRow As Range
Dim myCell As Range
Dim fNum As Long
Dim myRec As String
Dim myVal As Variant

Set wks = Worksheets("sheet1")
With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

fNum = FreeFile
Open "C:\output.txt" For Output As fNum

For Each myRow In myRng.Rows
myRec = ""
For Each myCell In .Range(.Cells(myRow.Row, "A"), _
.Cells(myRow.Row, .Columns.Count).End(xlToLeft)).Cells
myVal = myCell.Value
If Application.IsNumber(myCell.Value) Then
'don't add the double quotes
Else
'double up any embeded double quotes
myVal = Application.Substitute(myVal, Chr(34), _
Chr(34) & Chr(34))
myVal = Chr(34) & myVal & Chr(34)
End If
myRec = myRec & "," & myVal
Next myCell
Print #fNum, Mid(myRec, 2)
Next myRow

Close fNum

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

majobojrod wrote:

What do you use to convert an XLS file to to a CSV text file, that contains
double quotes around every alphnumeric field, but NO double quotes around
numeric fields?

Thank you.

--

Dave Peterson