Output Data to CSV format
It is often useful to be able to output data from your Datafile databases into a CSV file which can then be opened in Excel, Lotus 1-2-3 or some other program. This is a relatively simple operation that should take only a few minutes to set up.
There are two places in which you can set up data interchange, but the process is the same for both. You can use Report Generator by selecting an empty slot, providing a title, choosing the Datafile database to use, and selecting Data Interchange as the report type: or you can go into Database Systems, Datafiler Database Utilities, Data Interchange Options and Define Output Data in ASCII format (you can also choose DIF format if your 3rd party program requires it). Provide a heading and a definition pathname (this can be anything you like) and the Datafile database you want to use (this must be an existing .dfd file).
The next prompt is for the 'external file pathname'. This is the name of the CSV file you will create so you should give it a name that is descriptive of the data but does not contain spaces - for example SALESACCOUNTS2003.csv.
Most of the rest of the prompts on this first screen can be ignored, but an explanation of them is provided here in case your 3rd-party program requires special settings.
| Text items with quotes: | encloses any text items (X-type items) in inverted commas |
| Separator ASCII character | defines what character separates the different columns of data. the default, 44. is a comma, space is 32 - use F4 on this field for a full list |
| Compressed | dictates whether spaces in the data items are preserved or not - this may not be noticeable when you open the file with another program |
| CR between records | dictates whether a Carriage Return character is used to separate the output records |
| LF between records | dictates whether a Line Feed character is used to separate the output records |
| End of data ASCII character | allows you to choose what ASCII character to use to signify the end of the file |
| Ignore ASCII code characters | specifies which (if any) characters in the file to be ignored |
| Sequence | 1 outputs the file in
record number order (see article 5 How do I use selection
criteria in Datafile? for further explanation. 2 outputs the file in prime index sequence - this is the main key data item for the database -on a sales accounts file this would be the Account Code data item) |
| Output the record number | dictates whether the record number is output as a separate column |
You can also choose whether to use selection criteria as you can on any normal report. The 'selective records' prompt allows you to choose whether to apply selection criteria or not, 'allow run-time changes' dictates whether or not you can change these criteria when you actually run the output, 'fixed record range' restricts the output and the criteria to a certain section of the database, and 'save run-time changes' allows you to choose whether any changes to the selection criteria made at the point of running the output are remembered for next time.
The 'automatic run - no user prompts' setting dictates whether the user who runs the output gets the opportunity to confirm the definition pathname, the datafile database pathname and the pathname for the CSV file itself.
The last setting on this screen is whether to output the datanames as the first record. This is usually very helpful as it will put the datanames (eg account, address-1, postcode) in the first row of the CSV file so you effectively already have column headings for a spreadsheet and you can clearly see what each column represents.
The next screen offers more sequencing options, so you can choose to sort the ouput by ascending order of balance, or descending order of turnover etc.
The final screen is where you define what actual data will be sent to the CSV file. There are 160 boxes numbered 1-160. The numbers on the left of each column represent the left-to-right sequence of the columns of the CSV file. The boxes on the right of each column allow you to choose which data items in the Datafile database you want. You can use the F4-select key to choose from the full database.
In this example the items selected are:
1 - ACCOUNT
2 - NAME
3 - ADD1
4 - ADD2
5 - ADD3
6 - ADD4
7 - POSTCODE
8 - TELEPHONE
11 - BALANCE
30 - T/OVER-PERIOD
31 - T/OVER-YEAR
You can select all 160 items in the database automatically when prompted on this screen, but this is rarely necessary or desirable. Once you have filled in which items you want you can run the output by going back into your Report Generator report and selecting 'Run' or by going to 'Run output data in ASCII format' in Datafiler Database Utilities. When opened in Excel this sample file looks like this:
