[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

On a computer without Access, how do I import a csv into an access table? (using vb.net 2005)

Ok, I have this program which downloads csv files in order to update it's tables (in an access db). I want to import these csvs into my access db, (this is all in vb.net 2000).

First I tried the Access 11.0 library, but when I tried running the code to automate access and execute my macro to transferText it erred out. This is the code I was using:

Private Sub CallMacroInAccess(ByVal dbPath$, ByVal csvPath$, ByVal tableName$)

        Dim oAccess As Access.ApplicationClass
        'Start Access and open the database.
        oAccess = New Access.ApplicationClass
        'CreateObject("Access.Application")
        'oAccess.Visible = True

        'You will need to put the path to your own database here.
        oAccess.OpenCurrentDatabase(dbPath, False)

        'Run the macro.
        If tableName = "AffInfo" Then
            'oAccess.Run("ImportTxtFile_AffInfo")
            oAccess.DoCmd.RunMacro("ImportTxtFile_AffInfo")
            'oAccess.DoCmd.TransferText(Access.AcTextTransferType.acImportDelim, _
            '   "", tableName, csvPath)
        Else
               oAccess.DoCmd.RunMacro("ImportTxtFile_SplitNames")
        End If
               oAccess.DoCmd().Quit(Access.AcQuitOption.acQuitSaveNone)
        System.Runtime.InteropServices.Marshal. _
           ReleaseComObject(oAccess)
        oAccess = Nothing

    End Sub

Anyways, I need to know how to do a TransferText into my access db when there Access is NOT installed on the computer.. Does anyone know a good fast way to do this? (My csvs are over 400,000 records apiece, and semi-sizable.)

Another thing I've tried is this builtin csv reader.. here is the code for that. It was agonizingly slow but it did work.. (perhaps because my csv has fields which include vbcrlf inside them to seperate strings, that is my theory..)


    Private Function LoginForm_importCSV2(ByVal pathName As String, ByVal AffTrue_SplitFalse As Boolean) As Long
        myDB.InitOleDBConnection()
        '
        Me.ProgressBar1.Value = 0
        Me.ProgressBar1.Maximum = countLinesInTextFile(pathName)
        Me.ProgressBar1.Visible = True
        Dim recordsAdded As Long = 0
        Dim fields As String(), fieldNames As String()
        Dim delimiter As String = ","
        gImportDupKnt = 0
        '
        Using parser As New FileIO.TextFieldParser(pathName)
            parser.SetDelimiters(delimiter)
            fieldNames = parser.ReadFields()
            While Not parser.EndOfData
                ' Read in the fields for the current line
                fields = parser.ReadFields()
                Me.ProgressBar1.Value = Me.ProgressBar1.Value + 1
                Me.ProgressBar1.Refresh()
                If UBound(fieldNames) <> UBound(fields) Then Stop
                'If myDB.writeImportSet(fieldNames, fields) = True Then
                Dim result As Boolean
                If AffTrue_SplitFalse = True Then
                    Debug.Print(Replace(Join(fields, ","), vbCrLf, "|"))
                    result = write_AffInfo(fieldNames, fields)
                Else
                    result = write_SplitNames(fieldNames, fields)
                End If
                If result = True Then
                    recordsAdded = recordsAdded + 1
                Else
                    gImportDupKnt += 1
                End If
                Me.fldDoing.Text = recordsAdded & ":" & gImportDupKnt
                My.Application.DoEvents()
            End While
            'importCSV = recordsAdded
        End Using
cleanexit:
        Me.ProgressBar1.Visible = False
        myDB.CloseOleDBConnection()
        Return recordsAdded
    End Function

    Private Function write_AffInfo(ByVal fieldnames(), ByVal fieldvalues()) As Boolean
        Dim mySQL$, strFields$, strValues$
        strFields = UCase(makeListForInsertSQL(fieldnames, "[", "]"))
        strValues = UCase(makeListForInsertSQL(fieldvalues, "'", "'"))
        mySQL = "INSERT INTO AffInfo " & strFields & " VALUES " & strValues & ";"
        myOLEDBCommand = New OleDb.OleDbCommand(mySQL, myOleDBConnection)
        Dim result As Long = 0
        On Error GoTo myErrHandler
        result = myOLEDBCommand.ExecuteNonQuery()
cleanup:
        myOLEDBCommand.Dispose()
        Return IIf(result > 0, True, False)
        Exit Function
myErrHandler:
        If InStr(Err.Description, "duplicate") > 0 Then Err.Clear()
        GoTo cleanup
    End Function

What's the best way to do this? Another function I'm worried about is how to compact & repair a db if they don't have access, but perhaps that is a different question..

Thanks Experts! ~ Michael
0
JeffreyDurham
Asked:
JeffreyDurham
  • 3
1 Solution
 
Bob LearnedCommented:
Proof-of-concept, using the Microsoft Text Drive:

Importing CSV Data and saving it in database
http://www.codeproject.com/cs/database/FinalCSVReader.asp?print=true

Bob
0
 
JeffreyDurhamAuthor Commented:
Ok, I tried to do what you're saying I think.. I found code anyways to use the Microsoft Text Driver and here it is.. but it produces this error:

Public Sub LoadCSVIntoDataTable(ByVal csvPath$, ByVal tableName$)
        Dim conn As System.Data.Odbc.OdbcConnection
        Dim strConnstr$ = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvPath + ";"
        conn = New Odbc.OdbcConnection(strConnstr)

        Dim da As New System.Data.Odbc.OdbcDataAdapter("Select * From []", strConnstr)
        da.Fill(myDS, "CSV")
        'myOLEDBAdapter.Fill(myDS, "Output")

        myfuncs.debugPrintDataTable(myDS.Tables("CSV"))
    End Sub

ERROR [HY024] [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY024] [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
0
 
JeffreyDurhamAuthor Commented:
I also tried to use the OLEDB library, I'm probably doing this wrong, it keeps saying that the C:\creditwatchexpress\affInfo_update.csv is not a valid path name..
This is the function I was attempting to use:


    Public Sub LoadCSVIntoDT(ByVal csvPath$, ByVal tableName$)
       
        Try
            Try
                Dim strConnectionString As String
                Dim strPathToTextFile As String

                strPathToTextFile = csvPath
                strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPathToTextFile & ";" & _
                    "Extended Properties=""text;HDR=NO;FMT=Delimited"""
                Dim objConnection As New System.Data.OleDb.OleDbConnection(strConnectionString)

                objConnection.Open()
                Dim objDA As New OleDbDataAdapter("SELECT * FROM CSV.txt", objConnection)

                objDA.Fill(myDS, tableName)

                myfuncs.debugPrintDataTable(myDS.Tables(tableName))
                objConnection.Close()

            Catch ex As Exception
                Debug.Print(ex.ToString())
            End Try

        Catch Exc As Exception
            Debug.Print("Error: " & Exc.Message)
        End Try
       
    End Sub
0
 
JeffreyDurhamAuthor Commented:
Ok, I've altered my functions like so:

    Public Sub LoadCSVIntoDataTable(ByVal csvPath$, ByVal tableName$)
        Dim conn As System.Data.Odbc.OdbcConnection
        Dim strConnstr$ = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvPath + ";"
        conn = New Odbc.OdbcConnection(strConnstr)

        Dim da As New System.Data.Odbc.OdbcDataAdapter("Select * From AffInfo_Update.csv", strConnstr)
        da.Fill(myDS, "CSV")
        'myOLEDBAdapter.Fill(myDS, "Output")

        myfuncs.debugPrintDataTable(myDS.Tables("CSV"))
    End Sub

    Public Sub LoadCSVIntoDT(ByVal csvFolderPath$, ByVal csvFileName$, ByVal tableName$)

        Try
            Try
                Dim strConnectionString As String
                Dim strPathToTextFile As String

                strPathToTextFile = csvFolderPath & csvFileName
                strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & csvFolderPath & ";" & _
                    "Extended Properties=""text;HDR=NO;FMT=Delimited"""
                Dim objConnection As New System.Data.OleDb.OleDbConnection(strConnectionString)

                objConnection.Open()
                Dim objDA As New OleDbDataAdapter("SELECT * FROM " & csvFileName, objConnection)

                objDA.Fill(myDS, tableName)

                myfuncs.debugPrintDataTable(myDS.Tables(tableName))
                objConnection.Close()

            Catch ex As Exception
                Debug.Print(ex.ToString())
            End Try

        Catch Exc As Exception
            Debug.Print("Error: " & Exc.Message)
        End Try

    End Sub

Now using these arguments, I can actually make it work:

?mydb.LoadCSVIntoDataTable("C:\CreditWatchExpress\", "zTable")
?mydb.LoadCSVIntoDT("C:\CreditWatchExpress\", "AffInfo_Update.csv", "zTable")

Thank you, The Learned One, for your help with this!
~Michael
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now