Passing text file to SQL Server

[Chris]

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

[Bryan Martin]

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

"Chris" <an*******@discussions.microsoft.com> wrote in message
news:06**********************************@microsof t.com...

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 file

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

Can you show me some sample code?

Thanks

[Tu-Thach]

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

[Chris]

Hi
What if I need to pharse this data in vb and send it to the database?

[Chris]

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

[Tu-Thach]

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?

[Bryan Martin]

If you meant parse then if you need to do this as well you may be better off
doing it manually using OLE DB. You can look up the connection string here
http://www.connectionstrings.com

Bryan
"Chris" <an*******@discussions.microsoft.com> wrote in message
news:C3**********************************@microsof t.com...

Hi,
What if I need to pharse this data in vb and send it to the database?

[Cor]

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

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

Can you show me some sample code?

Thanks

[prasath Basavaraj]

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(Serve r.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.ConfigurationSe ttings.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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor]

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 got as far as this..

Dim strFileName As String = "pos.txt"
Dim strFilePath As String = "C:\pos\"
Dim ds As New DataSet
Try
Dim f As System.IO.File
If f.Exists(strFilePath & strFileName) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strFilePath & ";Extended Properties=""Text;HDR=No;FMT=Delimited\""" Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
strFileName, ConStr)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

How do I now transfer the data from the dataset to the database

Thanks

[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 got as far as this..

Dim strFileName As String = "pos.txt"
Dim strFilePath As String = "C:\pos\"
Dim ds As New DataSet
Try
Dim f As System.IO.File
If f.Exists(strFilePath & strFileName) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strFilePath & ";Extended Properties=""Text;HDR=No;FMT=Delimited\""" Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
strFileName, ConStr)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

How do I now transfer the data from the dataset to the database

Thanks

[Chris]

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?

[Cor]

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

"mytable" would be the tabel from the database?

Yes

[Cor]

Hi Chris,

It is not necessary you correct my typos exactly in the wrong direction :-)

dr1(7) = dr(7)
dr1(8) = dr(8)
dr1(9) = dr(9)
ds2.Tables(0).Rows.Add(dr1)


Next

Dim cmb As New SqlClient.SqlCommandBuilder(adoDA)
Try

adoDA.Update(ds2)
Catch ex As Exception

End Try

[Chris]

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.

[Cor]

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

no accessible 'FillSchema' accepts this number of arguements.

[Cor]

Hi Chris, sorry

adoDA.fillschema(ds2, SchemaType.Source)

Cor

[MAF]

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

[Tu-Thach]

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