Importing files and including File name as Column

[sstump]

Hey all. Need a little assistance here. I originally found this code online somewhere and someone here at my office helped me to modify it. However now I need another modification and the most knowledgable person here is out for the weekend. So I'm hoping you guys can help me out.

The code goes out to a network drive and sweeps everything into one table in Access, then moves the file to a processed folder. I need to modify it so it includes the file name as a column in the table, which contains the date. I don't want the date the file is created because it can be off by a day, but the file name is always correct.

Here's my code. Can anyone point me in the right direction?

Public Function Import_From_TEXT()
     'Macro Loops through the specified directory (strPath)
     'and imports ALL *.dat files to specified table in the Access
     'Database.
     
    Const strPath As String = "\\mynetworklocation\LetterOutbound\" 'Directory Path
    Const strNewPath As String = "\\mynetworklocation\LetterOutbound\Processed\" 'new path to archive
    
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
    Dim strOldName, strNewName As String
         
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.dat")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Function
    End If
     'cycle through the list of files & import to Access
     'creating a new table
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acImportFixed, "dailyspecs", "tmpLetter_Files", strPath & strFileList(intFile), True
         'strFilename = strFileList(intFile)
        strOldName = strPath & strFileList(intFile)
        strNewName = strNewPath & strFileList(intFile)
        Name strOldName As strNewName
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Function

 

[sstump]

Just to clarify I want a column and for it to list the files that the data comes from.

I'm dealing with about 300 files initially to get it started, then I'll setup a daily batch run.

 

[sstump]

Come on I know it's Friday and all, but I could really use the assistance. Anyone??

 

[scottian]

This is how i do it.
I think its what youre looking for.



For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportFixed, "dailyspecs", "tmpLetter_Files", strPath & strFileList(intFile), True
'==============================================
MySQL = "UPDATE table1 SET table1.FileId = '" & FileName & "' WHERE (((table1.FileId) Is Null));"
DoCmd.RunSQL MySQL
'=================================================
'strFilename = strFileList(intFile)
strOldName = strPath & strFileList(intFile)
strNewName = strNewPath & strFileList(intFile)
Name strOldName As strNewName
Next


"My God! It's full of stars...

 

[sstump]

That didn't seem to work either. I assume I replace "table1" with "tmpLetter_Files" as below:

'==============================================
MySQL = "UPDATE tmpLetter_Files SET tmpLetter_Files.field20 = '" & FileName & "' WHERE (((tmpLetter_Files.field20) Is Null));"
DoCmd.RunSQL MySQL
'=================================================

,but it just left all fields in 'field20' blank (which is the blank field I had the Specs build to input this info into).

 

[PHV]

which is the blank field I had the Specs build to input this info into
You may try this variation:
[tt]... & "' WHERE Trim(field20 & '')=''"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sstump]

Still blank...just to reiterate here's my code again. Am I missing something?

Public Function Import_From_TEXT()
     'Macro Loops through the specified directory (strPath)
     'and imports ALL Excel files to specified table in the Access
     'Database.
     
    Const strPath As String = "\\mynetworklocation\LetterOutbound\" 'Directory Path
    'new path to archive
    Const strNewPath As String = "\\mynetworklocation\LetterOutbound\Processed\"
    
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
    Dim strOldName, strNewName As String
         
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.dat")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Function
    End If
     'cycle through the list of files & import to Access
     'creating a new table called MyTable
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acImportFixed, "dailyspecs", "tmpLetter_Files", strPath & strFileList(intFile), True
'==============================================
MySQL = "UPDATE tmpLetter_Files SET tmpLetter_Files.field20= '" & FileName & "' WHERE Trim(field20 & '')=''"
DoCmd.RunSQL MySQL
'=================================================
              'strFilename = strFileList(intFile)
        strOldName = strPath & strFileList(intFile)
        strNewName = strNewPath & strFileList(intFile)
        Name strOldName As strNewName
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Function

 

[PHV]

MySQL = "UPDATE tmpLetter_Files SET tmpLetter_Files.field20= '" & [!]strFileList(intFile)[/!] & "' WHERE Trim(field20 & '')=''"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sstump]

Whoohooo...that got it. Thanks a lot guys!