How does OpenText work?

Posted by James Cronen on August 14, 2000 1:38 PM

Hi all,

I have an application that reads and writes CSV
files. Naturally, I'm using Excel (97 for Windows)
to quickly edit and save CSV files.

My problem comes when I try to import data back into
.xls format from a .csv file; I've been opening the
.csv file using the Workbooks.OpenText method,
selecting the region in the .csv file to be copied,
then using Copy and PasteSpecial (values) to move the
values into the .xls worksheet. (I actually do want
to do further analysis, so it's not acceptable just
to save the .csv file as a .xls file.)

The Workbooks.OpenText method works okay for almost all
my needs. However, I'm having a bear of a time working
with dates. I'd like to open my .csv file and have all
fields appear as text. Essentially, I'm just using
Excel as a text editor in this way. However, even
when using OpenText and requesting (the FieldInfo
parameter) that every column be parsed as text, the
data are still being posted in Excel as dates!

This would work if you could format the column as text
before putting data in it, but seeing as Excel is
opening the file for me, there's no chance.

And, of course, just to throw another wrench into the
works, I need to work both in European (dd/mm/yyyy) and
American (mm/dd/yyyy) date formats. That shouldn't
be too much of a problem for me, as long as the dates are
loaded in as text.

The data in the column coming in are going to be of
all types, so blindly formatting as dates isn't
acceptable (I don't want the number 5 coming into Excel
as 01/05/1900). Also, I'd like to preserve (visual)
formatting where possible, so I really need to use
PasteSpecial Paste:=xlValues instead of just Paste.

Anybody have any ideas? I'm just about at wit's end
on this one.

Thanks in advance...

Jim.

Posted by James Cronen on August 15, 0100 8:00 AM

Hi Michael,

Thanks for the advice. Yes, there are a set number
of columns in, and I was able to verify this "feature".
I should be back on my way with a little re-writing of
the code which imports and exports files.

Excel is a great program, but I hate it when it tries
to "help" me by assuming certain things. Especially
undocumented assumptions.

Again, thank you -- without your help, it would have
taken me forever to figure that out!

Jim.

Posted by Michael Liu on August 14, 0100 2:39 PM

Is there a set number of columns coming in?
Found an interesting "feature" of excel.

Running:
Workbooks.OpenText FileName:="test.csv", DataType:=xlDelimited, _
Comma:=True, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
will pull in dates in cols 1-3 even though I specified those columns to be
text. It tries to be smart after seeing the .csv extension.

But running:
Workbooks.OpenText FileName:="test.000", DataType:=xlDelimited, _
Comma:=True, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
on essentially the same datafile, with a different unrecognized extension
results in dates coming in as text if in the first 3 cols.

So, a potential solution is to rename your csv files to some other extension
and use similar code to get everything in as text. (You'd have to expand
the array element to format each column that is coming in.)

If your csv files are being generated by some program, then you could
use the Name function to rename them.
Ex: Name "H:\test.csv" as "h:\test.000"

Hope this helps... Hi all, I have an application that reads and writes CSV