How to open CSV as text?

[LeeL]

Cannot get a CVS file to open formatted as text. Using below opens columns
with numbers formatted as general, losing preceding zeros. Any ideas?

Workbooks.OpenText Filename:="C:\Test\INVBAL.csv", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))

I tried to change the file extension to .txt, but the data is corrupted or
encoded when trying to open.

 

[Joel]

The fieldinfo is determinine the format of the cells and the format of the
cells in the worksheet. You are using 2 (xlTextFormat) in the fieldinfo
which says to use text formating but your cells in the worksheet are set to
general which is causing the zeroes to be eliminated. I would format the
cells in the worksheet to text before importing the data.

 

[Dave Peterson]

Once VBA sees that .csv extension, it ignores the fieldinfo parm.

Rename the .csv to .txt (either manually or in code -- or even copy it to a .txt
file in code), then use that new name in the .opentext statement.

..CSV files are plain old text files. If renaming it to text makes the data look
back, then your .csv file isn't truly a CSV file.

You could try opening the .csv file (or .txt if you've renamed it) in a text
editor (like Notepad) to see what the data really looks like.

And as a guess...

You may want to rename the file from .csv to .xls (or .xlsm or .xlsx or ...).
Maybe the file is really a normal workbook that someone just renamed
incorrectly.

 

[LeeL]

Your guess was right on. I had not thought to simply try and change file to
..xls, Thanks!

I had tried earlier to change the file type to .txt and notepad could not
read the data.
After changing it to .xls, I see the data like this
0447923","01 ","
0451800","01 ","
And I can mange it from here. Thanks again!

--

 

[Dave Peterson]

You mean that after you change the extension to .xls and open it in excel, that
your data looks like that, right????

(just curious)

Your guess was right on. I had not thought to simply try and change file to
.xls, Thanks!

I had tried earlier to change the file type to .txt and notepad could not
read the data.
After changing it to .xls, I see the data like this
0447923","01 ","
0451800","01 ","
And I can mange it from here. Thanks again!

--