I've seen several questions on the Message board relating to CSV imports, so I figured I would share the CSV import routines I wrote some time ago.
These routines have the following advantages over the CSV import routines that are part of Alpha5.
- Imports can be defined by the end-user in the runtime.
- If there is an error in one of the input lines, the routine will report the offending line and continue the import. The Alpha5 import will abort and not indicate what line has the error.
- The import configuration is tested against a selected number of sample lines before the import is started.
- Any issue is reported and remains visible in the import configuration screen.
- Import configurations can be saved and reused.
- CSV values can be appended directly to existing tables.
- Duplicates can be removed.
- Values can be imported to memo fields
The routine has the following limitations.
- May be more work to implement.
- A little slower than the built-in A5 routines. There is a sample file of 1000 values that you can use to gauge its speed.
- This was part of a larger package so it requires a fair number of support routines.
- The first line must contain field names.
- Only Commas can be used as field separators
- Quoting values are optional but must be done if the values themselves contain commas.
- Values cannot contain carriage returns and/or line-feeds. The Import tool will not properly process files formatted in this way. It will interpret the carriage-return line-feed as the end of record/line even if it is inside quotes. Unfortunately Excel will produce files in this format.
Attached to this message is a sample application with a single table and form that demonstrates these CSV import routines.
CSVimport.jpg
CSVloads.zip
These routines have the following advantages over the CSV import routines that are part of Alpha5.
- Imports can be defined by the end-user in the runtime.
- If there is an error in one of the input lines, the routine will report the offending line and continue the import. The Alpha5 import will abort and not indicate what line has the error.
- The import configuration is tested against a selected number of sample lines before the import is started.
- Any issue is reported and remains visible in the import configuration screen.
- Import configurations can be saved and reused.
- CSV values can be appended directly to existing tables.
- Duplicates can be removed.
- Values can be imported to memo fields
The routine has the following limitations.
- May be more work to implement.
- A little slower than the built-in A5 routines. There is a sample file of 1000 values that you can use to gauge its speed.
- This was part of a larger package so it requires a fair number of support routines.
- The first line must contain field names.
- Only Commas can be used as field separators
- Quoting values are optional but must be done if the values themselves contain commas.
- Values cannot contain carriage returns and/or line-feeds. The Import tool will not properly process files formatted in this way. It will interpret the carriage-return line-feed as the end of record/line even if it is inside quotes. Unfortunately Excel will produce files in this format.
Attached to this message is a sample application with a single table and form that demonstrates these CSV import routines.
CSVimport.jpg
CSVloads.zip