import large text file
mfrsousa
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
William (Bill) Vaughn
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)
-----------------------------------------------------------------------------------------------------------------------
"mfrsousa" <mfrs...@gmail.com> wrote in message
news:1173042613.0...@t69g2000cwt.googlegroups.com...
RobinS
version...
Robin S.
-----------------------
"mfrsousa" <mfrs...@gmail.com> wrote in message
news:1173042613.0...@t69g2000cwt.googlegroups.com...
Cor Ligthert [MVP]
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
"mfrsousa" <mfrs...@gmail.com> schreef in bericht
news:1173042613.0...@t69g2000cwt.googlegroups.com...
RobinS
think it's the physical size of the file on the disk that's a problem.
Robin S.
---------------------------------------
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:OiRaaVuX...@TK2MSFTNGP03.phx.gbl...
Cor Ligthert [MVP]
I am not sure if I understand you?
I know that updating a Jet database goes slow.
Cor
"RobinS" <Rob...@NoSpam.yah.none> schreef in bericht
news:66Gdnb5spbLCMnbY...@comcast.com...
Paul Clement
¤ hi there,
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)
RobinS
>>> 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.
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.
---------------------------------
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:ude5SquX...@TK2MSFTNGP05.phx.gbl...
William (Bill) Vaughn
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:f2aou21l4eesup20e...@4ax.com...
Cor Ligthert [MVP]
That is easy when a textfile is zipped it is mostly 100 times as small and
with that will improve the storing time.
Cor
"RobinS" <Rob...@NoSpam.yah.none> schreef in bericht
news:17idnUWfMtKCo3HY...@comcast.com...
mfrsousa
<UseAdddressAtEndofMess...@swspectrum.com> wrote:
> I don't know what the format of your text file is but you can use a SQL statement to perform this
> operation:
>
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)
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
RobinS
that his problem was disk space, but rather, loading the file in an Access
database.
Robin S.
---------------------------------------
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:e5MoH50X...@TK2MSFTNGP02.phx.gbl...
Cor Ligthert [MVP]
> But you can't load a zipped file into Access
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
"RobinS" <Rob...@NoSpam.yah.none> schreef in bericht
news:2ZedndmhQ4TJLnHY...@comcast.com...
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]
RobinS
fewer records. And you can't load a zip file into Access.
Robin S.
---------------------------------------
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:eBBin74X...@TK2MSFTNGP03.phx.gbl...
Cor Ligthert [MVP]
> Zipping the file only makes it smaller on the disk. It doesn't make it
> have fewer records.
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.
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
mojeza
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.
RobinS
in a table isn't what the guy was going after.
Robin S.
------------------------------------------
"mojeza" <naj...@hotmail.com> wrote in message
news:1173272208....@n33g2000cwc.googlegroups.com...
Cor Ligthert [MVP]
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
"RobinS" <Rob...@NoSpam.yah.none> schreef in bericht
news:d7udnX24-_WMnXLY...@comcast.com...
Cor Ligthert [MVP]
what would you than answer?
what would be your answer then?
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> schreef in bericht
news:e0OYUePY...@TK2MSFTNGP03.phx.gbl...
> 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
RobinS
you're telling me you can store it as a zipped file, and unzip it to run a
query against it?
Robin S.
---------------------------------------
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:%23ohiT1Q...@TK2MSFTNGP03.phx.gbl...
Cor Ligthert [MVP]
The difficult is that you only can use it to store with a program. As soon
as you try to use it in by instance Office Access than it is impossible.
However you can try it just as you do a blob. If you want a sample. on the
website from Ken and me is more than enough about that.
If there is a problem than tell it (you are amongst those who I will help
via mail as well).
Cor
"RobinS" <Rob...@NoSpam.yah.none> schreef in bericht
news:oI2dnQXyrYgPaG3Y...@comcast.com...
RobinS
have checked out your website in the past, and found it very helpful. I
should browse more...
Have a great day!
Robin
---------------------------------------------
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:%23tMyMCn...@TK2MSFTNGP05.phx.gbl...
viru...@gmail.com
there is telephone which one i'm using as key but its working very slow...
my code is.....
// start calling jugad....
OleDbCommand coll = new OleDbCommand("select col from all_table", jug);
OleDbDataAdapter col_ad = new OleDbDataAdapter(coll);
DataTable col_dt = new DataTable();
int rw_no_col = col_ad.Fill(col_dt);
string accpath = "";
OleDbCommand accmd = new OleDbCommand("select DB_path,mode from jugad where id='1'", jug);
OleDbDataAdapter da = new OleDbDataAdapter(accmd);
DataTable dt = new DataTable();
da.Fill(dt);
accpath = dt.Rows[0]["DB_path"].ToString();
string mode_tb=dt.Rows[0]["mode"].ToString();
//start calling source file.....
OleDbConnection accconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + accpath + "");
DataTable dt1 = new DataTable();
accconn.Open();
//source file connection open....
// Required calculation....
double val = double.Parse(textBox1.Text);
Int64 obtn = (long)(val * Int64.Parse(tat)) / 100;
// End calculation..
int index_value = 1;
OleDbCommand lst3_cmd = new OleDbCommand("select col from all_table", jug);
OleDbDataAdapter list3_da = new OleDbDataAdapter(lst3_cmd);
DataTable list3_dt = new DataTable();
list3_da.Fill(list3_dt);
int lst3_cunt = list3_dt.Rows.Count;
string[] lst3_item = new string[lst3_cunt];
for (int r = 0; r < lst3_cunt; r++)
{
lst3_item[r] = list3_dt.Rows[r][0].ToString();
}
string[] colnm = new string[rw_no_col];
Int32 no = (Int32.Parse(obtn.ToString())) / 100;
string[] frstcoll = new string[Int32.Parse(obtn.ToString())];
Int32 no1 = (Int32.Parse(obtn.ToString())) / 100;
// random number generating....
Int32[] rend = new Int32[obtn + 1];
Random rm = new Random();
for (int h = 0; h < Int64.Parse(obtn.ToString()); h++)
{
rend[h] = rm.Next(int.Parse(tat.ToString()));
}
// end.....
// data entry in temp....
int gh = 100;
float one_per = (float)int.Parse(obtn.ToString()) / 100;
int hg = 1;
for (int i = 0; i < rw_no_col; i++)
{
progressBar1.Value = 0;
if (i == 0)
{
// OleDbCommand addcol123=new OleDbCommand
colnm[i] = col_dt.Rows[i][0].ToString();
label4.Text = "Database updating going on colunm " + colnm[i] + "";
try
{
OleDbCommand addcol = new OleDbCommand("create table temp([" + colnm[i] + "] char(255))", jug);
addcol.ExecuteNonQuery();
}
catch (Exception j)
{
}
OleDbCommand updatefin = new OleDbCommand("select ["+colnm[i]+"] from [" + mode_tb + "]", accconn);
OleDbDataAdapter upf_da = new OleDbDataAdapter(updatefin);
DataTable upf_dt = new DataTable();
upf_da.Fill(upf_dt);
for (int k = 0; k <int.Parse(obtn.ToString()); k++)
{
label4.Text = "Database updating going on colunm " + colnm[i] + "";
frstcoll[k] = upf_dt.Rows[rend[k]][colnm[i]].ToString();
OleDbCommand updatef = new OleDbCommand("insert into temp([" + colnm[i] + "])values('" + upf_dt.Rows[rend[k]][colnm[i]].ToString() + "')", jug);
updatef.ExecuteNonQuery();
if ((int)((float)k / one_per) < gh)
{
if (hg < gh)
{
progressBar1.Value = hg;
hg++;
continue;
}
else
{
progressBar1.Value = 0;
hg = 1;
continue;
}
}
}
}
else
{
colnm[i] = col_dt.Rows[i][0].ToString();
label4.Text = "Database updating going on colunm " + colnm[i] + "";
progressBar1.Value = 0;
hg = 1;
/* OleDbCommand single_col = new OleDbCommand("select col from non_marge", jug);
OleDbDataAdapter s_c_da = new OleDbDataAdapter(single_col);
DataTable s_c_dt=new DataTable();
int mar_count = s_c_da.Fill(s_c_dt);
if (mar_count > 1)
{
for (int y = 0; y < mar_count; y++)
{
OleDbCommand addcol1 = new OleDbCommand("alter table temp add [" + colnm[i] + "] char(255) ", jug);
addcol1.ExecuteNonQuery();
}
}*/
OleDbCommand addcol1 = new OleDbCommand("alter table temp add [" + colnm[i] + "] char(255) ", jug);
addcol1.ExecuteNonQuery();
OleDbCommand pick = new OleDbCommand("select [lst_data] from [" + colnm[i] + "]", jug);
OleDbDataAdapter temp_da = new OleDbDataAdapter(pick);
DataTable temp_dt = new DataTable();
temp_da.Fill(temp_dt);
string[] temp_store = new string[temp_dt.Rows.Count];
for (int g = 0; g < int.Parse(obtn.ToString()); g++)
{
string lst3_final = "";
for (int r = 0; r < temp_dt.Rows.Count; r++)
{
OleDbCommand updatefin1 = new OleDbCommand("select [" + colnm[0] + "],[" + temp_dt.Rows[r][0].ToString() + "] from [" + mode_tb + "] where [" + colnm[0] + "] = " + frstcoll[g] + "", accconn);
OleDbDataReader datarder = updatefin1.ExecuteReader();
while (datarder.Read())
{
lst3_final = lst3_final + " " + datarder.GetValue(1).ToString();
}
datarder.Close();
}
OleDbCommand updatef1 = new OleDbCommand("update temp set [" + colnm[i] + "] = '" + lst3_final + "' where [" + colnm[0] + "] = '" + frstcoll[g] + "' ", jug);
//OleDbDataAdapter ghjk = new OleDbDataAdapter(updatef1);
updatef1.ExecuteNonQuery();
if ((int)((float)g / one_per) < gh)
{
if (hg < gh)
{
progressBar1.Value = hg;
hg++;
continue;
}
else
{
progressBar1.Value = 0;
hg = 1;
continue;
}
}
}
}
}
MessageBox.Show("DONE");
}
catch (Exception h)
{
MessageBox.Show(h.Message);
}
}
}