5 Replies - 6481 Views - Last Post: 12 March 2009 - 05:52 PM Rate Topic: -----

#1 morlock  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-March 09

Loading a text file to SQL in vb.net

Post icon  Posted 12 March 2009 - 07:17 AM

Hi,
I am trying to create a program which reads data from a text file and insert the data in to a SQL 2005 table, but unfortunately it only reads the first line and insert correctly but not the rest of the lines on the text file

The text file looks like this

Test.txt
John, Doe, 40, 12/04/1968
Micke, Gulm, 39, 07/08/1970
Tomy, Salo, 29, 13/03/1980

The SQL 2005 DB name “USERSDAT”
Table name: anvinfo

fnamn varchar(50)
enamn varchar(50)
alder varchar(50)
fodd datetime

vb.net codes

Sub GetIt1()
		Dim i As Long = 0
		Dim sr As StreamReader = New StreamReader("C:\DEV\ Test.txt")
		Dim line As String = sr.ReadLine()
		Dim dbConn As SqlConnection = New SqlConnection("Data Source=devcomp7834;Initial Catalog= USERSDAT;Integrated Security=True")
		Dim dbCmd As SqlCommand = New SqlCommand()
		dbCmd.Connection = dbConn
		Dim wholeFile As String = sr.ReadToEnd()
		Do
			Dim fields() As String = line.Split(",")
			dbCmd.CommandText = "INSERT INTO dbo. anvinfo(fnamn, enamn, alder, fodd)VALUES('" & fields(0) & "','" & fields(1) & "', '" & fields(2) & "', " & fields(3) & " )"
			dbConn.Open()
			dbCmd.ExecuteNonQuery()
			i = i + 1
			line = sr.ReadLine()
		Loop While Not line = String.Empty
		dbConn.Close()
End Sub



Please if posiable help to sort out this issue as I need read the entire text file which in reality will be about 5000-10000 lines and insert them in to the SQL DB line by line.

Thanks in advance.

Mor.

Is This A Good Question/Topic? 0
  • +

Replies To: Loading a text file to SQL in vb.net

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Loading a text file to SQL in vb.net

Posted 12 March 2009 - 08:59 AM

Here's an example that should do exactly what you're looking for

Private Sub ReadFileIntoSql(ByRef file As String, ByRef delimiter As Char)
	Dim lines As String()
	Dim query As String
	Using dbConn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=devcomp7834;Initial Catalog= USERSDAT;Integrated Security=True")
		Using dbCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()
			dbCmd.Connection = dbConn

			lines = System.IO.File.ReadAllLines(file)

			For Each line As String In lines
				Dim values As String() = line.Split(delimiter)
				query = "INSERT INTO dbo. anvinfo(fnamn, enamn, alder, fodd) "
				query &= "VALUES("

				For Each value As String In values
					query &= "'"
					query &= value
					query &= "',"
				Next
				If query.EndsWith(",") Then
					query = query.Remove(query.Length - 1)
				End If
				query &= ")"
				dbCmd.CommandText = query
				dbCmd.Connection.Open()
				dbCmd.ExecuteNonQuery()
			Next
		End Using
	End Using
End Sub



I use the Using Statement with both the SqlConnection and SqlCommand object. This statement has been shown to release resource and clean up after itself, thus one less thing we have to worry around as a developer.

We have a variable lines which is a string array and we populate it with ReadAllLines which will read each line into our string array, the it closes the file for us. We then loop through each line and populate a second string array (values) by splitting each line on the delimiter provided.

From there we build our SQL statement for inserting the data, we then execute the query to insert the data. Keep in mind that your query is ripe for a SQL Injection attack and you may want to look into using Parameterized Queries when interacting with your database.

Hope this helps :)
Was This Post Helpful? 0
  • +
  • -

#3 morlock  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-March 09

Re: Loading a text file to SQL in vb.net

Posted 12 March 2009 - 04:09 PM

Thanks PsychoCoder

Your help is very much appreciated, I tried this method but its giving me all NULL values on the table.
So basically this is what I get

NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL

Any idea what could cause this or what am I doing wrong here.
Thanks again

Mor.
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Loading a text file to SQL in vb.net

Posted 12 March 2009 - 04:50 PM

First show how you're using the idea I offered. Then we can go from there
Was This Post Helpful? 0
  • +
  • -

#5 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2270
  • View blog
  • Posts: 9,496
  • Joined: 29-May 08

Re: Loading a text file to SQL in vb.net

Posted 12 March 2009 - 04:58 PM

Problem with OP code is they read to the end of stream first, then try to read the next line.
So Try
Sub GetIt1()
		Dim i As Long = 0
		Dim sr As StreamReader = New StreamReader("C:\DEV\ Test.txt")
		Dim line As String = sr.ReadLine()
		Dim dbConn As SqlConnection = New SqlConnection("Data Source=devcomp7834;Initial Catalog= USERSDAT;Integrated Security=True")
		Dim dbCmd As SqlCommand = New SqlCommand()
		dbCmd.Connection = dbConn
		while Not(sr.endofStream)
			line = sr.ReadLine()
Dim fields() As String = line.Split(",")
			dbCmd.CommandText = "INSERT INTO dbo. anvinfo(fnamn, enamn, alder, fodd)VALUES('" & fields(0) & "','" & fields(1) & "', '" & fields(2) & "', " & fields(3) & " )"
			dbConn.Open()
			dbCmd.ExecuteNonQuery()
			i = i + 1
			
		End While
		dbConn.Close()
End Sub


Was This Post Helpful? 0
  • +
  • -

#6 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2270
  • View blog
  • Posts: 9,496
  • Joined: 29-May 08

Re: Loading a text file to SQL in vb.net

Posted 12 March 2009 - 05:52 PM

Also have a read of this tutorial, it introduce the use of DataAdapters.
The author uses an Access Database but it applies for SQL Database, just select the SQL Databate instead.
Database Tutorial
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1