Need to change text to csv

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

My credit card company has an export feature. One of the options is ".csv
(Excel). This used to give me a nice clean spreadsheet with each column of
information in a separate Excell column. I guess it converted on the fly or
something. But now it seems to be a "comma delimited text file" In other
words, only the first Excel cell in each row has data in it, and it looks
like this in each row (the first row has headings).
1,"03/24/2005","HARVARD CS","Credit","$39.95". What can I do to get all
those comma delimited items into separate cells?
I've tried contacting the Credit Card company to no avail. Is the fact that
the first item is not enclosed in quotes significant?

This is actually a repost, as I tried to pursue this when I first discovered
it with last month's statement, but didn't get answers that provided a
solution.
 
Laurel

Comma delimited Text file and .csv files are one and the same.

Find the file you downloaded, either change the extension to .csv or .txt if it
is neither.
If you cant, then once the information is in Excel, select the column of data,
and in your Tool bars -- Select Data, Text to columns, make sure the comma box
is checked and you will have neat columns again



: My credit card company has an export feature. One of the options is ".csv
: (Excel). This used to give me a nice clean spreadsheet with each column of
: information in a separate Excell column. I guess it converted on the fly or
: something. But now it seems to be a "comma delimited text file" In other
: words, only the first Excel cell in each row has data in it, and it looks
: like this in each row (the first row has headings).
: 1,"03/24/2005","HARVARD CS","Credit","$39.95". What can I do to get all
: those comma delimited items into separate cells?
: I've tried contacting the Credit Card company to no avail. Is the fact that
: the first item is not enclosed in quotes significant?
:
: This is actually a repost, as I tried to pursue this when I first discovered
: it with last month's statement, but didn't get answers that provided a
: solution.
:
:
 
I made a small modification in the routine here:

http://www.mcgimpsey.com/excel/textfiles.html

Try:

Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "File1.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
sOut = .Cells(1).Value
For Each myField In Range(.Cells(1, 2), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, sOut
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
 
Thank you!
The file did have a .csv extension, but it wasn't being honored, apparently.
However your "Text to columns" suggestion worked great.
 
Back
Top