Data Loader File Format
The import file must:
Be a non-empty text file in either a fixed-width columnar format or a comma-separated values (csv) file format.
Begin with the text AASHTOWareProjectDataLoader in the first line, or AASHTOWareProjectDataLoader, csv if you are importing a csv file. The first line should contain no other text.
Be organized so that each subsequent line in the file corresponds to a single record for an entity (see the Example File below).
Approach
The best way to ensure that you build the import file with all field names spelled correctly and in the correct order is to start by exporting that entity as a sample of data you’d like to see. That way, you’ll know the format that the system expects. For more information about exporting data, see Exporting Entity Data and Exporting Agency View Data.
If you plan to use a csv file when importing your data, then be sure to select csv format when exporting a sample file.
Use a text editor, such as Windows Notepad or Notepad++, to open the exported csv sample file.
Note: Do not open the csv file using Microsoft Excel. Microsoft Excel will convert any date data types to a format that is incompatible with the Import process.
Use the format of the sample csv file to create the structure for your import file.
Keep the first row of the sample file as is.
For the second row, add the header row of the entity you want to import.
After the header row, add comma-delimited data rows with fields listed in the order specified in the header row.
To ensure that you import exactly the data you need:
You must account for all the fields in the entity as defined in the Data Loader metadata or the header row from the export of that entity. You must enter data for each required field. For fields that are not required, you must either enter data or add a comma to be used as a placeholder.
If the data in the comma-delimited data rows matches all of the key values of an existing record, the system will update all fields in the existing record except for system-generated ID fields. Any value that is blank will be imported as a null value.
Note: Because fields like CreatedDate, CreatedBy, LastUpdatedDate, and LastUpdatedBy are system-generated, you do not need to add data for them to your import file, even if they are required fields. Instead, you can leave them blank, with commas for place holders. When importing your data, the system will add the name of user importing the data, along with the current date, to these fields.
By default, an update overwrites all of the entity's fields, including overwriting existing values with null. If you do not want the system to overwrite existing values with null, select the Ignore Blanks option when importing your data.
Helpful Hints
When building a csv file to import data, you should know that:
It’s important to use a text editor, such as Windows Notepad or Notepad++, to open a csv file that you wish to import.
Note: Do not edit the csv file using Microsoft Excel. Microsoft Excel will convert any date data types to a format that is incompatible with the Import process.
In the Business Metadata Summary, you can review the Base or Custom Metadata for the entity to locate the required fields. When reviewing the metadata, most required fields have the IsRequired rule.
If you are importing a csv file that contains header rows, the import process identifies the header rows and ignores them.
The import file can contain records for multiple entities.
When a file includes both parent and child records, import the data in hierarchical order, so the parent records occur before the child records in the file.
When importing nesting entities such as subcontracts, the line of text for each nested record must end with the characters ,+ (comma and plus sign). This instructs the import not to save the record until after the next line. For example, to import a fund with multiple reference funds, each funding record line must end with ,+ because the funding percentage value must total 100 for a successful fund import.
If you use a fixed-width columnar format, and use the default OverWriteFieldsWithBlanks option, the system might expect a value to be placed in the last space in the record. For example, if the total record length is 616, and the last populated space in a record is column 110, the record may be rejected by the import. If the last field is a text field, you can correct this by placing a dummy value, such as a period or an X, in the last column.
If you use the fixed-width columnar format to import data, the record must match the expected length for the entity as defined by the fields in the record. The first field of every record is the Record ID of length 30 that contains the entity ID associated with the record. Each of remaining fields in the data row follow the order of the header row and spaced according to its specified column width.
Order of the entity's fields in the Data Loader metadata file is generated from the base metadata file and custom metadata when the -generatemetadata argument is passed to the program. Fields of Binary Large Object (BLOB) type and the Created By, Created Date, Last Updated By, and Last Updated Date fields are not included in the definition. If base and custom business metadata rules define different field lengths, the field's length is defined by the custom metadata.
Note: Because the record format for an entity is based on the structure of the entity as defined by business metadata, when the metadata is modified (for example, a new field is added to Project), you must update the import record structure for the entity to match. Agencies using the Data Loader function will make corresponding changes to the programs that they use to create the import files.
Field Type |
Description |
Text Field |
Text field length is defined by the Business Metadata's MaxAllowed argument of the MaxLength validation rule for the field. Text fields are right-trimmed only to preserve leading spaces and should be left-aligned in the record. |
Integer Numeric Fields |
Integer field length is defined by the Business Metadata's Precision argument of the IsNumber validation rule for the field. The value can be left or right justified. |
Decimal Numeric Fields |
Decimal field length is defined by the Business Metadata's Precision argument of the IsNumber validation rule for the field. The decimal should be included in the import data. |
Date Fields |
Date field length is eight and the value is required to be in YYYYMMDD format. |
Date/Time Fields |
Date and Time field length is fourteen and the value is required to be in the format YYYYMMDDHHMMSS with 24-hour time. |
Boolean Fields |
Boolean field length is one. Any of the following values indicate true: T,t,Y,y. Any of the following values indicate false: F,f,N,n. |
Longitude Fields |
Longitude field length is 15. The required format is DDD:MM:SS.ss, which corresponds to three decimal places for degrees, two places for minutes, and four places for seconds with two decimal places and the decimal removed. Additionally, a minus sign may be prepended to the value. The fields may be right or left justified. |
Latitude Fields |
Latitude field length is nine. The required format is DDMMSSss, which corresponds to two places for degrees, two places for minutes, and four places for seconds with two decimal places and the decimal removed. |
Example File
AASHTOWareProjectDataLoader,csv |
