Convert office vba code to vb.net for sql xpress 2005 to ctored procedure import from text file

vb.net 2008
sql server express 2005 sp3


reference question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26701701.html#a34428530

The following code works fine from access 2003 vba

What I need:
I need to create a stored procedure , from the vba code example,  and be able to import text files into a sql server express database and table..

My database is called:
MyDataImports

the fields will be same as in access and all "Types"  are nVarChar(200)

I need to run the code from a command button on a form in vb.net.

connection code

        '  "Data Source= .\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Data.mdf;Initial Catalog=MyDataImports
;Persist Security Info=True;User ID=sa;Password=veeger;Connect Timeout=0;User Instance=False"




Thanks
fordraiders








Option Compare Database

Private Sub Command0_Click()
Dim path As String, i As Long
path = "C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\"
i = read_files(path)
MsgBox i & " files imported", vbOKOnly
End Sub



Option Compare Database
Option Explicit

Function read_files(pathd As String) As Long
Dim smask As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As file
smask = ".txt"
read_files = 0
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(pathd)
For Each objFile In objFolder.Files
  If MatchSpec(objFile.Name, smask) Then
        read_f (objFile)
        read_files = read_files + 1
  End If
Next objFile
End Function
Private Function MatchSpec(FileName As String, FileSpec As String) As Boolean
MatchSpec = False
If Right(FileName, Len(FileSpec)) = FileSpec Then MatchSpec = True
End Function
Sub read_f(filen As String)
Dim Str As String, FileNum As Integer
Dim Arr() As String
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ImportedTextFiles")
FileNum = FreeFile
Open filen For Input As #FileNum
Do While Not EOF(FileNum)
    Line Input #FileNum, Str
    If Len(Str) > 0 Then
        Arr = Split(Str, "|")
        rst.AddNew
        rst![fldMfgname] = Arr(0)
        rst![fldDescription] = Arr(1)
        rst![fldkitNumber] = Arr(2)
        rst![fldLocation1] = Arr(3)
        rst![fldLocation2] = Arr(4)
        rst![fldMfnum] = Arr(5)
        rst![fldAwwg] = Arr(6)
        rst.Update
    End If
Loop
rst.Close
Close #FileNum
Set rst = Nothing
Set dbs = Nothing
End Sub


text file example:
LAWSON|HAMMERLOCK COTTER PINS, SMALL WIRE DIA.|LP70|D|02|1021|2UJH7
LAWSON|HAMMERLOCK COTTER PINS, SMALL WIRE DIA.|LP70|D|03|1042|2UJJ2
LAWSON|HAMMERLOCK COTTER PINS, SMALL WIRE DIA.|LP70|D|04|1000|2WZW1
LAWSON|HAMMERLOCK COTTER PINS, SMALL WIRE DIA.|LP70|D|05|1001|2WZW2
LAWSON|HAMMERLOCK COTTER PINS, SMALL WIRE DIA.|LP70|D|06|1002|2WZW3

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
ok, Thanks I have this ok...But its the looping part..changing the file name during the loop...
FordraidersAuthor Commented:
codecruiser,
I think this will work but getting the connection string correct ?

' don't need this one
Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;"   <---- dont need this one


' Need this one

'  "Data Source= .\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Data.mdf;Initial Catalog=Cart_Data;Persist Security Info=True;User ID=sa;Password=fgnelllll;Connect Timeout=0;User Instance=False"



'==========================================


