R
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.
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.