Sage 100 Visual Integrator: Importing Comma Separated or ‘CSV’ files.

The Sage 100 Visual Integrator module can import external data into many objects within the application. This is often used in new client implementations to convert data and for clients that want to integrate data into Sage 100 from external applications. Visual integrator can handle many types of source file data. The comma separated or ‘CSV’ file is one of the more commonly used source file types we encounter in data importing.

CSV files are simply files where the fields are delimited or separated by a comma. The CSV file is popular since a Microsoft Excel file can be saved directly to a CSV format. Clients will often use Microsoft Excel to manipulate data and save it in a CSV format. Clients using external systems that they would like to integrate data into Sage 100 will often times provided exported data in a CSV format. Read Excel Tips for Sage 100.

Using the Visual Integrator tool users can map fields in the import job to columns in the CSV file. This is a much easier exercise than importing of the older fixed position files where the mapping had to determine the starting position and length of a field in a string.

Here are examples of the Visual Integrator Job and CSV source File. You can see the mapping of the column position to the related column in the source file.

Column Mapping

Sample of source file with comma separators. The Sage 100 field is mapped to the column in the source file using the “Col/Pos” field in the import definition.

While CSV files are a popular format there are some considerations to keep on mind when using a comma separated format.

Commas within a field such a company name field will cause the import to fail the record since it will see the comma in the name as a delimiter and start importing into the next field from the comma in the name. Eventually data will be imported into a validated field and the record will fail. The common way around this is wrap text fields in quotes. You can see an example of this in the first vendor record in the source file example.

Another common problem clients encounter happens when CSV files are opened in excel for editing prior to import. For example leading zeros in zip codes are dropped. Do not open the file directly with excel. Open a blank excel workbook and use Data/Get External Data from text source. The import wizard will allow the user to define columns types prior to importing into excel. The zip code for example would be defined as text type field. The data can then be edited and saved to a CSV file.

In conclusion the comma separated file remains one of the more popular file formats used to import data via Visual Integrator in Sage 100 due to its ease of use with Microsoft Excel. Keep in mind Visual Integrator supports other file formats such as Microsoft Excel and other delimiters such a pipe delimiter so you are not limited to wrestling with some of the idiosyncrasies of a comma separated file format.