moving records from data table to database 1

[DebHanleyRI]

I need to insert into a database from a csv file.

I started by copying the csv file into a data table and that works like a charm - I can step through this and I see the data in the table:

Using sr As System.IO.StreamReader = System.IO.File.OpenText("\\testSv\c$\VRFiles\liveoutputTEMP.csv")
line = sr.ReadLine()
Do While line IsNot Nothing
Dim data() As String = line.Split(","c)
If data.Length > 0 Then
If i = 0 Then
For Each item In data
dt.Columns.Add(New DataColumn())
Next item
i += 1
End If
Dim row As DataRow = dt.NewRow()
row.ItemArray = data
dt.Rows.Add(row)
End If
line = sr.ReadLine()
Loop
End Using

Now I need to take this datatable and insert it into a database - here is my pseudocode:

For each row in table
patient name - column A
patient DOB = column B
etc.....
Next row

Can anyone point me in the right direction please?

not all who wander are lost....

 

[gmmastros]

What type of database? SQL Server, Access, Oracle, etc...

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DebHanleyRI]

sql 2005

not all who wander are lost....

 

[gmmastros]

There are a ton of ways to import CSV data in to SQL Server. Each method has it's advantages and disadvantages. Using VB.Net is usually considered the hardest and slowest method for importing data (in to SQL Server).

I would suggest that you:

1. Make sure the data file is on the same physical computer as SQL Server (this helps with permissions).
2. If your data is nice and clean, use a Bulk Insert command to load the data in to SQL Server.
3. If you need to modify the data prior to inserting it, then I suggest you use SSIS to load it in to the DB.

Usually, in situations like this, I will create a VB front end that allows you to select the data file to load. I then copy it to a folder on the server, and then issue a Bulk Insert command (from VB) to do the actual loading.

This blog shows 6 different ways to import data in to SQL Server.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DebHanleyRI]

The csv file is being created from a piece of software we bought - this software will only write to the server it resides on and the file it creates sometimes has garbage in it.

I have no control over the output so I was hoping to modify/verify the data before I write to SQL.

Thanks for the link - I will review these to seewhich one fits - FYI: I am a newbie to .net - thank you for the help!

not all who wander are lost....

 

[gmmastros]

In this situation, I would be tempted to write a vb.net app that opens the CSV file, cleans it up, and writes to another file in a folder on the SQL Server computer. Then you can use a simple Bulk Insert sql command to load the data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DebHanleyRI]

Thank you George - I got it working - here is the end result:

Try

Dim path As String = ("\\testSv\c$\VRFiles\liveoutputTEMP.csv")
Dim path2 As String = "liveoutput" + tDate + ".csv"
If File.Exists(path) = True Then


Using sr As System.IO.StreamReader = System.IO.File.OpenText("\\testSv\c$\VRFiles\liveoutputTEMP.csv")

line = sr.ReadLine()
Do While line IsNot Nothing
Dim data() As String = line.Split(","c)
If data.Length > 0 Then
If i = 0 Then
For Each item In data
dt.Columns.Add(New DataColumn())
Next item
i += 1
End If
Dim row As DataRow = dt.NewRow()
row.ItemArray = data
dt.Rows.Add(row)
End If
line = sr.ReadLine()
Loop
End Using


Dim NewDT As DataTable = dt
Dim e As Integer


Do While e < dt.Rows.Count
If Not IsDBNull(dt.Rows(e).Item(0)) Then
patName = dt.Rows(e).Item(0)
accNumber = dt.Rows(e).Item(1)
phone = dt.Rows(e).Item(2)
DOS = dt.Rows(e).Item(3)
dateFax = dt.Rows(e).Item(4)
result = dt.Rows(e).Item(5)
center = dt.Rows(e).Item(6)
flag = dt.Rows(e).Item(7)
If flag = "YES" And result = " Disconnected" Then
result = "Message"
End If

e = e + 1

tmpDate = Date.Today
strSQL = "INSERT into dialer(patname, accnumber,phone,DOS,datefax,result,center,dateadded)"
strSQL = strSQL + "Values (" & "'" & patName & "'," & "'" & accNumber & "'," & "'" & phone & "'," & "'" & DOS & "'," & "'" & dateFax & "','" & result & "','" & center & "','" & tmpDate & "')"
Debug.Print(strSQL)

sCommand2.Connection = sDSN2.dbIntegratons
sCommand2.CommandText = strSQL
sCommand2.ExecuteNonQuery()
End If

Loop
File.Delete(path)
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
'sCommand2.Connection.Close()

End Sub

not all who wander are lost....

 

[gmmastros]

That's not at all what I had in mind, but I'm glad it's working for you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom