Passing text file to SQL Server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I need to pass the entries in a text file to corresponding tables in a database. Can I pass the entire text file to a stored procedure or can I write a VB.NET procedure to pharse the file and pass the data to the database? This is a sample of the text fil

1120,011522169050,01,+0000001,031221,0005.95,2003,8331308,2003122
9924,014305689050,01,+0000001,031221,0005.95,2003,8331308,2003122
1883,012152869050,01,+0000001,031222,0005.95,2003,8331308,2003122
1896,017202869050,01,+0000001,031221,0005.95,2003,8331308,2003122
1896,072211119050,01,+0000001,031222,0005.95,2003,8331308,2003122
1908,028502869050,01,+0000001,031221,0005.95,2003,8331308,2003122

Can you show me some sample code

Thanks
 
You should look at bulk insert in the Books online help.

Chris said:
Hi,
I need to pass the entries in a text file to corresponding tables in a
Click to expand...
database. Can I pass the entire text file to a stored procedure or can I
write a VB.NET procedure to pharse the file and pass the data to the
database? This is a sample of the text file
 
If you are just going to import the text directly then you can just SQL server import utility. If you are looking at doing it programmatically, then you can use the OleDb provider to read the text file and insert it into the database

Tu-Thac
www.ongtech.co

----- Chris wrote: ----

Hi
I need to pass the entries in a text file to corresponding tables in a database. Can I pass the entire text file to a stored procedure or can I write a VB.NET procedure to pharse the file and pass the data to the database? This is a sample of the text fil


1120,011522169050,01,+0000001,031221,0005.95,2003,8331308,2003122
9924,014305689050,01,+0000001,031221,0005.95,2003,8331308,2003122
1883,012152869050,01,+0000001,031222,0005.95,2003,8331308,2003122
1896,017202869050,01,+0000001,031221,0005.95,2003,8331308,2003122
1896,072211119050,01,+0000001,031222,0005.95,2003,8331308,2003122
1908,028502869050,01,+0000001,031221,0005.95,2003,8331308,2003122

Can you show me some sample code

Thanks
 
Hi
Where can I find info on using the OLEDB provider to read the text file and pass it to the database?
 
I have a web log listed on my web site that shows you how to use OleDb to read flat text file

Tu-Thac
www.ongtech.co

----- Chris wrote: ----

Hi
Where can I find info on using the OLEDB provider to read the text file and pass it to the database?
 
Hi Chis,

You need in my opinion,

a SQLconnection
a SQLcommand
a SQL insert string
execute a SQLcommand (executenonquerry)

That should do the job for you.

Very easy to do.

I hope this helps, if you have detailed qeustions, feel free to ask.

Cor
Hi,
I need to pass the entries in a text file to corresponding tables in a
Click to expand...
database. Can I pass the entire text file to a stored procedure or can I
write a VB.NET procedure to pharse the file and pass the data to the
database? This is a sample of the text file
 
Hai friend,
This is a simple code which will give u a good idea of how to start
with. I have worked it out in c sharp and can help u much if required
any help from my side.
Here i have read the data in the text file that i assume to be in the
root folder in the name sample.txt in to string variable and inserted
the data into a field in the trial1 table.Check that u give the
connection string right.My connection string is in the web.config file
so u will find me refering to that.And remember thet the field in the
table should be of ntext type.



string strData="";

StreamReader srReadToEnd = new
StreamReader((System.IO.Stream)File.OpenRead(Server.MapPath("sample.txt"
)),System.Text.Encoding.ASCII);
srReadToEnd.BaseStream.Seek(0, SeekOrigin.Begin);
strData=srReadToEnd.ReadToEnd().ToString();
srReadToEnd.Close();

SqlConnection objConn = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Co
nnectionTrial"]);
SqlCommand cmd=new SqlCommand();
cmd.Connection = objConn;
cmd.CommandText = "Insert into trial1 values(@data)";
cmd.Parameters.Add("@data",SqlDbType.NText).Value=strData;
objConn.Open();
cmd.ExecuteNonQuery();
objConn.Close();



I can help on any further doubts and issues.
 
Hi Chris,

I thought I knew this code.
And I did not send it to you but to someone else today.
And told him that he got it in the dataset but with that not in his
database.
:-)

I did give you the solution
a SQLconnection
a SQLcommand
a SQL insert string
execute a SQLcommand (executenonquerry)

But Ok you have now a dataset with a table and a row and we go further with
this using another dataset from the database. I type all in this message
because it is late here.

I said you need a SQLconnection that is something as

Dim Conn As New
SqlClient.SqlConnection("Server=Myserver;DataBase=Mydatabase;Integrated
Security=SSPI")

Because we use a dataset we do not use the command but the SQLdataadapter

dim da as new SqlClient.SqlDataAdapter("select * from mytabel", conn)

Then we need a dataset

Dim ds2 as new dataset

We fill all the schema and the constraints from our database in that dataset

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
This is for the key

da.fillschema(ds2)

Now we have to fill all the columns in that database in the right way

dim dr as datarow
for each dr in ds.tables(0).rows
dim dr1 as datarow = ds2.tables(0).newrow
dr1("mycolumnfromdataset") = dr(0)
dr1("mynextcol")=dr(1)
etc
ds1.tables(0).rows.add(dr1)
next

The part that can give a problem is the key here

Now we can put this datases ds1 back to the database therefore we use the
commandbuilder to make the instert command (it makes also the update and
delete).

dim cmb as new sqlclient.sqlcommandbuilder(da)

and then we can do the update

try
da.update(ds2)
catch sqlex as sqlclient.sqlexception
messagebox.show(sqlex)
catch ex as exception
messagebox.show(ex)
end try

This should do it (also I made two exeptions because there can be duplicate
problems, and than we need a slightly different solution but try this
first).

But as I said, I typed everything in here, so it would be a wonder if it
would go in one time

However succes

Cor
 
Should be nothing strange in it.
Hi Chris,

I thought I knew this code.
And I did not send it to you but to someone else today.
And told him that he got it in the dataset but with that not in his
database.
:-)

