Importing part of file name into field in Access table

[DataWife]

I need to import several .csv files into one table in Access, but one of the key fields I need to use to identify the relevant records from the .csv file is only held in the filename and not within the data inside the file. Is there a way to extract part of the filename and add it as a field in Access when the file is imported?

 

[ousoonerjoe]

Does the column already exist in the table or will this be a new column?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[DataWife]

It's going to be a new column

 

[ousoonerjoe]

i'm sure there's a better way to do this, but my first thought is to create an ALTER TABLE script to add the new column. Then do your INSERT/UPDATE to the table using your filename. Build the SQL statements on the fly in your code.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[dilettante]

You say "in Access" but do you mean "in VB?" If not maybe you want one of the Access forums.

In VB6 you might do something like (fragment):

Private Sub DBCreate()
    'Create new MDB.
    Dim catDB As Object
    Set catDB = CreateObject("ADOX.Catalog")
    catDB.Create strConn
    With catDB
        'Create empty table.
        Set cnDB = .ActiveConnection
        With cnDB
            .Execute "CREATE TABLE Employees (" _
                   & "[Source] TEXT(255) WITH COMPRESSION, " _
                   & "[Date] DATETIME, " _
                   & "[Time] DATETIME, " _
                   & "[EmployeeNo] TEXT(20) WITH COMPRESSION)"
        End With
        cnDB.Close
        Set cnDB = Nothing
    End With
End Sub

Private Function Extract(ByVal CSVName As String) As String
    Extract = Left$(CSVName, InStrRev(CSVName, ".") - 1)
End Function

Private Function DBImport(ByVal CSVName As String) As Long
    'Assumes cnDB is open to the MDB.
    Dim intFile As Integer
    
    intFile = FreeFile(0)
    Open strCSVFolder & "\schema.ini" For Output As #intFile
    Print #intFile, "[" & CSVName & "]"
    Print #intFile, "MaxScanRows=1"
    Print #intFile, "Format=CSVDelimited"
    Print #intFile, "ColNameHeader=False"
    Print #intFile, "DateTimeFormat=YYYY/MM/DD HH:NN:SS"
    Print #intFile, "Col1=""Date"" DateTime"
    Print #intFile, "Col2=""Time"" DateTime"
    Print #intFile, "Col3=EmployeeNo Text Width 20"
    Close #intFile
    
    cnDB.Execute _
        "INSERT INTO [Employees] ([Source], [Date], [Time], [EmployeeNo]) " _
      & "SELECT '" _
      & Extract(CSVName) _
      & "' AS [Source], [Date], [Time], " _
      & "[EmployeeNo] FROM [Text;Database=" _
      & strCSVFolder _
      & "].[" _
      & CSVName _
      & "]", _
        DBImport, _
        adCmdText Or adExecuteNoRecords
    
    Kill App.Path & "\schema.ini"
End Function

Private Sub DBOpen()
    Set cnDB = New ADODB.Connection
    cnDB.Open strConn
    cmdImport.Enabled = True
End Sub

Private Sub cmdImport_Click()
    Log "Text imported: " & CStr(DBImport("abc.txt")) & " records."
    Log "Text imported: " & CStr(DBImport("xyz.txt")) & " records."
End Sub

DBImport has the logic you might be after, taken along with the Extract() function to pull out part of the import file name.

 

[dilettante]

Oops!

[tt] Kill App.Path & "\schema.ini"[/tt]

... should read:

[tt] Kill strCSVFolder & "\schema.ini"[/tt]

But they were the same in my test case.