 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Ingo
Guest
|
 Posted: Mon Jun 19, 2006 8:11 am Post subject: Slow import problem |
 |
|
(reposted from groups.google.com to http://newsgroups.borland.com/)
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 schrieb:
| Quote: | On 14 Jun 2006 07:35:28 -0700, ir2000 (AT) web (DOT) de wrote:
Any hints, solutions or questions are very welcome!
Hoi Ingo
You need to repost your message on the Borland news server to make
everybody see it and possibly answer your message.
How to post to Delphi newsgroups:
http://delphi.wikia.com/wiki/Delphi_Newsgroups |
|
|
| Back to top |
|
 |
Vitali Kalinin
Guest
|
| Posted: Mon Jun 19, 2006 1:47 pm Post subject: Re: Slow import problem |
|
|
Take a look at "in" statment in Access SQL help.
"Ingo" <ir2000 (AT) web (DOT) de> сообщил/сообщила в новостях следующее:
news:44964f9a$1 (AT) newsgroups (DOT) borland.com...
| Quote: |
(reposted from groups.google.com to http://newsgroups.borland.com/)
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 schrieb:
On 14 Jun 2006 07:35:28 -0700, ir2000 (AT) web (DOT) de wrote:
Any hints, solutions or questions are very welcome!
Hoi Ingo
You need to repost your message on the Borland news server to make
everybody see it and possibly answer your message.
How to post to Delphi newsgroups:
http://delphi.wikia.com/wiki/Delphi_Newsgroups |
|
|
| Back to top |
|
|
Ingo
Guest
|
| Posted: Mon Jun 19, 2006 4:02 pm Post subject: Re: Slow import problem |
|
|
"Vitali Kalinin" <vitkalinin (AT) yahoo (DOT) com> wrote:
| Quote: | Take a look at "in" statment in Access SQL help.
|
1. I forgot to mention that I am using Microsoft SQL Server 2000
2. I dont see where an IN-Statememt would be useful to solve my problem |
|
| Back to top |
|
|
Vitali Kalinin
Guest
|
| Posted: Mon Jun 19, 2006 5:07 pm Post subject: Re: Slow import problem |
|
|
"Ingo" <ir2000 (AT) web (DOT) de> сообщил/сообщила в новостях следующее:
news:44968440$1 (AT) newsgroups (DOT) borland.com...
| Quote: |
"Vitali Kalinin" <vitkalinin (AT) yahoo (DOT) com> wrote:
Take a look at "in" statment in Access SQL help.
1. I forgot to mention that I am using Microsoft SQL Server 2000
Yes that makes a big difference here.
2. I dont see where an IN-Statememt would be useful to solve my problem
Still possible to apply. Since you are using MS Jet you can reference |
external tables using "IN" statement on Access SQL (don't mix that with "IN"
of ANSI SQL). But in this specific situation I would recommend to go with
DTS or BCP. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
