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?

 

[Golom]

Yes ... but you would need to be somewhat pedestrian about how you import the CSV. Normally you would use TransferText to import a CSV file but TransferText won't extract information from a filename and use it as part of the data to be loaded.

The following is just some skeleton code for how I would do it.

nH = FreeFile
Open "C:\SomeDir\MyFile.CSV" For Input As #nH
DataFromFileName = "???"

Do Until EOF(nH)
   Line Input #nH, Buffer
   Fields = Split(Buffer, ",")
   SQL = "INSERT INTO myTable (fld1, fld2, fld3, ...) " & _
         "VALUES ('" & DataFromFileName & "'," & Fields(0) & ", " & Fields(1) & "," & ...)"
   CurrentDb.Execute SQL
Loop