Mapping data to source file

You can map your data to a source file in several ways:

  • Put data into a spreadsheet file and create a source file from that.
  • Create a source file by using data pulled from a Microsoft Access database.
  • Write a data mapping program to map your data and save it as a source file.

When creating a source file from a spreadsheet program such as Excel, each column of the spreadsheet is considered a new field. When you save the spreadsheet as a CSV file, a comma is placed after each column. If a particular field is blank, a comma, or column is still required to indicate the presence of the field, even though there is no value for it. For example, if you are loading balances and do not have a balance for a specific period, there must be a blank column in your spreadsheet for that period's balance.

Use these guidelines to map data. Not all guidelines are identified.

  • See the Table Definitions documents, export file descriptions, data file text, database definitions, and CSV file definitions for detailed information about each field in an interface file (such as field order, type, and size). For information about how to build export files and generate data file text and database definitions, see the Landmark Technology Documentation.
  • Determine the order of data in the source file:

    If you do not use headers at the beginning of a CSV file, then you must include all of the fields in the order specified. For those fields that have no data, include the blank field so that you account for each field. If you use headers to identify columns in your data table, you can place the column headers at the beginning of the CSV file preceding the data, on line 1. The file then can contain only the applicable fields. When you run the dbimport load utility, use the -n option to indicate that there is no header line. For information about dbimport, see Running the load utility. For additional information about this utility, see the Landmark Technology Documentation.

    To export an empty CSV file with headers at the beginning of a CSV file, you can use the dbexport command with the dump --dumpempty parameter. This only works when there are no existing data records with the report. If there is data the data is exported by the dbexport command as normal.

  • If a field is optional, blank, or zero:

    Use a comma to indicate the presence of the field, even though you do not specify a value for it.

  • If a field is signed:

    The sign must be the farthest left character in the value string. For example, a signed amount field containing the value $5.82- must be represented as -5.82. A blank or plus sign (+) indicates a positive number, and a minus sign (-) indicates a negative number.

  • If a field requires percentage rates:

    Specify percentage rates as decimals (for example, enter 10% as .10).

  • If a field requires alphanumeric values

    Enclose alphanumeric field values in double quotes.

  • Mapping the date field will vary depending on the platform where the database file or CSV file will reside. In the Infor databases, dates are stored in ISO format: CCYYMMDD.
  • Unique ID fields are system-assigned values. Leave them blank.