Workbooks.OpenText Method / FieldInfo
in [Programming]
|
From: Volker Jordan on 4 Jan 2010 10:26 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
From: JP Ronse on 4 Jan 2010 12:36 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" <VolkerJordan(a)discussions.microsoft.com> wrote in message news:70D360B1-8F4C-486C-994E-8C63753BDF0C(a)microsoft.com... > 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
From: Volker Jordan on 5 Jan 2010 06:41 "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" <VolkerJordan(a)discussions.microsoft.com> wrote in message > news:70D360B1-8F4C-486C-994E-8C63753BDF0C(a)microsoft.com... > > 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 > > > . >
From: Volker Jordan on 5 Jan 2010 06:54 "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.
From: goshute on 6 Jan 2010 17:43
On Jan 5, 6:54 am, Volker Jordan <VolkerJor...(a)discussions.microsoft.com> 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 |