Kayleigh
Member
- Local time
- Today, 23:23
- Joined
- Sep 24, 2020
- Messages
- 709
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.
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