Bulk Insert Function

jdegrate · March 18th, 2009, 02:15 PM

I am trying to read a tab delimitted text file into SQL Server 2008 Express Database and have those data display on a data grid on a form window that I created. I have heard about the bulk insert or bulk copy method for doing this but I have yet to find the syntax for it.

Also I need to skip lines in the text file before data is recorded into the database. Any help would be greatly appreciated.

Code is as follows:

Dim AllText AsString = "", LineOfText AsString = ""
OpenFileDialog1.Filter = "Text files (*.TXT)|*.TXT"
OpenFileDialog1.ShowDialog() 'display Open dialog box
If OpenFileDialog1.FileName <> ""Then
Try'open file and trap any errors using handler
FileOpen(1, OpenFileDialog1.FileName, OpenMode.Input)
DoUntil EOF(1) 'read lines from file
LineOfText = LineInput(1)
'add each line to the AllText variable
AllText = AllText & LineOfText & vbCrLf
Loop
(import data here with proper formatting to data base)
Catch
MsgBox("Error opening file.")
Finally
FileClose(1) 'close file
Me.DataGridView1.Update()
EndTry
EndIf

gbianchi · March 18th, 2009, 02:54 PM

Hello.. So, what's your problem???

Bulk insert is an SQL directive to do an insert from a file. You need an schema file for the file and the file itself. It work very fast, but I don't know if you can make it work from outside SQL management (altough you are free to try, I never try it).

Also you know you have pure .net object to open, read and manage a file (you are using vb6 compatiblity objects, no good if you are using vb2008). read about the system.io namespace.

jdegrate · March 18th, 2009, 04:27 PM

I guess what I'm really trying to do is automate this procedure into one event, i.e. when the user opens the file, then file is imported into the database and "magically" it will be in the proper format in the correct columns and rows. I am new to VB 2008 express and the syntax for this eludes me...this is what I need help.

gbianchi · March 18th, 2009, 05:56 PM

And that's why microsoft invent MSDN... :) read the whole bunch and if you need specific help just ask. Take in mind that you need the to know the format in advance (you have to supply a format file for it). If the format will change, you will have to make a formated import proccess (complicate, but not imposible, it only takes time to build).