Dim objConn As SqlConnection
        Dim ds As New DataSet
        Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;"   <---- dont need this one
        '  "Data Source= .\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Data.mdf;Initial Catalog=Cart_Data;Persist Security Info=True;User ID=sa;Password=fgnelllll;Connect Timeout=0;User Instance=False"  <--- need this one
     
        objConn = New SqlConnection
        objConn.ConnectionString = m_strConnection
        objConn.Open()
        Try

            Dim fname As String
            fname = Dir("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files
            Do While Len(fname) > 0
                '   If File.Exists(sLeadFile) Then

                ' ------ Load the data from the .CSV file: ----------
                Dim strSQL As String
                strSQL = "BULK INSERT dbo.list_staging FROM '" & fname & "' " & _
                          "WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"""

                Dim objCommand As SqlCommand
                objCommand = New SqlCommand(strSQL, objConn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                objConn.Close()
            Loop



        Catch ex As Exception
            ' sResultText = sResultText & "<BR>" & ex.Message
        End Try
        ' end if
    End Sub
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

FordraidersAuthor Commented:
this code is starting to work...but getting an errrormessage on the insert sql part..

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.  Unclosed quotation mark after the character string ''.
Dim objConn As SqlConnection
        Dim ds As New DataSet
       Dim m_strConnection As String = "Server=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\iCart_Data.mdf;Initial Catalog=iCart_Data;Persist Security Info=True;User ID=sa;Password=veeger;Connect Timeout=0;User Instance=False"



                objConn = New SqlConnection
        objConn.ConnectionString = m_strConnection
        objConn.Open()
        Try

            Dim fname As String
            fname = Dir("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files
            Do While Len(fname) > 0
                '   If File.Exists(sLeadFile) Then

                ' ------ Load the data from the pipe delimited file: ----------
                Dim strSQL As String
                strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM '" & fname & "' " & _
                          "WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"""

                Dim objCommand As SqlCommand
                objCommand = New SqlCommand(strSQL, objConn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                objConn.Close()
            Loop



        Catch ex As Exception
            ' sResultText = sResultText & "<BR>" & ex.Message
        End Try
        ' end if
    End Sub



PROGRAM|UNIT|LELVEL A|I|1||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|2||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|3||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|4||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|5||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|6||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|I|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|1||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|2||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|3||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|4||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|5||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|6||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|H|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|1||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|2||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|3||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|4||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|5||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|6||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|G|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|1||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|2||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|3||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|4||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|5||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|6||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|F|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|1||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|2||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|3||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|4||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|5||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|6||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|E|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|1|571|5XU95|FIBER WASHERS 3/32 I.D., 1/4 O.D., 1/32 THICK|LAWSON|571|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|2|572|5XU96|FIBER WASHERS 1/8 I.D., 1/4 O.D., 1/32 THICK|LAWSON|572|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|3|591|5XY17|FIBER WASHERS 13/16 I.D., 1-1/8 O.D., 1/32 THICK|LAWSON|591|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|4|593|5XY18|FIBER WASHERS 15/16 I.D., 1-1/4 O.D., 1/32 THICK|LAWSON|593|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|5|573||FIBER WASHERS 5/32 I.D., 5/16 O.D., 1/32 THICK|LAWSON|573|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|6|574||FIBER WASHERS 3/16 I.D., 5/16 O.D., 1/16 THICK|LAWSON|574|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|D|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|1|575||FIBER WASHERS 3/16 I.D., 3/8 O.D., 1/32 THICK|LAWSON|575|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|2|576||FIBER WASHERS 1/4 I.D., 3/8 O.D., 1/16 THICK|LAWSON|576|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|3|577||FIBER WASHERS 1/4 I.D., 1/2 O.D., 1/32 THICK|LAWSON|577|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|4|578||FIBER WASHERS 5/16 I.D., 7/16 O.D., 1/16 THICK|LAWSON|578|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|5|579||FIBER WASHERS 5/16 I.D., 9/16 O.D., 1/16 THICK|LAWSON|579|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|6|580||FIBER WASHERS 3/8 I.D., 1/2 O.D., 1/16 THICK|LAWSON|580|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|C|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|1|581||FIBER WASHERS 3/8 I.D., 5/8O.D., 1/32 THICK|LAWSON|581|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|2|582||FIBER WASHERS 7/16 I.D., 11/16 O.D., 1/32 THICK|LAWSON|582|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|3|583||FIBER WASHERS 1/2 I.D., 5/8 O.D., 1/32 THICK|LAWSON|583|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|4|584||FIBER WASHERS 1/2 I.D., 3/4 O.D., 1/16 THICK|LAWSON|584|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|5|585||FIBER WASHERS 9/16 I.D., 13/16 O.D., 1/32 THICK|LAWSON|585|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|6|586||FIBER WASHERS 5/8 I.D., 3/4 O.D., 1/16 THICK|LAWSON|586|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|B|8||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|1|CUSTNUM587||FIBER WASHERS 5/8 I.D., 7/8 O.D., 1/32 THICK|LAWSON|587|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|2|588||FIBER WASHERS 11/16 I.D., 15/16 O.D., 1/32 THICK|LAWSON|588|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|3|589||FIBER WASHERS 3/4 I.D., 1 O.D., 1/16 THICK|LAWSON|589|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|4|590||FIBER WASHERS 3/4 I.D., 1-1/16 O.D., 1/32 THICK|LAWSON|590|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|5|592||FIBER WASHERS 7/8 I.D., 1-3/16 O.D., 1/16THICK|LAWSON|592|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|6|594||FIBER WASHERS 1 I.D., 1-5/16 O.D., 1/16 THICK|LAWSON|594|LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|7||||LAWSON||LP89|FIBER WASHES|
PROGRAM|UNIT|LELVEL A|A|8||||LAWSON||LP89|FIBER WASHES|

Open in new window

CodeCruiserCommented:
First make sure that columns in the table and the file match.

Try changing code to this

            Dim fname As String
            fname = IO.Directory.GetFiles("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files
            Dim objCommand As SqlCommand
            objCommand = New SqlCommand(strSQL, objConn)
            Dim strSQL As String
            For I as Integer = 0 to fName.Length - 1
                strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM '" & fname & "' " & _
                          "WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"""
                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
            Next
FordraidersAuthor Commented:
getting same error...codecruiser
FordraidersAuthor Commented:
codecruiser, I know the culprit...its the file name syntax...

I hard code the dir and file name and it imported the data...but looping problems. I only  have 72 lines in the textfile...and its importing 1.3 million


 Dim strSQL As String
            strSQL = ""
            Dim fname As String
            fname = Dir("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files
            Do While Len(fname) > 0
                Dim objCommand As SqlCommand
                objCommand = New SqlCommand(strSQL, objConn)

                For I As Integer = 0 To fname.Length - 1
                    strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM  'C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\LP89_EXPORT_Update.txt'  WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"
                    objCommand.CommandText = strSQL
                    objCommand.ExecuteNonQuery()
                Next
            Loop






FordraidersAuthor Commented:
ok....sorry this works   hard code...correct lines imported...

Dim strSQL As String
            strSQL = ""

            Dim objCommand As SqlCommand
            objCommand = New SqlCommand(strSQL, objConn)

            strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM  'C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\LP89_EXPORT_Update.txt'  WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"
            objCommand.CommandText = strSQL
            objCommand.ExecuteNonQuery()
FordraidersAuthor Commented:
according to this:

http://bytes.com/topic/sql-server/answers/83459-variables-sql-statements

According the the Books Online syntax, BULK INSERT doesn't take a variable
as a file name. A workaround is to build a SQL statement string and execute
????
FordraidersAuthor Commented:
ok...got this to work on the variable...

now I just need it to loop through each file in the dir..

' this works using a variable name... !!!!


Dim strSQL As String
            strSQL = ""
            Dim fname As String
            fname = Dir("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files
            fname = "C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\" & fname
            Dim objCommand As SqlCommand
            objCommand = New SqlCommand(strSQL, objConn)
            strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM   '" & fname & "'  WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"
            objCommand.CommandText = strSQL
            objCommand.ExecuteNonQuery()
           
FordraidersAuthor Commented:
OK FINALLY, yes a variable can work !


Dim strSQL As String
            strSQL = ""
            Dim fname As String
            fname = Dir("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files
            fname = "C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\" & fname
            Do While fname <> ""

                Dim objCommand As SqlCommand
                objCommand = New SqlCommand(strSQL, objConn)
                strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM   '" & fname & "'  WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"
                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()

                fname = Dir()

            Loop


FordraidersAuthor Commented:
ok spoke to soon...

not loping all files  only one file is getting inserted ????


any help please ???

fordraiders

FordraidersAuthor Commented:
ok, this time for sure ! !!!


Dim objConn As SqlConnection
        Dim ds As New DataSet
        Dim m_strConnection As String = "Server=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\iCart_Data.mdf;Initial Catalog=iCart_Data;Persist Security Info=True;User ID=sa;Password=veeger;Connect Timeout=0;User Instance=False"
        objConn = New SqlConnection
        objConn.ConnectionString = m_strConnection
        objConn.Open()
        Try
            Dim strSQL As String
            strSQL = ""
            Dim fname As String
            fname = Dir("C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\*.txt")   'folder containing text files

            Do While fname <> ""
                fname = "C:\Program Files\Crs Enterprise\Vendor Kits\Import iQ exports\" & fname
                Dim objCommand As SqlCommand
                objCommand = New SqlCommand(strSQL, objConn)
                strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM   '" & fname & "'  WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )"
                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                fname = Dir()
            Loop

        Catch ex As Exception
            ' sResultText = sResultText & "<BR>" & ex.Message
        End Try
        ' end if
FordraidersAuthor Commented:
repost and i'll  issue points codecruiser !

Thanks !
CodeCruiserCommented:
Sorry I was away from computer. Did you get it working?
FordraidersAuthor Commented:
yep my last post...
CodeCruiserCommented:
Great :-)
FordraidersAuthor Commented:
Thanks for the direction !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.