BorlandTalk.com

Guest · Posted: Wed Jun 14, 2006 7:35 pm Post subject: Slow import problem

Hello everybody!

I have to import text files (csv) of different structure (various
delimiters and text qualifiers) containing customer data. All data
needs to be inserted in an existing table. That table has 49 columns so
I have created a tool to assign the columns from the file to the
table's columns.

e.g.: filecolumn1 -> tablecolumn23; filecolumn2 -> tablecolumn5

Currently I am using a TADOConnection with
Provider=Microsoft.Jet.OLEDB.4.0 and a TADODataset to connect to the
file. Then a "while not eof"-loop steps through the Dataset and builds
an INSERT-SQL-Statement like "INSERT INTO mytable (tablecolumn23,
tablecolumn5) VALUES ('hello', 'world')" where 'hello' would be a a
value of filecolumn1 and 'world' value of filecolumn2

For 12000 records this takes like 25 minutes what is far too slow...

I searched for other importing methods and found BULK INSERT and
OPENROWSET.
For BULK INSERT I didnt find a way to assign my columns and OPENROWSET
throws an exception (Ad hoc access to OLE DB provider
'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this
provider through a linked server.) that I havent found a way to work
around without modifying server config oder registry which is no
option.
Other components than Delphi's ADO are also no option.

I hope I could explain my problem in a comprehensible way.
Any hints, solutions or questions are very welcome!

thanks in advance
Ingo

Riki Wiki · Guest

On 14 Jun 2006 07:35:28 -0700, ir2000 (AT) web (DOT) de wrote: