import large text file

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
 
W

William \(Bill\) Vaughn

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)
 
R

RobinS

Is there really no way you can use SQLServer instead? They have a free
version...

Robin S.
 
C

Cor Ligthert [MVP]

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
 
R

RobinS

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.
 
C

Cor Ligthert [MVP]

Robin,

I am not sure if I understand you?

I know that updating a Jet database goes slow.

Cor
 
P

Paul Clement

¤ 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)
 
R

RobinS

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.
 
W

William \(Bill\) Vaughn

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)
 
C

Cor Ligthert [MVP]

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
 
M

mfrsousa

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
 
R

RobinS

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.
 
C

Cor Ligthert [MVP]

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
 
P

Paul Clement

¤
¤ 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)
 
R

RobinS

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.
 
C

Cor Ligthert [MVP]

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
 
M

mojeza

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.
 
R

RobinS

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.
 
C

Cor Ligthert [MVP]

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import large text file to a MS Access database 14
Importing Text Files Into Access 1
Importing large text files to excel 4
Text Import Wizard Breaklines 6
Modifying a LARGE text file 2
Text import wizard does import properly beyond text file line 1048 5
Import text file with DATE column into MS Access Database with VB.NET or C# 3
Import Text File Into Access 0

Top