Import CSV files into new tables and append (1 Viewer)

Krayna

Member
Local time
Today, 03:15
Joined
Sep 24, 2020
Messages
562
I am writing a procedure to import CSV files into a table. The CSV file will be structured in one of two different methods so I would like to detect the field titles so I can suitably append to the import table. I started writing a method but got a little stuck:
1) How do I allow the user to browse for the CSV files?
2) How do I append the new data to my existing table and specify which columns I wish to use?
Any tips would be appreciated.

This is the code and also attached screenshots of the two types of CSV files and the table I wish to import to.

Code:
Public Sub import()
    Dim FileName, FilePathName, Path, FileNameList() As String
    Dim FileCount As Integer
    Dim tblName, tblNames() As String

    DoCmd.SetWarnings False
    Path = "C:\"
    Shell (Path)
    FileName = Dir(Path & "")
    
    
    While FileName <> "" And Right(FileName, 3) = "csv"
        FileCount = FileCount + 1
        ReDim Preserve FileNameList(1 To FileCount)
        FileNameList(FileCount) = FileName
        FileName = Dir()
    Wend
    
    If FileCount > 0 Then
        For FileCount = 1 To UBound(FileNameList)
            tblName = CStr(Now()) + "_" + FileCount
            ReDim Preserve tblNames(1 To FileCount)
            tblNames(FileCount) = tblName
            FilePathName = Path & FileNameList(FileCount)
            DoCmd.TransferText transferType:=acImportDelim, TableName:=tblName, FileName:=FilePathName, hasfieldnames:=True
        Next
    End If
    DoCmd.SetWarnings True
End Sub
 

Attachments

  • Screenshot 2021-10-22 130042.png
    Screenshot 2021-10-22 130042.png
    25.9 KB · Views: 104
  • Screenshot 2021-10-22 130224.png
    Screenshot 2021-10-22 130224.png
    43.5 KB · Views: 33
  • Screenshot 2021-10-22 130414.png
    Screenshot 2021-10-22 130414.png
    32.5 KB · Views: 104

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:15
Joined
May 7, 2009
Messages
15,030
i also made similar db from a request post from another forum.
the catch is everytime you run it it will re-create the table/tables.
the scenario is that the op has a csv that has more than 600 columns
and wanted to split it by 254 field + 1 autonumber (255 is the limit number of
fields).
hope you find use in this.
 

Attachments

  • import_666_fields.accdb
    504 KB · Views: 119

Krayna

Member
Local time
Today, 03:15
Joined
Sep 24, 2020
Messages
562
Thank you will take a look. Obviously I won't have more than 600 columns.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:15
Joined
May 7, 2009
Messages
15,030
another thing is that the table will be in format table1, table2, etc.
so if you have similar table name, you need to modify the code to
use other name.
 

Krayna

Member
Local time
Today, 03:15
Joined
Sep 24, 2020
Messages
562
Yes I would like to timestamp the table names.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:15
Joined
May 7, 2009
Messages
15,030
also take note of the sub dropTable(), modify it so
only those filename pattern you make are deleted.
 

Krayna

Member
Local time
Today, 03:15
Joined
Sep 24, 2020
Messages
562
I have adapted your code and it works very well for my purposes.

The only trouble I have is when filenames contain spaces and/or symbols. Is there any way round this? I have tried adding quotes as has been advised in multiple places to no avail.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:15
Joined
May 7, 2009
Messages
15,030
it will open those filename with space, but other "symbol" will not.
maybe you can have a function that rename/copy the csv to a "sanitized" name.
 

Users who are viewing this thread

Top Bottom