Making I.T. Happen
Accounting and Information Technology, Assistance and Support
Excel conversion problems with CSV data
When opening CSV files with Excel there are several problems.
One of the biggest problems is that because Excel handles the CSV format and
we are used to excel we tend to use it to manage the data in our CSV files, unfortunately
Excel is a little too clever, it makes a few mistakes in opening files, for
example if it sees the Customer account reference JAN001 it will think this is a
date and change it to 1st Jan, thing like nominal codes with leading zeros, it
will convert to a number and loose the leading zeros.
How to open a CSV file in Excel without having
it auto convert data and mess things up
- First you need to set windows to not hide file extensions if it is not
In windows explorer go to Tools>Folder Options>View and untick
the box next to "Hide extensions for known file types"
- Rename your file and change the extension from CSV to PRN.
- Open your file buy using the Open or Crtl-O option in Excel, you will need
to change the file tyPe filter to all files to be able to see the file now
the extension has changed.
- The Text import wizard will now start, this varies a bit from version to
version of Excel, however,
- the format is delimited rather than fixed length,
- the delimiter is a comma
- the final stage allows you to select the format for each column of
data, do NOT use General, this is automatic formatting, select Text for
all the important columns (Nominal codes, Account Codes, Descriptions
and so on.) Setting text for ALL columns if you are not sure will work
fine if you are unsure.