import delimited text file into Access via VB.NET

[toon10]

I'm trying to rewrite an old VB6 application in VB.NET. My old VB6 routine used to detect when a text file was placed in a folder and then it would import that delimited text file into an Access database.

The old way of doing this doesn't work. I thought the way I would handle this is to use a FileSystemWatcher to run the import routine when the text file was detected in the import folder.

I haven't figured out how to use this control properly yet despite finding some code and examples on the Internet.

My major concern is that I can't seem to find anything on importing delimited files into Access via VB.NET.

Does anyone know of any good web sites or samples that they can point me to?

The VB6 code I used to use is

    Open ImportDataFile For Input As 1 'import selected text file

    While Not EOF(1) 'loop through datafile
        Line Input #1, strTemp 'capture data line by line
        ImportString = ImportString & strTemp 'ImportString is now a valid record
        ImportString = Replace(strTemp, "'", "''") 'counteracts problems with apostrophe's 
        fitemarray = Split(ImportString, "!") 'split the record into fields 
            f1 = "'" & fitemarray(0) & "'" 'Invoice No
            f2 = "'" & fitemarray(1) & "'" 'Invoice Line


etc...

Thanks

 

[jebenson]

Check out the following link:

Import | Delimited text file into Access Database




I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[toon10]

Thanks for the reply. Ironically, I found this very page after posting here!

 

[toon10]

I have created the following function based on what I have seen on the Tinterweb.

    Function ImportTextToAccess() As Boolean

        Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Progs\data\CostDB;Persist Security Info=True;Jet OLEDB:Database Password=MyPassword")
        Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblCost] (Record Type, Part Number, Description) SELECT F1, F2, F3 FROM'[Text;DATABASE=C:\Progs\Import Data;].[pcost.txt]")

        AccessCommand.ExecuteNonQuery()
        AccessConn.Close()

    End Function

It gives the error message "ExecuteNonQuery: Connection Property has not been initialised"

Has anyone come across this before?

 

[jebenson]

AccessCommand.Connection = AccessConn

AccessCommand.ExecuteNonQuery()



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[toon10]

Thanks for the replies guys.

I've solved it. After extensive searching, it became apparent that I needed to use a schema.ini file to specify the construction of the text file.

For future reference if anyone's interested, the ini file looks like:

[pcost.txt]
Format=Delimited(!)
ColNameHeader=False
MaxScanRows=0
CharacterSet=ANSI
Col1=F1 Text Width 18
Col2=F2 Integer Width 15
Col3=F3 Text Width 40
Col4=F4 Text Width 1
Col5=F5 Text Width 10
Col6=F6 Text Width 20
Col7=F7 Text Width 10
Col8=F8 Date Width 10
Col9=F9 Integer Width 15
Col10=F10 Integer Width 15
Col11=F11 Integer Width 15
Col12=F12 Integer Width 15
Col13=F13 Integer Width 15
Col14=F14 Integer Width 15
Col15=F15 Integer Width 15

This needs to reside in the same folder as the text file.

My code now looks like:

        Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblCost] ([Record Type], [Part Number], Description, [Procurement Type], BOM, Component, Node, [Valid From], [Qty Per], [Raw Material], [Sub Contract], [Material Overhead], [Direct Labour], [Labour Overhead], [Factory Overhead]) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15 FROM [Text;DATABASE=" & varImportFolder & "].[" & varImportFile & "]")

        With AccessCommand
            .Connection = AccessConn
            .CommandType = CommandType.Text
            .ExecuteNonQuery()
        End With

        AccessConn.Close()