Import a text file into sql 2000 with vb.net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Import a text file into sql 2000 with vb.net

Author

Topic

sbhegel
Starting Member

19 Posts

Posted - 2006-03-08 : 10:47:02

Hello,

I am new to vb.net 2005 and wondering how I can do the following in either vb.net or asp.net (it will need to be in asp in the end, but I would like to start with vb.net)

1. Open a text file delimited by "|" with " (double qoute) as text identifiers. Example:
78965 | "This is | a test" | HI

2. Read this text file into vb.

3. connect to a sql 2000 server and add these records to a table.

I know this has probably been asked a lot, but I can't seem to find a post that addresses this.

Thanks,
Scott

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-03-08 : 13:16:06

You'll use a System.IO.StreamReader or System.IO.TextReader to read the file. FOr each line, you'll do a Split() to give you an array of the different data elements in your file. Split may not work exactly for that string since you have a pipe in the middle of your "text" element. You may have to get creative there.
Then do your standard SQL stuff of a System.Data.SQLCLient.SQLCommand object executing a stored proc to insert the data into the database.

If you do some searching on the classes listed above, you should be able to find samples on how to use them all.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

sbhegel
Starting Member

19 Posts

Posted - 2006-03-08 : 14:29:21

OK, This is what I came up with.

Dim myConnectionString as String = "Server=xxxx;Database=custom;User ID=xxxx;Password=xxxx;Trusted_Connection=False"
Dim myConnection As New SqlConnection(myConnectionString)

Dim sr As TextFieldParser = New TextFieldParser(BAK_FILE_NAME)
Dim currentRow As String()
Dim myArr1(8) As String
Dim i as Integer
Dim SQLText as String
Dim myCommand As New SqlCommand()

sr.TextFieldType = FileIO.FieldType.Delimited
sr.SetDelimiters("|")

myCommand.Connection = myConnection
myConnection.Open()

While Not sr.EndOfData
Try

currentRow = sr.ReadFields()
Dim currentField As String
i = 0
For Each currentField In currentRow
myArr1.SetValue(currentField,i)
i = i + 1
Next

SQLText = "INSERT INTO PalletLabel (custpart, format, printtime, printdate, part, qty, datecode, shift) Values("
SQLText = SQLText & "'" & myArr1.GetValue(0) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(1) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(2) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(3) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(4) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(5) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(6) & "',"
SQLText = SQLText & "'" & myArr1.GetValue(7) & "')"

myCommand.CommandText = SQLText
myCommand.ExecuteNonQuery()

Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try

End While

MyConnection.Close()

Any thoughts on this? Is there a better way of doing this?

Thanks,
Scott

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-03-08 : 14:40:57

Well, that looks like and even better way than I've ever done it before, but ti doesn't look like it handles the test case you gave us

78965 | "This is | a test" | HI
if you split on |, you get "4" fields instead of the "3" that you are wanting. There are a few different ways of handling this (such as replacing the | in your text that comes out of the database with some other weird character so when you save it you can replace this new weird character with the pipe, but I'm not really sure what the best way is for that.

I'd suggest moving away from the "build up the string of SQL and executing it" method though. You should try to move to a stored proc with parameters. This will help you fight off SQL Injection attacks

78965 | "This is | a test" | HI; DROP DATABASE YourDatabaseNameHere
Something like that

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

sbhegel
Starting Member

19 Posts

Posted - 2006-03-08 : 15:39:39

for my test scenerio I guess there is a method that you can use on this to tell that the fields are enclosed in quotes.

Something like:
sr.HasFieldsEnclosedInQuotes = true

I think this then allows me to have "This is | a test" and it won't split on the |. I think the catch might be is that everything has to be enlosed in quotes??

Scott

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources