automatic formatting on import of .DIF files

[Ken Ent]

I am extracting data from another application and outputting a .DIF file.
When I open this file in EXCEL, the cells are individually formatted such
that, even though the field is a text field, some of the data does not
contain "text" characters, only numbers. Excel automatically converts those
to a number format and thus removes any leading zeros, which renders the data
useless. Alternatively, if the data is numeric except there is an "E" in it,
EXCEL converts it to numeric with scientific notation -- again rendering it
useless. Preformatting the worksheet doesn't work because EXCEL opens a new
worksheet when you "open". I could use some ideas on how to work around this
problem. I only have limited output options from my application database
(unfortunately, ODBC is not one of them.)

 

[johnwy]

I am having the same problem with converting data from Excel to DIF
formatting. Were you able to resolve this problem and if so how did you?

 

[David Biddulph]

Name your input file as .txt (not as .csv). When you read it in through the
text import wizard, specify the format of each of the relevant columns to be
Text, rather than letting it default to General.

 

[Gord Dibben]

To open a pre-formatted workbook you must create a template workbook which
overrides the default workbook Excel provides.

Open a new workbook. Customize as you wish.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New or
CTRL + n

WARNING................Do not use File>New...Blank Workbook or you will get the
Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP