Import CSV files into new tables and append

[Kayleigh]

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.

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

 

[arnelgp]

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.

 

[Kayleigh]

Thank you will take a look. Obviously I won't have more than 600 columns.

 

[arnelgp]

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.

 

[Kayleigh]

Yes I would like to timestamp the table names.

 

[arnelgp]

also take note of the sub dropTable(), modify it so
only those filename pattern you make are deleted.

 

[Kayleigh]

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]

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.

 

[Kayleigh]

Can you advise how I would do that?

 

[Sec3Op41514]

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.

It looks like your file is VERY close to what it is i've been searching for for past week. I need a file import browser to be able to add a file to a table (or to delete an existing table and recreate it with the same name while importing the file data). The file being imported changes daily as the file is named per the date and time it was exported from the origin server. I'm a noob at this access vba stuff and its hard for me to figure out where in your code its making 1 column. I will attach my sample file showing what the columns and stuff are that i need imported.
"CatalogID, ID, Name, Distributor" are the headers. I'm always looking to learn more! Thanks for your time with this.

 

[Kayleigh]

Hi @Sec3Op41514 I've just read your post here.
Did you get sorted?

 

[Pat Hartman]

The file being imported changes daily as the file is named per the date and time it was exported from the origin server.

It is better to link the table rather than import it. That allows you to maintain a consistent table name in the Access database but change the file it is linked to.