I did give you the solution
a SQLconnection
a SQLcommand
a SQL insert string
execute a SQLcommand (executenonquerry)

But Ok you have now a dataset with a table and a row and we go further with
this using another dataset from the database. I type all in this message
because it is late here.

I said you need a SQLconnection that is something as

Dim Conn As New
SqlClient.SqlConnection("Server=Myserver;DataBase=Mydatabase;Integrated
Security=SSPI")

Because we use a dataset we do not use the command but the SQLdataadapter

dim da as new SqlClient.SqlDataAdapter("select * from mytabel", conn)

Then we need a dataset

Dim ds2 as new dataset

We fill all the schema and the constraints from our database in that dataset

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
This is for the key

da.fillschema(ds2)

Now we have to fill all the columns in that database in the right way

dim dr as datarow
for each dr in ds.tables(0).rows
dim dr1 as datarow = ds2.tables(0).newrow
dr1("mycolumnfromdataset") = dr(0)
dr1("mynextcol")=dr(1)
etc
ds1.tables(0).rows.add(dr1)
next

The part that can give a problem is the key here

Now we can put this datases ds1 back to the database therefore we use the
commandbuilder to make the instert command (it makes also the update and
delete).

dim cmb as new sqlclient.sqlcommandbuilder(da)

and then we can do the update

try
da.update(ds2)
catch sqlex as sqlclient.sqlexception
messagebox.show(sqlex)
catch ex as exception
messagebox.show(ex)
end try

This should do it (also I made two exeptions because there can be duplicate
problems, and than we need a slightly different solution but try this
first).

But as I said, I typed everything in here, so it would be a wonder if it
would go in one time

However succes

Cor
 
Hi Cor,
I see you have in your code

dim da as new SqlClient.SqlDataAdapter("select * from mytabel", conn)


"mytable" would be the tabel from the database?
 
Hi Cor
I am getting an error at

adoDA.FillSchema(ds

Its underlined. When I compile I get "Overload resolution failed because no accessible 'FillSchema' accepts this number of arguements.
 
Hi Chris,

In my sample it was this da.fillschema(ds2)

In yours adoDA.FillSchema(ds2, SchemaType.Mapped)

And now you changed it to adoDA.FillSchema(ds)

Did you try adoDA.fillschema(ds2) ?

Cor
adoDA.FillSchema(ds)

Its underlined. When I compile I get "Overload resolution failed because
Click to expand...
no accessible 'FillSchema' accepts this number of arguements.
 
You spoke of an example on your website. I was not able to locate the example where is it located

----- Tu-Thach wrote: ----

If you are just going to import the text directly then you can just SQL server import utility. If you are looking at doing it programmatically, then you can use the OleDb provider to read the text file and insert it into the database

Tu-Thac
www.ongtech.co

----- Chris wrote: ----

Hi
I need to pass the entries in a text file to corresponding tables in a database. Can I pass the entire text file to a stored procedure or can I write a VB.NET procedure to pharse the file and pass the data to the database? This is a sample of the text fil


1120,011522169050,01,+0000001,031221,0005.95,2003,8331308,2003122
9924,014305689050,01,+0000001,031221,0005.95,2003,8331308,2003122
1883,012152869050,01,+0000001,031222,0005.95,2003,8331308,2003122
1896,017202869050,01,+0000001,031221,0005.95,2003,8331308,2003122
1896,072211119050,01,+0000001,031222,0005.95,2003,8331308,2003122
1908,028502869050,01,+0000001,031221,0005.95,2003,8331308,2003122

Can you show me some sample code

Thanks
 
Under the Blogs section, you should look at the connection strings example. It shows how you can use OleDb provider to open text file as its datasource

Tu-Thac
www.ongtech.co

----- MAF wrote: ----

You spoke of an example on your website. I was not able to locate the example where is it located

----- Tu-Thach wrote: ----

If you are just going to import the text directly then you can just SQL server import utility. If you are looking at doing it programmatically, then you can use the OleDb provider to read the text file and insert it into the database

Tu-Thac
www.ongtech.co

----- Chris wrote: ----

Hi
I need to pass the entries in a text file to corresponding tables in a database. Can I pass the entire text file to a stored procedure or can I write a VB.NET procedure to pharse the file and pass the data to the database? This is a sample of the text fil


1120,011522169050,01,+0000001,031221,0005.95,2003,8331308,2003122
9924,014305689050,01,+0000001,031221,0005.95,2003,8331308,2003122
1883,012152869050,01,+0000001,031222,0005.95,2003,8331308,2003122
1896,017202869050,01,+0000001,031221,0005.95,2003,8331308,2003122
1896,072211119050,01,+0000001,031222,0005.95,2003,8331308,2003122
1908,028502869050,01,+0000001,031221,0005.95,2003,8331308,2003122

Can you show me some sample code

Thanks
 
Back
Top