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

Kayleigh

Member
Local time
Today, 16:08
Joined
Sep 24, 2020
Messages
706
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: 414
  • Screenshot 2021-10-22 130224.png
    Screenshot 2021-10-22 130224.png
    43.5 KB · Views: 226
  • Screenshot 2021-10-22 130414.png
    Screenshot 2021-10-22 130414.png
    32.5 KB · Views: 428

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
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: 539

Kayleigh

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 16:08
Joined
Sep 24, 2020
Messages
706
Yes I would like to timestamp the table names.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
also take note of the sub dropTable(), modify it so
only those filename pattern you make are deleted.
 

Kayleigh

Member
Local time
Today, 16:08
Joined
Sep 24, 2020
Messages
706
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
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
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.
 

Sec3Op41514

New member
Local time
Today, 12:08
Joined
Oct 6, 2023
Messages
6
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.
 

Attachments

  • products_1_10-1-2023_72113.zip
    2.4 KB · Views: 49

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:08
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom