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
===============================================================================

1. First you need to set windows to not hide file extensions if it is not already set.

   In windows explorer go to Tools>Folder Options>View and untick the box next to "Hide extensions for known file types"

2. Rename your file and change the extension from CSV to PRN.
3. 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.
4. The Text import wizard will now start, this varies a bit from version to version of Excel, however, 
   1. the format is delimited rather than fixed length, 
   2. the delimiter is a comma
   3. 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.

Accounting, Pay as you go Sage 50 Support, Excel Reports, Sage Reports, Sage 50 3rd Party integration and more!

This Sage 50 import website is copyright Making I.T. Happen 2022