import large text file

  • Thread starter Thread starter mfrsousa
  • Start date Start date
M

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?

thank you
 
I'm not surprised. Access does have an import facility to do this kind of
thing. Of course, SQL Server Express supports bulk copy which can handle
data imports this large in a fraction of the time--a few minutes at most.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Is there really no way you can use SQLServer instead? They have a free
version...

Robin S.
 
mfrsousa,

In your situation I surely would check if I am not able (and I am) to make a
Zip method in my program.
Then I can save it as a Image file.

Cor
 
How does that get a big text file imported into an Access database? I don't
think it's the physical size of the file on the disk that's a problem.

Robin S.
 
Robin,

I am not sure if I understand you?

I know that updating a Jet database goes slow.

Cor
 
¤ 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.

I don't know what the format of your text file is but you can use a SQL statement to perform this
operation:

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=C:\Test Files\db1 XP.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ReportFile] FROM
[Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[ReportFile.txt]",
AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
You posted this:

And I don't understand how that helps the OP with his problem of loading a
large file into a database. So I'm asking for clarification.

Robin S.
 
Thanks Paul. Now that's something I would mark as an "answer". ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Robin,

That is easy when a textfile is zipped it is mostly 100 times as small and
with that will improve the storing time.

Cor
 
I don't know what the format of your text file is but you can use a SQL statement to perform this
operation:
Click to expand...
/> Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=C:\Test Files\db1 XP.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ReportFile] FROM
[Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[ReportFile.txt]",
AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

Hi Paul,

Thank for our help

I had already tried something similar but was getting errors, i've
tried your code and still got some errors, as the error is in the SQL,
and i'm not very comfortable with SQL, maybe you can help me, here is
my code:
/************************ CODE ******************/
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|
DataDirectory|\\db.mdb";
string oledbstr = "INSERT * INTO [tblExtData] FROM [Text;DATABASE=F:\
\Projects\\KJStudio\\KJStudio\\bin\\Debug\\].[file.txt]";

OleDbConnection oleconn = new OleDbConnection(conn);
oleconn.Open();
OleDbCommand newcom = new OleDbCommand(oledbstr, oleconn);
newcom.ExecuteNonQuery();
/******************* END CODE ********************/

tblExtData is a table in db.mdb
file.txt is the text file

and the error is ERROR IN INSERT INTO STATEMENT.

Thank you all for the help
 
But you can't load a zipped file into Access, can you? I don't get the idea
that his problem was disk space, but rather, loading the file in an Access
database.

Robin S.
 
Robin,
But you can't load a zipped file into Access
Click to expand...

Read my message to the OP until the last sentence. It has nothing to do with
space, loading a small file takes just less time than a hug file.

Cor
 
¤
¤ I had already tried something similar but was getting errors, i've
¤ tried your code and still got some errors, as the error is in the SQL,
¤ and i'm not very comfortable with SQL, maybe you can help me, here is
¤ my code:
¤ /************************ CODE ******************/
¤ string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|
¤ DataDirectory|\\db.mdb";
¤ string oledbstr = "INSERT * INTO [tblExtData] FROM [Text;DATABASE=F:\
¤ \Projects\\KJStudio\\KJStudio\\bin\\Debug\\].[file.txt]";
¤
¤ OleDbConnection oleconn = new OleDbConnection(conn);
¤ oleconn.Open();
¤ OleDbCommand newcom = new OleDbCommand(oledbstr, oleconn);
¤ newcom.ExecuteNonQuery();
¤ /******************* END CODE ********************/
¤
¤ tblExtData is a table in db.mdb
¤ file.txt is the text file
¤
¤ and the error is ERROR IN INSERT INTO STATEMENT.

Does file.txt have a column header? In order to perform the INSERT without specifying column names,
the column names must match in both the source and destination. If the source is a comma delimited
file, and the first row does not contain a column header that identifies the column names, then the
column names default to F1, F2, F3, etc. Below is an example which specifies the column names as
they are different in the source and destination:

INSERT INTO [tblExtData] (Col1, Col2, Col3) SELECT F1, F2, F3 FROM [Text;F:\
\Projects\\KJStudio\\KJStudio\\bin\\Debug\\;].[file.txt]


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Zipping the file only makes it smaller on the disk. It doesn't make it have
fewer records. And you can't load a zip file into Access.

Robin S.
 
Robin,
Zipping the file only makes it smaller on the disk. It doesn't make it
have fewer records.
Click to expand...

What do you mean by that, I really cannot understand what you mean with this
sentence. Do you mean that it is important for the loading time what the
database is loading. A loading a zippfile is just one transaction, which can
be written with an execute.nonquery and what I never be tried but should be
possible even to be read by an executescalar.
And you can't load a zip file into Access.
Click to expand...

I find it great that you know what I can, an object is just an object and
can as far as I know be written as a bytearray (BLOB) in Access in the same
way as in SQL server.

Cor
 
Cor,

If you read the original post again you will notice that question was
how to IMPORT data from a text file into Access database not how to
SAVE text file in a database. This is why RobinS try to say that
zipping a text file wont help this process.
 
Thank you, that's exactly what I meant. Importing a zip file into a column
in a table isn't what the guy was going after.

Robin S.
 
Robin,

Why not, if I would ask you how can I in the easiest way make this routine
bellow quicker, what would you than answer.

dim a as float
for I as integer = 0 to 100000000000000
a = a + 1
end for

It is in my opinion almost exactly my answer.

Update(Zip(MyFile))

MyFile = Unzip(Execute.Scalar(Select myfilecolumn from Server where bla
a) )

Cor
 
Back
Top