Import large text file to a MS Access database

[mfrsousa]

hi there,

i have a huge large text file (350.000 lines) that i want to import to
a MS Acccess Database, of course i don't want to use Access, but do it
with C#.

i already have tried the AddRow method or Insert, reading each line of
the text file, the problem of course is velocity, it would take more
than 4 hours to add all lines/records to the database.

any suggestion?
than you

[Rad [Visual C# MVP]]

On 3 Mar 2007 08:36:21 -0800, mfrsousa wrote:

hi there,

i have a huge large text file (350.000 lines) that i want to import to
a MS Acccess Database, of course i don't want to use Access, but do it
with C#.

i already have tried the AddRow method or Insert, reading each line of
the text file, the problem of course is velocity, it would take more
than 4 hours to add all lines/records to the database.

any suggestion?

than you

A bit more info would help ... do you want each line to go into its own
row in the database or all the lines into one field
--
Bits.Bytes
http://bytes.thinkersroom.com

[Mark Rae]

"mfrsousa" <mf******@gmail .comwrote in message
news:11******** *************@t 69g2000cwt.goog legroups.com...

i have a huge large text file (350.000 lines) that i want to import to
a MS Acccess Database, of course i don't want to use Access, but do it
with C#.

i already have tried the AddRow method or Insert, reading each line of
the text file, the problem of course is velocity, it would take more
than 4 hours to add all lines/records to the database.

First of all, there is no such thing as an "Access database" per se -
Microsoft Access, like several other Micrsoft applications, uses the Jet
database format...

You say you don't want to use Access - is there any particular reason for
this...? I strongly suggest you open up the database in Access, open the
table in question, then import the text file using the Get External Data
functionality and time how long it takes - I'm willing to bet it will be
nothing like 4 hours...

Obviously if Access isn't installed, then your options are pretty much
limited to ADO.NET and the Jet OleDb provider. However, you might like to
consider using transactions to "batch" the imports into groups of, say
10,000 records - I've achieved massive speed gains with this technique in
the past...

[rossum]

On 3 Mar 2007 08:36:21 -0800, "mfrsousa" <mf******@gmail .comwrote:

>hi there,

i have a huge large text file (350.000 lines) that i want to import to
a MS Acccess Database, of course i don't want to use Access, but do it
with C#.

i already have tried the AddRow method or Insert, reading each line of
the text file, the problem of course is velocity, it would take more
than 4 hours to add all lines/records to the database.

any suggestion?

For a bulk load of a database, such as you describe, it is often
better to switch off indexing on the affected tables before starting
the load and then rebuilding the indexes from scratch after the bulk
load has finished.

rossum

[mfrsousa]

rossum wrote:

For a bulk load of a database, such as you describe, it is often
better to switch off indexing on the affected tables before starting
the load and then rebuilding the indexes from scratch after the bulk
load has finished.

rossum

Right i was just reading this bulk thing (sorry my ignorance), the
problem is that i have tried something like this:
/*************** *************** ******** CODE
*************** *************** */
string conn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=|
DataDirectory|\ \db.mdb";
OleDbConnection oleconn = new OleDbConnection (conn);
oleconn.Open();

string sqlstr = "BULK INSERT INTO tblExtData (ext_disc, ext_num,
ext_track, ext_artist) FROM 'c:\\data1.lst' )";
OleDbCommand olecom = new OleDbCommand(sq lstr, oleconn);
olecom.ExecuteN onQuery();
/*************** *************** ******** CODE
*************** *************** */
and i get this error

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.
i think that the OleDB doesnt support the bulk command

could you point to some bulk reading or explain better please

thanks

[mfrsousa]

Mark Rae wrote:

First of all, there is no such thing as an "Access database" per se -
Microsoft Access, like several other Micrsoft applications, uses the Jet
database format...

You say you don't want to use Access - is there any particular reason for
this...? I strongly suggest you open up the database in Access, open the
table in question, then import the text file using the Get External Data
functionality and time how long it takes - I'm willing to bet it will be
nothing like 4 hours...

Obviously if Access isn't installed, then your options are pretty much
limited to ADO.NET and the Jet OleDb provider. However, you might like to
consider using transactions to "batch" the imports into groups of, say
10,000 records - I've achieved massive speed gains with this technique in
the past...

