Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing part of file name into field in Access table

Status
Not open for further replies.

DataWife

Programmer
Jun 18, 2008
4
GB
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?

 
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.
 
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.
 
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):
Code:
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top