Import file formatting

1 minute read time.

Often you need to import Sage 500 data using built in Import utility in multiple modules (GL, AP, AR, IM).  The data is imported as ASCII records in flat files using either fixed length or record delimited (using a specified character) source records. By convention, files containing fixed length records have the extension ".asc," while the files containing delimited records have the extension ".del." . However the data source that you are dealing with is usually in Excel spreadsheet. All Import utilities have access to Help Center where you can review the required record layouts but they are all lined up in column and your import records need to be lined up in a row (either fixed length or record delimited). This is an example how they look in Sage 500 Help Center:

In order to prepare your spreadsheet you will need to flip-flop data in an Excel worksheet. Excel offers a handy Transpose option that will quickly take care of the task and turn columns into rows and eliminate the need to rekey data. So to format your source file correctly and place the header in each column correctly (‘Import Table Field Name’ from the above screenshot) instead of rekeying do the following:

Start by selecting and copying your entire data range (‘Import Table Field Name’ column). Click on a new location in your sheet, then with right mouse click select  Paste Special and then check  the Transpose box, as shown below:

Click OK, and Excel will transpose the column and row labels and data, as shown here:

You aren't limited to using the Paste Special | Transpose option to rearrange multiple rows and columns of data. It works just as well when you need to turn a single row of labels into a column, or vice versa.