Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: How to use VB to replicate the File\Get External Data\Import command

    All,
    Ran into an issue importing a CSV file. In our current process, we get a export CSV file from a 3rd party application that contains trailer numbers. Some of the trailer numbers begin with a zero, which does show up in the CSV file when you open in wordpad. However, when we open the file in Excel to save and run the transfer spreadsheet function, it drops the zero from the front of the trailer number.

    When I simply use the menu option File/Get External Data\Import option, and select text files and import into a new table, I get the zeros with no issue. So I just need to be able to automate that process into a function or module for the users.

    The file path and name I am bringing in is G:\Share\DataYD\YardTrailers\RPT00831_Yard_Check_r eport.csv

    Any quick code I can use to make this a button click operation? Since these are updates, I will simply delete the previous table and create this as a new one to replace it each time it is ran to create a new fresh list of trailer numbers.

    Thanks all!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a possible solution:
    Code:
    Public Sub ImportCSVFile(ByVal FileName As String, ByVal TableName As String, _
                             Optional ByVal Separator As String = ",", Optional ByVal Newtable As Boolean = False)
     '
     ' FileName:    Full path + name of the .csv file.
     '
     ' TableName:   Name of the destination table (must exist in the current database).
     '
     ' Separator:   Character used as separator in the .csv file (optional, default = ",").
     '
     ' NewTable:    Delete and recreate the destination table when True (optional, default = False).
     '
     ' Example:     ImportCSVFile "U:\SomeCSVFile.csv", "Tbl_InputBuffer", ";", True
     '
                    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim var As Variant
        Dim strLine As String
        Dim intHandle As Integer
        Dim i As Integer
        
        If Newtable = True Then
            '
            ' Copy the structure of the existing table.
            '
            Set dbs = CurrentDb
            Set tdf = dbs.TableDefs(TableName)
            ReDim var(0 To tdf.Fields.Count - 1)
            For i = 0 To UBound(var)
                With tdf.Fields(i)
                    var(i) = Array(.Name, .Type, .Size, .Attributes)
                End With
            Next i
            Set tdf = Nothing
            Set dbs = Nothing
            '
            ' Drop the existing table.
            '
            CurrentDb.Execute "DROP TABLE " & TableName, dbFailOnError
            '
            ' Recreate the table.
            '
            Set dbs = CurrentDb
            Set tdf = dbs.CreateTableDef(TableName)
            For i = 0 To UBound(var)
                Set fld = tdf.CreateField(var(i)(0), var(i)(1), var(i)(2))
                tdf.Fields.Append fld
            Next i
            dbs.TableDefs.Append tdf
            Set tdf = Nothing
            Set dbs = Nothing
        Else
            '
            ' Empty the existing table.
            '
            CurrentDb.Execute "DELETE * FROM " & TableName, dbFailOnError
        End If
        '
        ' Import data from the .csv file.
        '
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenDynaset)
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        Do Until EOF(intHandle) = True
            Line Input #intHandle, strLine
            var = Split(strLine, Separator)
            rst.AddNew
            For i = 0 To UBound(var)
                If i = rst.Fields.Count Then Exit For
                rst.Fields(i) = Val(var(i))   ' To remove the heading zero's.
                ' Use: rst.Fields(i) = var(i) ' To keep the heading zero's.
            Next i
            rst.Update
        Loop
        Close #intHandle
        rst.Close
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    Thanks, tested and working!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •