Excel Number Formatting

[AL725]

Hi,

I’m working on exporting data to Excel in a tab delimited format (TSV) from
the AS400. When the numeric or alpha fields are separated by tabs, then each
field will show in a separate column in Excel. I noticed a couple things
about the numeric data that gets exported to Excel and I was wondering if you
knew if it could be fixed or not. One is that when a numeric value is too
big for the column (for example, 8863166678), it will show up as 8.86E + 09.
However, when you widen the column in Excel, the number shows correctly.
Another issue I’ve seen is the number of decimal places that show for numeric
data. The user has to actually format the column while in Excel to show the
correct number of decimal places. When the data first shows, it is all in a
general format in Excel, so Excel doesn’t know how many decimal places the
number should have.

Do any of you know if there is any way to indicate in a TSV file how the
numeric field should be formatted in the Excel spreadsheet?

Thanks for your help.

 

[JLatham]

AFIK, you cannot specify the format of a field in a TSV file, which other
applications, such as Excel, refer to as CSV files. By definition these
files are simple ASCII files with some designated character, usually the
comma, a tab or the pipe being the field separator and no 'markup' tools are
available for them.

If your data always has the same fields exported/to be imported into Excel,
then you could record a macro while formatting up the individual columns
after an import and use that macro in the future to format the data after
importing it.