exporting to a CSV file

[Ravi Krishna]

Hi,

I have to write a tool to generate a CSV file from database. The database
is Informix and I will be using Perl DBD to write it in a CSV format. From
what I know of CSV file, I have to basically separate columns by comma.
However I have two problems:-

1. One of the columns is a DATE field in the format mm/dd/yyyy. I see that
when it saves it as CSV file, Excel has problem converting it correctly.
For some dates it shows the column as 10/28/2003 and for some it shows as
10 02 2003. I suspect the default date format in Excel is dd mm yyyy.
How do I force Excel to acknowledge that column as a date column with a consistent
format. One workaround I can think of is to download that column in yyyy-mm-dd format.
Excel in this case will always show it as a character field, solving the problem.

2. Another column has very large number. That is actually a character field containing
ticket number. Frequently Excel shows a value like 0167462030636 as 1.67462E+11.
Since the field must be viewed as it is originally stored, how to force Excel to
display it in its original format. One workaround I found is to prefix some characters
like TKT# to 0167462030636. This will force Excel to show it as a text literal, solving
the problem.

Is there an elegant solution. Specifically can Excel be told about the column type.

TIA.

 

[Tom Ogilvy]

Not unless you rename the file with a txt extension, then when you open it,
you will be taken through the text import wizard and in the last dialog, you
can specify interpretations for specific columns, such as the order of MDY
and whether to treat a column as text.