Flat File Utility

The Flat File Utility is used to import or export SDF or Flat Files. SDF stands for System Data Format. "SDF" or "Flat File" are interchangeable terms for the same file format. For more information on the structure of SDF or Flat Files, see Supported File Types.

Among other purposes, the Flat File format is used with the Electronic Data Interchange (EDI) system. EDI is a method of transmitting text files electronically, usually through a value added network (VAN). The Flat File Utility can help you prepare files for EDI purposes.

To reach the Flat File Utility click on the "SDF Flat File Utility" button on the bottom of the first Import or Advanced Export dialog box.

The following discussion is divided into two parts: Importing a SDF/Flat File and Exporting to a SDF/Flat File:

Importing a SDF/Flat File

Brief Summary of the Process. The important part of the process of importing a Flat File is creating a Template File. The template tells AnyOrder the number, size and type of fields found in each record of the Flat File. Once that's done, the records from the Flat File are imported into the template. The template now contains data found in the Flat File and can be imported into AnyOrder using an import profile as described in Importing. (The Template File is a dBase file.)

More Details. To import a Flat File, follow the green steps in the Flat File Utility dialog box:

1. Select a Directory. Select a directory where the Flat File that you are importing is located. If desired, AnyOrder has a subdirectory AnyOrder\My_Files where you can place the files used by the Flat File Utility. Or, you can select a directory of your choosing. Use the "B" (for browse) button to bring up a list of directories.

2. Create or Modify a Template File. In order to import a Flat File, you need to create a Template File. The Template File is used for several purposes: a) it tells AnyOrder how many fields are found in each record of the Flat File and their order; b) it tells AnyOrder the size of each of the fields, and c) it tells AnyOrder what kind of fields they are: number, character or date fields.

To Create a Template File, give it a name and then click "Create Template." The "Create Template" dialog box appears.

This dialog box is largely occupied by a spreadsheet. On the spreadsheet, type in the field names, size and type of field. For instance, let's say the first field of your flat file is a billing name and that field occupies 20 spaces in the Flat File. You would give the field a name. You can give any name you desire. Let's call it "Name." You would type in "Name" in the FIELD NAME Column.

Then in the FIELD TYPE column, type in "C" for character field (see below for the types of fields). Under the FIELD LEN column, type in 20 for the space it occupies in the Flat File. That's it. Continue entering names, lengths and types for until you've included all of the fields in the Flat File. Template fields must be entered in the same order as they are found in the Flat File.

Here are more details on each of the columns on the spreadsheet:

FIELD NAME. In this column, type-in the name of the field (10 letters or less with NO SPACES).

FIELD TYPE. In this column, type-in 'C' for a character field, 'N' for a number field and 'D' for a date field.

FIELD LEN. In this column, type in the length of the field. For importing, this is the size of the field in the flat file. If it's a date field, always use eight (8).
If it's a number field, count the number of digits. For an example: 3972 = 4 digits, and, thus, you would type-in "4" in the FIELD LEN column.

FIELD DEC. If the number has decimals, count the total number of digits PLUS one for the decimal. The number of decimals go in the FIELD DEC column. For example: 9354.74 = 7 digits (including decimal point). You would type in "7" in FIELD LEN and "2" in FIELD DEC. The FIELD DEC column remains empty for numbers without decimals. And it remains empty for character or date fields

FIELD IDX. Leave this column empty. If you want, you can type in an "N" to indicate "not used."

The template file is a dBase file which is AnyOrder's native file format. dBase files are indicated with a .dbf extension.

Once you create a Template File, you can return to it at a later time and modify it. To do so, type in the name of Template File in the blank: "Modify existing template file, indicate name." Once you've indicated the name, click on "Modify Template."

3. Import the Contents of a Flat File to a Template File. Once you've created the Template File, you're ready to move the data from the Flat File to the Template file. Type in the name of the Flat File. The Flat File should have been placed in the directory indicated at the top of the dialog box. Type in the name of the Template File that you've created based on the Flat File's structure. When you've indicated both names, click the "Import Flat/SDF File" button. The records will be moved from the Flat File to the Template File.

4. Edit a Template File (With Imported Records). After you've imported the Flat File records to the template, you'll want to view the Template File to make sure the process worked properly. This function allows you to do that--and it allows you to make changes in the imported data if needed.

Type in the name of Template File and click "Edit." A spreadsheet will appear. The fields will be listed across the top of the spreadsheet. Each of the rows represent different records. You can adjust the width of the columns by clicking and moving the line separating the names of the fields at the top of the screen. To view more fields, use the right scroll bar.

If you find that parts of fields are missing--or that some fields include portions of other fields, then you'll need to modify the Template File (see above). You've probably mistakenly entered the wrong field sizes.

If needed, you can make changes in the data. All changes that you make are saved automatically.

Another editor is available should you need do more extensive editing of the Template File. This is a small separate file editing program which includes search, search/replace, sort and other features. To reach it, click on the "Optional Editor" button. Since the Optional Editor runs as a separate program, be sure to close it before going on with other Flat File tasks. (Note that filter menu item on the Optional Editor is not applicable for editing a Template and you should avoid using it.)

Once you are sure that the Template File contains all of the data from the Flat File and the data is properly formatted, you are ready to import. To import, set up a profile for the Template File as you would any other file that you are importing. For more information on setting up a profile and the general process of importing, see Importing.

