Import from a folder with file names (1 Viewer)

jcmaad · Jan 29, 2019

Hi everyone,

I have a folder of text files. Text files have quite a bit of text in it (over 30,000 characters).
I am trying to import those files into a table along with the file name. So the table imported into Access would have two columns: FileName and FileContent.
FileName is Short Text and FileContent is a Long Text.

I found some codes that can import all files from a folder, but not the file name.

moke123 · Jan 29, 2019

I found some codes that can import all files from a folder, but not the file name.

Are you able to import the text and all you need is to get the file name?
Post the code you are using as I'm sure you can get the filename somewhere in it.

Micron · Jan 29, 2019

Why would you want to copy text into a table when you can just open the text file from Access? Your table will have one record with 30,000 characters in a field? I must not be interpreting the question correctly as the goal doesn't make sense to me.
If this is a csv file, then it still might not make sense since a csv can be linked as a table.
Nor should you store the files in an Access table as an embedded object. The better method is to store the file path in a table, but not the file.

jcmaad · Jan 29, 2019

Micron said:
Why would you want to copy text into a table when you can just open the text file from Access? Your table will have one record with 30,000 characters in a field? I must not be interpreting the question correctly as the goal doesn't make sense to me.
If this is a csv file, then it still might not make sense since a csv can be linked as a table.
Nor should you store the files in an Access table as an embedded object. The better method is to store the file path in a table, but not the file.

Here is the situation. I am connecting to a SQL database. This Db already has records. For each record, there is a field which needs to be updated with the content of the text file. SQL db has the field with the filename, which i was planning to use to join two tables.

jcmaad · Jan 29, 2019

moke123 said:
Are you able to import the text and all you need is to get the file name?
Post the code you are using as I'm sure you can get the filename somewhere in it.

This is the code that used:

Code:

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
    
    Dim objFS As Object, objFolder As Object
    Dim objFiles As Object, objF1 As Object
    Dim strFolderPath As String
    
    strFolderPath = "C:\Import TXT files\"
    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFS.GetFolder(strFolderPath)
    Set objFiles = objFolder.files
    
    For Each objF1 In objFiles
        If Right(objF1.Name, 3) = "txt" Then
            DoCmd.TransferText acImportDelim, "TextImportSpecs", "tblImportedFiles", strFolderPath & objF1.Name, False
            Name strFolderPath & objF1.Name As "C:\Archived TXT Files\" & objF1.Name 'Move the files to the archive folder
        End If
    Next
    
    Set objF1 = Nothing
    Set objFiles = Nothing
    Set objFolder = Nothing
    Set objFS = Nothing
    
bImportFiles_Click_Exit:
    Exit Sub
    
bImportFiles_Click_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume bImportFiles_Click_Exit
    
End Sub

Micron · Jan 29, 2019

Then I suspect the solution is to run Update sql after the transfer but inside the loop, using objF1.Name as the value for file name. I you wan the whole path, then
"C:\Archived TXT Files" & objF1.Namel However, if anyone moves the file, the full path data becomes meaningless.

Import from a folder with file names (1 Viewer)

jcmaad

New member

moke123

AWF VIP

Micron

AWF VIP

jcmaad

New member

jcmaad

New member

Micron

AWF VIP

Similar threads

Users who are viewing this thread