# Converting Excel data to ASCII comma delimited text file format



## jsolo (Aug 15, 2008)

I need to convert Excel data into ASCII comma delimited text file format. I know that if I save as csv. that the data can be pasted into Wordpad with the comma delimiter, but how do I get the quotes around the data fields? 

Any help would be appreciated.

Thanks


----------



## slurpee55 (Oct 20, 2004)

jsolo said:


> how do I get the quotes around the data fields?


Not sure I know what you mean.
A csv file would have the data stored as (as an example)
1,ghas
2,dfaha
3,fkdfgyi
with a comma representing a column break.
Quotes aren't part of a csv file.
Are you wanting something like
"1","ghas"
"2","dfaha"
"3","fkdfgyi"
???


----------



## jsolo (Aug 15, 2008)

Sorry for not being clear in my question. Yes I am looking for a way to put my data into this format "name","age","weight","height",....

Thanks for your help.


----------



## slurpee55 (Oct 20, 2004)

I would probably just concatenate the values along with quotes as needed in a new column. For instance, if the data is in A and B, put " in C and in d and e have the combined data like this
1	a	"	"1"	"a"
which comes from 
1	a	"	=C1&A1&C1	=C1&B1&C1


----------



## jsolo (Aug 15, 2008)

Thanks for the help. I was just curious if anyone had an existing macro or another tool to help with this.

When I do the concatenate method and open the .csv file in wordpad the data looks like this:
 
"""Name""","""Height""","""Weight""","""Race""","""Age"""

It's adding 2 quotes per side. Why is that?

Thanks.


----------



## slurpee55 (Oct 20, 2004)

I would guess that in column C (in my example) you have "" rather than ".
The formula puts whatever is in C on each side of the words, so you are ending up with ""&Name&""


----------



## Rollin_Again (Sep 4, 2003)

Here is a sample macro that will write to a text file.


```
Sub WriteFile()

    Dim vNumber As Integer
    Dim vFileName As String
    vNumber = FreeFile
    
    'Change text file save location
    vFileName = "C:\Test.txt"

    Open vFileName For Output As #vNumber
    
    'Change Start and End Rows in Excel file
    For i = 1 To 3
    
    'Add additional columns as needed.  This example uses columns A-D only
    Print #vNumber, Chr(34) & Range("A" & i).Value & Chr(34) & "," & Chr(34) & Range("B" & i).Value & Chr(34) & "," _
    & Chr(34) & Range("C" & i).Value & Chr(34) & "," & Chr(34) & Range("D" & i).Value & Chr(34)
    
    Next i

    Close vNumber

End Sub
```
Regards,
Rollin


----------



## Aj_old (Sep 24, 2007)

Hi to every one
you could use this code in excel to get a single column with the text in the way you need it and after that to save it as text file

```
Sub ComaSeparatedText()

    Columns("A:A").Insert Shift:=xlToRight
    Columns("A:A").NumberFormat = "General"
    rws = Range("B2").End(xlDown).Row
    Cln = Range("B1").End(xlToRight).Column
    
    f1 = ""
    For i = 2 To Cln - 1
        f1 = f1 & "&"","" & """""""" &RC[" & i & "] &"""""""""
    Next
    f1 = "=" & Right(f1, Len(f1) - 6)
    Range("A1:A" & rws).FormulaR1C1 = f1
    Columns("A:A").Copy
    Range("A1").PasteSpecial xlPasteValues
    Columns("B:IV").Delete Shift:=xlLeft
End Sub
```
or you could use this one:

```
Sub ComaSeparatedText2()

    rws = Range("A2").End(xlDown).Row
    Cln = Range("A1").End(xlToRight).Column
    Range(Columns("A:A"), Columns("A:A").End(xlToRight)).Insert Shift:=xlToRight
    
    With Range(Cells(1, 1), Cells(rws, Cln))
        .FormulaR1C1 = "=""""""""& RC[" & Cln & "]&"""""""""
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Range(Cells(1, Cln + 1), Cells(1, Cln * 2)).EntireColumn.Delete Shift:=xlLeft
End Sub
```
To have all cells added quotation marks at begin and end of the each cell value, and after that just save it as comma separated values.


----------

