Delphi ASCII Text Import/Export

• Using TBatchMove
• Using TTable to view an ASCII file
• Using Delphi's Text-File I/O Routines
• Using 3rd party components
• see also:
  • Delphi Programming Main Index
  • Importing Batch Data

Using TBatchMove:

• Steps:
  • create a SCHEMA file:
    • open Notepad or other text editor
    • create an ASCII text file of same name as file to be imported or exported except name extension as "SCH" as this will be a schema file for the BDE ASCII driver to use to configure the ascii file procedure
    • format for schema file is as follows:
      • [xxx] where xxx is the name of the ascii file
      • FileType=[Fixed,Varying]
      • if varying then need to add the following lines too:
        • Delimiter=" (or Delimiter=#9 if tab delimited)
        • Separator=,
      • CharSet=ascii
      • Fieldy=FieldName,FieldType,NumberOfCharacters,NumberOfDigitsAfterDecimal,Offset
        • where:
          • y = field number (an integer)
          • FieldName is <= 25 characters not including: {}(),.[]!|>-
          • FieldType = [Char,Float,Number,LongInt,Bool,Date,Time,Timestamp]
          • NumberOfCharacters:
            • must be <= 20 for numeric data types
            • must be maximum number of characters for date, time, timestamp
          • NumberOfDigitsAfterDecimal:
            • currently doesn't work so can leave as 0
          • Offset:
            • applies only to fixed-length records (make it 0 for delimited files)
            • no. of chars from beginning of record to where this field begins
      • example:
        • [CNS]
        • FileType=Fixed
        • CharSet=ascii
        • Field1=CNS_ID,LongInt,3,0,0
        • Field2=Lab_ID,Char,10,0,5
        • Field3=Sample_Number,Char,15,0,18
        • Field4=Material,Char,15,0,36
        • Field5=Method,Char,8,0,54
        • Field6=Weight,Float,6,4,64
  • In Delphi project form:
    • add 2 TTable components, one will be source table & other the destination table, set properties:
      • Databasename = location of files
      • Exclusive = true
      • TableType = ttASCII for source Table if importing, for destination table if exporting
      • Tablename = ascii file or database table depending on whether source/destination as above
      • optionally, create a filter to restrict which records will be used
    • add a TBatchMove component and set its properties:
      • source = source TTable
      • destination = destination TTable
      • mode = [batCopy to create a new table, batAppend to append records]
      • optionally, set mappings if you want to rename fields or only import some fields
    • call the TbatchMove execute method and procedure will run.
      • NB. if exporting to ASCII and a SCH file was not used, the BDE will export as fixed length by default and create a SCH file automatically which can be used later if that file needs to be imported.

Using TTable to view an ASCII file:

• limitations:
  • there is no support for indexes or functions that use them, but you can use Filter
  • you must open it for exclusive use (ie. Exclusive = true)
  • you cannot use a TQuery
  • you cannot delete records
  • you cannot insert records but you can append them

Using Delphi's Text-File I/O Routines:

Using 3rd party components:

• FITCO's freeware Tascii2DB (dbcvt.pas):
  • no need to create a SCH file as this functionality is accessed via setting properties, no need for TBatchMove
  • allows:
    • use of onParse event to:
      • modify imported data & calculate values for each record
    • use of onBeforePost event to:
    • creation of error files
    • progress bar
  • problems:
    • if delimited files with ", fields with uneven number of " characters generate an error
    • dates must be converted to dd/mm/yy[yy] as mmm format creates error