Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert spreadsheet to csv with double quotes | Excel Discussion (Misc queries) | |||
Quotes around fields in CSV | Excel Discussion (Misc queries) | |||
CONVERT Function Disappered in Excel | Excel Discussion (Misc queries) | |||
i have fields that are formatted for text that randomly convert t. | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions |