Import CSVs and location from multiple folders and sub folders

[Gareth Jones]

Hi,

I am trying to import a number of CSV's from a number of folders including subfolders. The snag is that I need to record the folder name with the file contents from each folder.

I can import the file name and contents when it is from one folder, but not multiple.

I am able to import the full directory list including the folder/subfolder names but not the contents of the actual files. I have used a function from http://allenbrowne.com/ser-59alt.html fofull location for this.

If i have the full path of the files I need in a seperate table, can I use this to loop through to import the files?

I have tried everything I have found on the subject and believe I'm very close :)

Thanks all
Gareth

[PhilOfWalton]

This will get all the file names that meet your criteria in folders and subfolders.

If you have a routine to import the data, put that in where the Debug statement goes

Sub Test()
 
    Dim colFiles As New Collection
    
    RecursiveDir colFiles, "E:\Phil Data\Access", "*.Csv", True

    Dim vFile As Variant
    For Each vFile In colFiles
        Debug.Print vFile
    Next vFile
    
End Sub

Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop

        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function

Public Function TrailingSlash(strFolder As String) As String
    ' Add a backslah if it's missing
    If Len(strFolder) > 0 Then
        If right(strFolder, 1) = "\" Then
            TrailingSlash = strFolder
        Else
            TrailingSlash = strFolder & "\"
        End If
    End If
    
End Function

Phil

[ADezii]

AIRCODE Example:

1. Let's assume that you have the very simple Table Structure and Data below:
   

   ID  FilePath
   1   C:\Test\File1.csv
   2   C:\Test\File2.csv
   3   C:\Test\File3.csv

2. The following Code will loop thru all the *.csv File Paths contained within the [FilePath] Field, then Import the *.csv File into a Table named with the corresponding Primary Key Field ([ID]) Value.
   

   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim intCtr As Integer
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("tblCSVFiles", dbOpenSnapshot)
   
   With rst
     Do While Not .EOF
       DoCmd.TransferText acImportDelim, , ![ID], ![FilePath], True
         .MoveNext
     Loop
   End With
   
   rst.Close
   Set rst = Nothing

3. After Code Execution you will have three new Tables named 1, 2, and 3.
4. The Code assumes that you have Field Names embedded in the *.csv Files.

[Gareth Jones]

Thanks both for replying. I tried the code from AIRCODE and I am 90% there now.I have a list of tables with the contents of the CSV's, and each table name corresponds with the ID of the path in the directory table.

I have tried and tried however to find a way of linking the table name with the ID of the directory table. Is there a way of doing this? I presume it will involve some sort of INSERT INTO query where I would insert the tablename into the corresponding table.

I have tried looking for a function to loop through all tables and add the table name to the table.

Is this possible via VBA or a function do you think?

[Gareth Jones]

I am even closer now. I have used the below code to append all the tables into one:

Dim db As DAO.Database
 Dim rs As DAO.Recordset
 
 Const cstrAppend As String = "INSERT INTO maintable SELECT * FROM "
 
   Set db = CurrentDb()
    Set rs = db.OpenRecordset("tableoftables")
 
   Do Until rs.EOF
       db.Execute cstrAppend & rs!TableName, dbFailOnError
       rs.MoveNext
    Loop
 
   rs.Close: Set rs = Nothing
    Set db = Nothing

I have then tried this one to do the same as the above, but append the tablename as well

Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim cstrAppend As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tableoftables")
 
cstrAppend = "INSERT INTO maintable SELECT *, """ & rs!TableName & """, FROM & rs!TableName"
 
   Do Until rs.EOF

       db.Execute cstrAppend, dbFailOnError
       rs.MoveNext
    Loop
 
   rs.Close: Set rs = Nothing
    Set db = Nothing

However the second one gives me this error;
No Destination field name in INSERT INTO statement ("993")

993 is my ID above which is correct.
Any ideas?

Thanks

[PhilOfWalton]

AFIK The Append query takes the form of
INSERT INTO MainTable (Field1, Field2.....)
SELECT FieldA, FieldB... FROM SecondTable;

So I think you need to define the corresponding fields from both tables.
As, in your case, the SecondTable is a calculated value, I think I would try
FROM Chr$(34) & rs!TableName & Chr$(34);

I have a pet hatred of """, and always use Chr$(34) which is a double quote

Phil

[Gareth Jones]

I have tried all ways of using Chr$(34) but cannot seem to get the syntax right. I have gone a slightly different way below and am agonisingly close now...

When I hover over the strSQL I can see
"INSERT INTO maintable select *, "993", from "993""

All the rs!tablename variables are correct.

Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim cstrAppend As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tableoftables")
 
strsql = "INSERT INTO maintable SELECT *, """ & rs!TableName & """, FROM """ & rs!TableName & """"
   Do Until rs.EOF

       db.Execute strsql, dbFailOnError
       rs.MoveNext
    Loop
 
   rs.Close: Set rs = Nothing
    Set db = Nothing

I still get the error No Destination field name in INSERT INTO statement ("993")

Do i need to specify an alias in the statement? I have tried a few ways but I just get a syntax error.

[PhilOfWalton]

Please read my previous post. You have not defined the fields specifically in the Main table that you want to append, nor defined specifically the fields that the data is coming from in your input tables.

[Gareth Jones]

I've tried doing this but unfortunately I keep getting syntax errors. All the fields from other tables copy across ok as they are picked up in the SELECT *. The only issue is the table name variable rs!tablename. I have added a field to the new table called "tablename" however I dont believe this makes a difference. I believe I need to specify what field to append the tablename to, but I am sure of the syntax.

Appreciate your help with this :)

[PhilOfWalton]

Without knowing the field names in the two tables, it is difficult to be of further help. Perhaps you would enlighten us

Phil

[Gareth Jones]

No problem, these are all test names for now, but there is a table containing all the table names called "tableoftab les" with [ID] and [tablename]

The fields in each table I am appending from has [field1] and [field2]. These copy append fine into a table called "maintable" .

I have added a field called [tablename] into this "maintable" as it is the same name as the field in "tableoftab les" The only issue appears to be appending the table name :(

[ADezii]

I used my previous Code Segment to illustrate how you can utilize a Table Name and Primary Key, inserting a combination of both into a Main Table:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTableName As String
Dim strSQL As String

strTableName = "tblCSVFiles"

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strTableName, dbOpenSnapshot)

With rst
  Do While Not .EOF
    strSQL = "INSERT INTO [MainTable] ([MyID]) VALUES('" & ![ID] & "_" & strTableName & "')"
      CurrentDb.Execute strSQL, dbFailOnError
        .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

[PhilOfWalton]

Try strSQL = "INSERT INTO [MainTable] ([MyID]) SELECT ![ID] & "_" & !strTableName AS NewTable"

You certainly need an exclamation mark before strTableName
If ID is numeric, you may need Cstr(!ID)

Phil

[ADezii]

You certainly need an exclamation mark before strTableName

Not required, it is a String Variable, not a Field Name.

If ID is numeric, you may need Cstr(!ID)

Not required, it will be coerced to a String.

[Gareth Jones]

We are a step closer as this now adds the table ID to the main table, however it doesnt add the contents of the other tables. Is there a way we can incorporate SELECT * from the other tables as well as appending the table name?

What I believe I need is something like

strSQL = "INSERT INTO [MainTable] SELECT *,([MyID]) VALUES('" & ![ID] & "_" & strTableName & "')"

But this doesnt work :(

Really appreciate all your help with this one.