Access import xls data to remote server "speeding up"

[Dolenc090]

Hi, my first topic, so I hope I put it in the right section .

I'm trying to import some data(8 columns, 30k rows) from Excel(xls) file to a db2(iSeries) table trough Access97(uses odbc connection).

The server is located in another country and it looks like the connection is a bit slow.

For 30k rows it takes about an hour to transfer
The problem is transferring the data through from my pc to the server.

Is there a way to speed things up a bit?
Some kind of bulk insert?


This is what I tried so far:

First I tried to import the data directly from xls, looping through rows and inserting data into the table, that is linked in access, row by row. That was slow

Then I looped again through the data and inserting it to a local table, then insert the entire local table to the "real" linked table.

Then I removed the looping and import the data using transferSpreadSheat method and then insert the local table to the real one.
But if I look at the server jobs, I can still see that it inserts row by row.(THAT IS THE REAL PROBLEM)

Now Im trying to transfer all the data to a local table and then copy the entire table to the server using "TransferDatabase acExport", and it works a bit faster, but I ran into all kind of problems, it copies the table "name", with "" that db2 doesnt recognize, and some other problems, so not the best solution.

Any help apriciated.

 

[alvechurchdata]

Might it be quicker to split the task into two. If you use a batch file or script to copy the xls to a local drive then the actual import into Access might go much faster.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[traingamer]

I'm not familiar with db2 bulk copy / bulk insert facilities. You may be better off asking in the db2 forum:

http://www.tek-tips.com/threadminder.cfm?pid=178

Using the Access jet engine / ODBC to update even a SQL server database with thousands and thousands of rows is horribly slow compared to using SSIS. I replaced an already-optimized daily ODBC upload of just 16k rows with SSIS and reduced elapsed time from around 10 minutes to seconds.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Dolenc090]

Import in access is not really the problem, its quite fast (a few seconds) but when it starts to transfer the data to a table on the server, then all hope is lost

I also tried transferring the xls(converted to csv) directly to the server file system and then importing. That worked, but to transfer the files to a server (if not manually) you have to use "db2 file to server" transfer program and "we" didn't buy the license for it.

So I have to find something that works on an access or db2Sql/procedure level

 

[Dolenc090]

Thank to both of you, will look into SSIS tomorrow and will report back what happened.

 

[traingamer]

SSIS is for SQL Server - I was just using that as an example.

If you have no transfer options with db2, your bottleneck is apparently the network speed (or lack thereof).

One of the tricks we've tried in the past is to bundle multiple Insert statements so we send fewer packets up the line to the server. This usually only gave us incremental speed increases though as the server is still inserting records one at a time.

partial example:

   For ctr = startcount to endcount
        sADOCommand = sADOCommand & vbCrLf & "insert into dbo.tablename (ID, CAC, CAC_Code, etc...

        If Len(sADOCommand) > 40000 Then
            adoSendCommand (sADOCommand)
            sADOCommand = ""
        End If
Next ctr

Good luck

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Dolenc090]

Closure

This is what happened, it went on a halt for some time.

I experimented with blob fields a bit(kinda of a bulk insert, just one big one). But then gave up the idea and made a separate program in .net, and that speed things up a bit, since it doesn't have to go through ODBC.

Not solved the original problem, but a workaround will do just fine.

Thank to all for the help