Download/Import from Tab Delimited - Formatting of Data

[Ash]

My office does a download from a mainframe application to get data into
Excel. The data is put into a tab delimited and this the file Excel reads
for the import/download.

Quite of bit of the data is financial data which contains leading zeroes.
Currently, to preserve the leading zeroes, we put an apostrophe at the front
of each field (in the tab delimited file, so this is how it opens in Excel).

Is there a way to send it the data to Excel all as text so that the leading
zeroes will be preserved without having to use an apostrophe?

Thanks for your thoughts/help/comments!
Ashley

 

[Joel]

My office does a download from a mainframe application to get data into
Excel. The data is put into a tab delimited and this the file Excel reads
for the import/download.

Quite of bit of the data is financial data which contains leading zeroes.
Currently, to preserve the leading zeroes, we put an apostrophe at the front
of each field (in the tab delimited file, so this is how it opens in Excel).

Is there a way to send it the data to Excel all as text so that the leading
zeroes will be preserved without having to use an apostrophe?

Thanks for your thoughts/help/comments!
Ashley

 

[Ash]

Hi Joel, Thank you kindly for your response.

Unfortunately, the data isn't pulled into Excel via the import tool. The
"coversion" from tab delimited to Excel happens behind the scenes and the
Excel version of the report is what's available for a user to open. Meaning,
after we send the report for download, the next option we have is to click a
web link that allows us to Open/Save the Excel version of the report. The
report that we open is the one that was read from Tab Delimited file and it
is already put into the columns it needs to be in, but with the leading
apostrophes.

I thought this issue might be resolved by having the default format set to
text instead of general, but I haven't found anyway to do that.

 

[Joel]

The spreadsheet must be set to the proper format before data is put into the
spreadsheet so the leading zeroes are preserved or the import methods need to
be told to keep the zeroes (like the import tool). the question is what is
the behind the scene method. I know a number of ways that it can be done but
which one are they using and how easily can it be modified. Is it a Query,
or a macro. The server may be opening an instance of Excel and putting the
data into the spreadsheet?

 

[Ash]

Yes, the server is opening an instance of Excel and putting the data into the
spreadsheet.

 

[Joel]

The server need to add on e instruction

Activesheet.Cells.numberformat = "@"