How to (bulk) import data from an ASCII (.txt) file into a database using ADO

.TXT to a database using one INSERT statement

For example:

asciidata.txt
FieldName1;FieldName2
1973;Zarko
1998;Zarko-Ozana
2000;Borna
2001;Karlo

Further more, let's say you have an MS Access (or MS SQL Server) database with a table named "TableName". Suppose there are two fields in this table: FieldName1 of type INTEGER and FieldName2 of type NVARCHAR.

Here's how to import data contained in the asciidata.txt file into the TableName table using one simple INSERT statement.

Connect a TADOCommand to your database using the ConnectionString or the Connection (TADOConnection) property. Assign the next string for the CommandText property of the ADOCommand object:

'INSERT INTO TableName (FieldName1, FieldName2) SELECT * FROM [asciidata.txt] in "C:\SomeFolder" "Text;HDR=Yes;"'

Call the Execute method of the ADOCommand object.

Note:

1. Replace asciidata.txt with the name of the text file you want to get data from.
2. Replace C:\SomeFolder with the name of the folder where the text file is saved.
3. The first row in the text file will be used as column headings/field names. Make column titles rom the text file match the table field names.
4. Each column with data must be separated with the list separator character that is used in the regional settings in the Control Panel.

Delphi tips navigator:
» Fixing the CheckBoxList ASP.NET Web Server control - adding Attributes to Items
« Fixing the "Duplicate resource" error

• 1
• 2
• 3
• 4
• 5