Once you've created a profile for the template, you can save the names of your Template and Flat File along with the other profile values. To do so, start at the first Import dialog box and select the appropriate profile name from the list. Click the Flat File Utility button on the bottom of the Import dialog box. When the Flat File Utility dialog box appears, enter the names of the directory, Flat File and Template File in any appropriate blank, and click "Save."

Exporting to a SDF/Flat File

Brief Summary of the Process. To export to a Flat File, you'll need to start in Advanced Export and export the desired AnyOrder fields to a Comma Delimited File. The fields exported to the Delimited File should be in the order that you want in the resulting Flat File. (For more information on exporting, see Advanced Export.) Once you've created the Comma Delimited File, then you're ready to use the Flat File Utility. When the Flat File Utility appears, the first thing to do is to create a Template File. The template tells AnyOrder the number, size and type of fields found in each record of the resulting Flat File. Once you've created the Template File, the next step is to export the records from the Comma Delimited File to the Template File. Lastly and the easiest step, the Flat File (and all the exported data) is created from the structure of the Template File.

More Details on Export to Flat File. To export to a Flat File, follow the blue steps in the Flat File Utility dialog box:

1. Select a Directory. Select a directory where the Template File and the Flat File that you are creating will be located. If desired, AnyOrder has a subdirectory AnyOrder\My_Files where you can place the files associated with the Flat File Utility. Or, you can select a directory of your choosing. Use the "B" (for browse) button to bring up a list of directories.

2. Create or Modify a Template File. In order to export to a Flat File, you need to create a Template File. The Template File is used for several purposes: a) it tells AnyOrder how many fields you want in each record of the Flat File; b) also tells AnyOrder the size of each of the fields, and c) it tells AnyOrder what kind of fields are coming in from the Comma Delimited File (number, character or date fields).

To Create a Template File, give it a name and then click "Create Template." The "Create Template" dialog box appears.

The Template dialog box is largely occupied by a spreadsheet. On the spreadsheet, type in the field names, size and type of field. Field names can be any name of your choosing. For instance, let's say the first field of the Flat File will contain a Billing Name and that field will occupy 20 spaces in the Flat File. Let's name this field "BillName." You would type in "BillName" in the FIELD NAME Column.

Next you need to determine the field type coming in from the Comma Delimited File. In the case of Billing Name, it's a character field, so in the FIELD TYPE column, we would type in "C." (See below for the types of fields).

Under the FIELD LEN column, type in the desired length of the field in the Flat File. We'll say that we want this first field in the Flat File to occupy 20 spaces, so we type in "20" in the FIELD LEN column. Continue entering names, lengths and types for until you've included all of the fields from Comma Delimited File.

Note that the fields in the Comma Delimited File and the Template File must match. You must have the same number of fields, and the fields in the Template file must be in the same order as they are in the Comma Delimited File. This same order carries over to the Flat File. Thus, it's important that when you first create the Comma Delimited File, the number of the fields and their order is same as what you want in the Flat File.

Here are more details on each of the columns on the spreadsheet:

FIELD NAME. In this column, type-in the name of the field (10 letters or less with NO SPACES).

FIELD TYPE. In this column, type-in 'C' for a character field, 'N' for a number field and 'D' for a date field.

FIELD LEN. In this column, type in the size that you want the field to occupy in the resulting Flat File. If it's a number field, count the number of digits. For an example: 3972 = 4 digits, so you would type-in "4" in the FIELD LEN column.

FIELD DEC. If the number has decimals, count the total number of digits PLUS one for the decimal. The number of decimals go in the FIELD DEC column. For example: 9354.74 = 7 digits (including decimal point). You would type-in "7" in FIELD LEN and "2" in FIELD DEC. The FIELD DEC column remains empty for numbers without decimals. And it remains empty for character or date fields

FIELD IDX. Leave this column empty. If you want, you can type in an "N" to indicate "not used."

The Template File is a dBase file which is AnyOrder's native file format. dBase files are indicated with a .dbf extension.

3. Export the Contents of a Delimited File to a Template File. Once you've created the Template File, you're ready to move the data from the Delimited File to the Template file. Type in the name of the Delimited File. The Delimited File should have been placed in the directory indicated at the top of the dialog box. Type in the name of the Template File that you've created to receive the data from the Delimited File. When you've indicated both names, click the "Export to Template" button. The records will be moved from the Delimited File to the Template File.

4. Edit a Template File (With Imported Records). After you've moved the Comma Delimited records to the Template File, you'll want to view the Template File to make sure the process worked properly. This function allows you to do that--and it allows you to make changes in the imported data if needed.

If needed, you can make changes in the data. All changes that you make are saved automatically.

5. Moving Data Out of AnyOrder. Create Flat File / SDF File from Template. Now that all the data is in the Template File, you're ready to create the Flat File. Type-in the name of the Template File. Next, type in the name of the Flat File that you'd like to create. Then click on "Create SDF File" The Flat File will created and placed in the directory that you indicated on top of the dialog box. If you'd like to view the Flat File, you can do so by opening it in a word processor.

You can save the names of your Template and Flat File. To do so, make sure that the export profile name that you want to associate the Flat File with is showing at the bottom of the Flat File Utility dialog box. Then click "Save." (If the wrong profile name is showing, exit from the Flat File Utility and select the appropriate profile name on the Advanced Export dialog box. Then return to the Flat File Utility. Type in the names of the Directory, Template and Flat File in any of the appropriate blanks and click "Save.")