import csv to table using vba 1

[Marksimus]

Hi all,

I need to use VBA to import a csv file into an existing Access 2000 table.

Only starting out in VBA and programming and haven't got a clue where to start and cannot find anything useful on the net.

Could somebody please point me in the right direction.

Cheers

Marksimus

 

[ploeryan]

Hello

try using the transfertext method - can be done from VBA or macros. eg,

DoCmd.TransferText acImportDelim, "", "TABLENAME", "c:\name.csv", True, ""

 

[Marksimus]

That's great but...

1) The table has 10 fields, the csv has 4 and I need to specify which column goes into each field.
2) I need to add a unique ID, which includes a variable from a form, to each record.

Have a nasty feeling this could be real complex.

 

[Nikita6003]

No, doesn't have to be complex at all.

Either open the text file using the VBA

Open l_sFileName For Input As #<IntFilenum>

, parse out the fields (it's coma delimited so use Mid in comb with Instr to get all text up the next comma) & assign each of your 4 column values to their respective fields
Or use (e.g.) ADO to set up a connection to the text file
Example:

Private m_cnTextFile As ADODB.Connection

Public Sub OpenConnection(p_sPath As String)
    Dim l_sConnection As String
    
    Set m_cnTextFile = New ADODB.Connection
    l_sConnection = "Provider=MSDASQL.1;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & p_sPath & ;" & _
                    "Initial Catalog=tblNames.txt;Extended Properties='text;HDR=NO;FMT=Delimited'"
    
    With m_cnTextFile
        .ConnectionString = l_sConnection
        .ConnectionTimeout = 0
        .Open
    End With
    
End Sub

SubTestTextDriver
    Dim l_rsTest As New ADODB.Recordset
    Dim l_lRow As Long
    Dim l_iCounter As Integer
    
    Call OpenConnection("C:\")
    l_rsTest.Open "Select * from Filename.csv", m_cnTextFile, adOpenStatic, adLockReadOnly, adCmdText
    l_lRow = 1
    
    For l_iCounter = 0 To l_rsTest.Fields.Count - 1
         ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, l_iCounter + 1) = l_rsTest.Fields(l_iCounter).Name
    Next l_iCounter

    'You can now treat the text file like any ordinary recordset
    Do Until l_rsTest.EOF
        Debug.Print l_rsTest.Fields(0).Value
        Debug.Print l_rsTest.Fields(1).DataFormat
        Debug.Print l_rsTest.Fields(2).Value
        Debug.Print l_rsTest.Fields(3).Value

        l_rsTest.MoveNext
    Loop

    Set l_rsTest = Nothing
End Sub

There are more ways than this - of course - but this'll get you started, I hope

Cheers
Nikki

 

[GHolden]

Hi,

Does you csv file have column headings in it which match the field names that you want to copy into?

There are two ways to write error-free programs; only the third one works.