Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open CSV as text?
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. -- Thanks & Best Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open CSV as text?
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. "LeeL" wrote: 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. -- Thanks & Best Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open CSV as text?
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 wrote: 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. -- Thanks & Best Regards -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open CSV as text?
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" wrote: 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 wrote: 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. -- Thanks & Best Regards -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to open CSV as text?
You mean that after you change the extension to .xls and open it in excel, that
your data looks like that, right???? (just curious) LeeL wrote: 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" wrote: 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 wrote: 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. -- Thanks & Best Regards -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open text file as text not numbers | Excel Programming | |||
from VBA open text file in default text editor application | Excel Programming | |||
open some txt files ,find text , copy the text before that to a single cell | Excel Programming | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |