MrExcel Publishing
Your One Stop for Excel Tips & Solutions

import text-delimited file (.CSV)


Posted by Philippe LEBLANC on January 30, 2002 3:53 PM

Is there any way to put columns format instructions
(as well as colums headers) in a text delimited file
(.csv) to avoid '12 a' to be retrieved by excel as
'12:00 am' in a cell !#*@? Haaaaaarggh ! HELP!!!


Posted by Richard S on January 30, 2002 4:32 PM

Is Excel treating 12 a as 12:00 am? When you do your import, specify the column as text as opposed to general, and it should work OK
Richard

Posted by Philippe LEBLANC on January 30, 2002 5:26 PM

Thanks Richard, but i need a solution convenient for several different columns (result of a different database queries) which could be date, amount or text without specifying the end-user which one is to be formatted with appropriated format... !#*@? Haaaaaarggh !

Posted by Barrie Davidson on January 30, 2002 6:32 PM

Philippe, what about recording a macro and using that (assuming that the file name and layout remain the same all the time) to open your CSV file?

BarrieBarrie Davidson

Posted by Philippe LEBLANC on January 31, 2002 2:11 AM

Thanks Barrie, the problem is that the layout don't remain the same all the time (depending
on the queries) this is why i can't use a macro!

any suggestion ??????????????????

Posted by Barrie Davidson on January 31, 2002 6:10 AM

What about one macro for each query? So if you have ten queries, you have ten macros.

BarrieBarrie Davidson

Posted by Mike G. on January 31, 2002 11:52 AM

I have the same problem. If I save a file with the .CSV extension (something I create in notepad for example), and order the data properly, by default, Excel will be the app of choice when you open the CSV file by double-clicking it.

It is essentially not an import, it is simply opening a CSV file. THE PROBLEM is that Excel tries to enforce, arbitrarily, it's own formatting on each cell once the file is opened.

Unfortunately, the side effects are MISTAKES. The worst is when excel drops leading zero's on zip fields. There seems to be no workaround for this. Your problem is essentially the same problem I'm having.

SO - does anyone know how to force EXCEL to actually apply field formula instructions from a simple CSV file???? Not an import, no macros, but a simple CSV file?

Posted by Philippe LEBLANC on January 31, 2002 2:36 PM

Barrie, i can't maintain a hundred of macros, and put them on the end-user's local installation of Excel, don't you ???.

The need i have is to know if there is any way
to force format columns in the text-delimited file .CSV

Posted by Philippe LEBLANC on January 31, 2002 2:44 PM

i could only find this way forcing excel
to accept number as text:
;colum;;colum;colum;="number here";colum;colum
(this applied too for road numbering, ex: '12 a')

but i search for a secure solution for each
type of data (formated date, number or text)
to be insert in cells !!!

Posted by Barrie Davidson on February 01, 2002 6:13 AM

Okay, a hundred macros would be unreasonable (I now see the light!). The only way to force Excel to read your example (12 a) as text in a CSV file is to put quotes around it. So it would look something like this:

1,Barrie,"12 a",Philippe
2,Vince,"10 p",Brian

Does this help you out?
BarrieBarrie Davidson

Posted by Philippe LEBLANC on February 01, 2002 4:57 PM

Thanks Barrie, see my reply to Mike

Add to your suggestion ->
change these lines:
1,Barrie,"12 a",Philippe
2,Vince,"10 p",Brian
by these:
1,Barrie,="12 a",Philippe
2,Vince,="10 p",Brian
and it will work, but is fastidious
to set in Brio SQR report code.