Yes i'm developing an applicattion so i don't expect that everyone
will have the MAccess installed to make this operation. I will have a
look on the batch imports.

Thanks

[rossum]

On 3 Mar 2007 12:14:57 -0800, "mfrsousa" <mf******@gmail .comwrote:

>
rossum wrote:

>For a bulk load of a database, such as you describe, it is often
better to switch off indexing on the affected tables before starting
the load and then rebuilding the indexes from scratch after the bulk
load has finished.

rossum

Right i was just reading this bulk thing (sorry my ignorance), the
problem is that i have tried something like this:
/*************** *************** ******** CODE
************** *************** **/
string conn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=|
DataDirectory| \\db.mdb";
OleDbConnectio n oleconn = new OleDbConnection (conn);
oleconn.Open() ;

string sqlstr = "BULK INSERT INTO tblExtData (ext_disc, ext_num,
ext_track, ext_artist) FROM 'c:\\data1.lst' )";
OleDbCommand olecom = new OleDbCommand(sq lstr, oleconn);
olecom.Execute NonQuery();
/*************** *************** ******** CODE
************** *************** **/
and i get this error

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.
i think that the OleDB doesnt support the bulk command

could you point to some bulk reading or explain better please

thanks

I do not know MS SQL, all my experience was with Oracle SQL some time
ago.

Basically the sequence is:

Delete all indexes for tables to be loaded
Load data into tables
Rebuild/create indexes for tables just loaded

If you load data into an indexed table then each line inserted into
the table also requires an update to the index(es) for that table. By
dropping all the indexing before a bulk load you do not have the
overhead of re-indexing during the run.

Rebuilding the indexes from scratch is faster than trying to keep them
up to date while loading 350,000 lines into a table.

HTH

rossum

[RobinS]

"mfrsousa" <mf******@gmail .comwrote in message
news:11******** **************@ z35g2000cwz.goo glegroups.com.. .

>
rossum wrote:

>For a bulk load of a database, such as you describe, it is often
better to switch off indexing on the affected tables before starting
the load and then rebuilding the indexes from scratch after the bulk
load has finished.

rossum

Right i was just reading this bulk thing (sorry my ignorance), the
problem is that i have tried something like this:
/*************** *************** ******** CODE
*************** *************** */
string conn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=|
DataDirectory|\ \db.mdb";
OleDbConnection oleconn = new OleDbConnection (conn);
oleconn.Open();

string sqlstr = "BULK INSERT INTO tblExtData (ext_disc, ext_num,
ext_track, ext_artist) FROM 'c:\\data1.lst' )";
OleDbCommand olecom = new OleDbCommand(sq lstr, oleconn);
olecom.ExecuteN onQuery();
/*************** *************** ******** CODE
*************** *************** */
and i get this error

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.
i think that the OleDB doesnt support the bulk command

could you point to some bulk reading or explain better please

thanks

You are correct, I do not believe Access supports BULK Insert. SQLServer
does, but that's not much help to you.

Your choices are to try to figure out how to use OLE Automation to get
Access to import the file, or to write a line-by-line import. (First option
will be much faster). You might try posting your question to
microsoft.publi c.framework.ado net unless there's someone over there who can
help you who doesn't read this forum.

Robin S.

[Mark Rae]

"rossum" <ro******@coldm ail.comwrote in message
news:v9******** *************** *********@4ax.c om...

If you load data into an indexed table then each line inserted into
the table also requires an update to the index(es) for that table. By
dropping all the indexing before a bulk load you do not have the
overhead of re-indexing during the run.

Or perform the writes within a transaction - indexes are only updated when
the transaction is committed...

[mfrsousa]

On Mar 4, 7:48 pm, "RobinS" <Rob...@NoSpam. yah.nonewrote:

>
You are correct, I do not believe Access supports BULK Insert. SQLServer
does, but that's not much help to you.

Your choices are to try to figure out how to use OLE Automation to get
Access to import the file, or to write a line-by-line import. (First option
will be much faster). You might try posting your question to
microsoft.publi c.framework.ado net unless there's someone over there who can
help you who doesn't read this forum.

Robin S.

thanks