Workbooks.OpenText Method / FieldInfo
 

Hi,
after reading some posts about the OpenText method and the FieldInfo
parameter I want to ask a question.

Is it still not possible to open a csv file ( with .csv ending ) with all
columns declared as text?

Renaming the file to .txt is not an option.

Regards
Volker Jordan

Workbooks.OpenText Method / FieldInfo
 

Hi,

What do you mean with "not possible to open a csv file"? Can't you open the
file, is the parsing not corect or is the retrieved data not correct?

AFAIK, it depends also on your locale settings, e.g. separator is set to ";"
and the file is using "," or vice versa or another character. How is the
file opened when you do it manually?

You say that renaming the file is not an option. I have had the same issue
and renaming the file was the work around.

You can create a copy of the .csv file to a .txt file with:

FileCopy strFile, "path" & Left(strFile, Len(strFile) - 4) & ".txt"

with strFile is the .csv file.

After reading/importing the .txt file, you can clean it up with:

Kill strFile (the .txt file of course).

Hope this helps.





"Volker Jordan" wrote in message
...
Hi,
after reading some posts about the OpenText method and the FieldInfo
parameter I want to ask a question.

Is it still not possible to open a csv file ( with .csv ending ) with all
columns declared as text?

Renaming the file to .txt is not an option.

Regards
Volker Jordan

Workbooks.OpenText Method / FieldInfo
 

"JP Ronse" wrote:

Hi,

What do you mean with "not possible to open a csv file"? Can't you open the
file, is the parsing not corect or is the retrieved data not correct?

AFAIK, it depends also on your locale settings, e.g. separator is set to ";"
and the file is using "," or vice versa or another character. How is the
file opened when you do it manually?

You say that renaming the file is not an option. I have had the same issue
and renaming the file was the work around.

You can create a copy of the .csv file to a .txt file with:

FileCopy strFile, "path" & Left(strFile, Len(strFile) - 4) & ".txt"

with strFile is the .csv file.

After reading/importing the .txt file, you can clean it up with:

Kill strFile (the .txt file of course).

Hope this helps.





"Volker Jordan" wrote in message
...
Hi,
after reading some posts about the OpenText method and the FieldInfo
parameter I want to ask a question.

Is it still not possible to open a csv file ( with .csv ending ) with all
columns declared as text?

Renaming the file to .txt is not an option.

Regards
Volker Jordan


.

Workbooks.OpenText Method / FieldInfo
 

"JP Ronse" wrote:

Hi,

What do you mean with "not possible to open a csv file"? Can't you open the
file, is the parsing not corect or is the retrieved data not correct?

I can open the file, but all values with leading 0 e.g. telephone numbers
are gone.
0695155 - 695155. I have to declare some fields as text.

AFAIK, it depends also on your locale settings, e.g. separator is set to ";"
and the file is using "," or vice versa or another character. How is the
file opened when you do it manually?

Its the same. Manually the 0 are converted to numbers.

Workbooks.OpenText Method / FieldInfo
 

On Jan 5, 6:54*am, Volker Jordan
wrote:
"JP Ronse" wrote:
Hi,

What do you mean with "not possible toopena csvfile"? Can't youopenthe
file, is the parsing not corect or is the retrieved data not correct?

I canopenthefile, but all values with leading 0 e.g. telephone numbers
are gone.
0695155 - 695155. I have to declare some fields astext.

AFAIK, it depends also on your locale settings, e.g. separator is set to ";"
and thefileis using "," or vice versa or another character. How is the
fileopened when you do it manually?

Its the same. Manually the 0 are converted to numbers.

No, You can not import a .csv file with all columns defined as text
because Excel is trying to be helpful and defining what looks like
numbers as numbers.
I know of two ways to handle this.
Change the .csv to .txt
Insert a single quote ' as the first character in these fields forcing
Excel to handle as text.

Mike

Workbooks.OpenText Method / FieldInfo
 

"goshute" wrote:


Its the same. Manually the 0 are converted to numbers.

No, You can not import a .csv file with all columns defined as text
because Excel is trying to be helpful and defining what looks like
numbers as numbers.
I know of two ways to handle this.
Change the .csv to .txt
Insert a single quote ' as the first character in these fields forcing
Excel to handle as text.


The problem is, that I have to read csv data, that is not generated by
myself. So renaming and inserting a quote in not an option. I wonder, why
there is no hint in the documentation, that FieldInfo does not work with
files ending with .csv.

Volker

Workbooks.OpenText Method / FieldInfo
 

On Jan 7, 3:46*am, Volker Jordan
wrote:
"goshute" wrote:

Its the same. Manually the 0 are converted to numbers.

No, *You can not import a .csv file with all columns defined as text
because Excel is trying to be helpful and defining what looks like
numbers as numbers.
I know of two ways to handle this.
Change the .csv to .txt
Insert a single quote ' as the first character in these fields forcing
Excel to handle as text.

The problem is, that I have to read csv data, that is not generated by
myself. So renaming and inserting a quote in not an option. I wonder, why
there is no hint in the documentation, that FieldInfo does not work with
files ending with .csv.

Volker

Sorry Volker, I was not clear in my last post and I was out for a few
days. I learned how to handle these files after working with them for
years. You only have to do one of the two suggestions. Adding single
tick mark to the beginning of the field will force Excel to handle
that value as text when it opens the file. If you change the
extension to .txt, Excel will give you the import wizard where you can
select text for the column.
I normally handle these kinds of issues with VBA. I look in the
directory and rename the file with a .txt extension. Afterall
the .csv file is nothing more than a text file with the .csv
extension. Then when I open the file, either manually or with VBA, I
can define the data formats. There are lots of examples on this site
to read a directory and rename a file but I can post examples if it
helps.
Goshute