468,539 Members | 1,607 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

Import CSVs and location from multiple folders and sub folders

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
Mar 18 '16 #1

✓ answered by ADezii

Not sure if you are still interested, but I do believe that I did come up with a purely based Access solution. Won't go into details unless you request them, just look at the Attached Demo.

23 3725
PhilOfWalton
1,430 Expert 1GB
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

Expand|Select|Wrap|Line Numbers
  1. Sub Test()
  2.  
  3.     Dim colFiles As New Collection
  4.  
  5.     RecursiveDir colFiles, "E:\Phil Data\Access", "*.Csv", True
  6.  
  7.     Dim vFile As Variant
  8.     For Each vFile In colFiles
  9.         Debug.Print vFile
  10.     Next vFile
  11.  
  12. End Sub
  13.  
  14. Public Function RecursiveDir(colFiles As Collection, _
  15.                              strFolder As String, _
  16.                              strFileSpec As String, _
  17.                              bIncludeSubfolders As Boolean)
  18.  
  19.     Dim strTemp As String
  20.     Dim colFolders As New Collection
  21.     Dim vFolderName As Variant
  22.  
  23.     'Add files in strFolder matching strFileSpec to colFiles
  24.     strFolder = TrailingSlash(strFolder)
  25.     strTemp = Dir(strFolder & strFileSpec)
  26.     Do While strTemp <> vbNullString
  27.         colFiles.Add strFolder & strTemp
  28.         strTemp = Dir
  29.     Loop
  30.  
  31.     If bIncludeSubfolders Then
  32.         'Fill colFolders with list of subdirectories of strFolder
  33.         strTemp = Dir(strFolder, vbDirectory)
  34.         Do While strTemp <> vbNullString
  35.             If (strTemp <> ".") And (strTemp <> "..") Then
  36.                 If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
  37.                     colFolders.Add strTemp
  38.                 End If
  39.             End If
  40.             strTemp = Dir
  41.         Loop
  42.  
  43.         'Call RecursiveDir for each subfolder in colFolders
  44.         For Each vFolderName In colFolders
  45.             Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
  46.         Next vFolderName
  47.     End If
  48.  
  49. End Function
  50.  
  51. Public Function TrailingSlash(strFolder As String) As String
  52.     ' Add a backslah if it's missing
  53.     If Len(strFolder) > 0 Then
  54.         If right(strFolder, 1) = "\" Then
  55.             TrailingSlash = strFolder
  56.         Else
  57.             TrailingSlash = strFolder & "\"
  58.         End If
  59.     End If
  60.  
  61. End Function
Phil
Mar 19 '16 #2
ADezii
8,800 Expert 8TB
AIRCODE Example:
  1. Let's assume that you have the very simple Table Structure and Data below:
    Expand|Select|Wrap|Line Numbers
    1. ID  FilePath
    2. 1   C:\Test\File1.csv
    3. 2   C:\Test\File2.csv
    4. 3   C:\Test\File3.csv
    5.  
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim intCtr As Integer
    4.  
    5. Set MyDB = CurrentDb
    6. Set rst = MyDB.OpenRecordset("tblCSVFiles", dbOpenSnapshot)
    7.  
    8. With rst
    9.   Do While Not .EOF
    10.     DoCmd.TransferText acImportDelim, , ![ID], ![FilePath], True
    11.       .MoveNext
    12.   Loop
    13. End With
    14.  
    15. rst.Close
    16. 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.
Mar 19 '16 #3
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?
Mar 20 '16 #4
I am even closer now. I have used the below code to append all the tables into one:

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.  Dim rs As DAO.Recordset
  3.  
  4.  Const cstrAppend As String = "INSERT INTO maintable SELECT * FROM "
  5.  
  6.    Set db = CurrentDb()
  7.     Set rs = db.OpenRecordset("tableoftables")
  8.  
  9.    Do Until rs.EOF
  10.        db.Execute cstrAppend & rs!TableName, dbFailOnError
  11.        rs.MoveNext
  12.     Loop
  13.  
  14.    rs.Close: Set rs = Nothing
  15.     Set db = Nothing
  16.  
I have then tried this one to do the same as the above, but append the tablename as well

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.  Dim rs As DAO.Recordset
  3.  Dim cstrAppend As String
  4.  
  5. Set db = CurrentDb()
  6. Set rs = db.OpenRecordset("tableoftables")
  7.  
  8. cstrAppend = "INSERT INTO maintable SELECT *, """ & rs!TableName & """, FROM & rs!TableName"
  9.  
  10.    Do Until rs.EOF
  11.  
  12.        db.Execute cstrAppend, dbFailOnError
  13.        rs.MoveNext
  14.     Loop
  15.  
  16.    rs.Close: Set rs = Nothing
  17.     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
Mar 20 '16 #5
PhilOfWalton
1,430 Expert 1GB
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
Mar 20 '16 #6
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.

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.  Dim rs As DAO.Recordset
  3.  Dim cstrAppend As String
  4.  
  5. Set db = CurrentDb()
  6. Set rs = db.OpenRecordset("tableoftables")
  7.  
  8. strsql = "INSERT INTO maintable SELECT *, """ & rs!TableName & """, FROM """ & rs!TableName & """"
  9.    Do Until rs.EOF
  10.  
  11.        db.Execute strsql, dbFailOnError
  12.        rs.MoveNext
  13.     Loop
  14.  
  15.    rs.Close: Set rs = Nothing
  16.     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.
Mar 21 '16 #7
PhilOfWalton
1,430 Expert 1GB
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.
Mar 21 '16 #8
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 :)
Mar 21 '16 #9
PhilOfWalton
1,430 Expert 1GB
Without knowing the field names in the two tables, it is difficult to be of further help. Perhaps you would enlighten us

Phil
Mar 21 '16 #10
No problem, these are all test names for now, but there is a table containing all the table names called "tableoftables" 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 "tableoftables" The only issue appears to be appending the table name :(
Mar 21 '16 #11
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim strTableName As String
  4. Dim strSQL As String
  5.  
  6. strTableName = "tblCSVFiles"
  7.  
  8. Set MyDB = CurrentDb
  9. Set rst = MyDB.OpenRecordset(strTableName, dbOpenSnapshot)
  10.  
  11. With rst
  12.   Do While Not .EOF
  13.     strSQL = "INSERT INTO [MainTable] ([MyID]) VALUES('" & ![ID] & "_" & strTableName & "')"
  14.       CurrentDb.Execute strSQL, dbFailOnError
  15.         .MoveNext
  16.   Loop
  17. End With
  18.  
  19. rst.Close
  20. Set rst = Nothing
Mar 21 '16 #12
PhilOfWalton
1,430 Expert 1GB
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
Mar 21 '16 #13
ADezii
8,800 Expert 8TB
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.
Mar 21 '16 #14
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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO [MainTable] SELECT *,([MyID]) VALUES('" & ![ID] & "_" & strTableName & "')"
But this doesnt work :(

Really appreciate all your help with this one.
Mar 21 '16 #15
ADezii
8,800 Expert 8TB
@Gareth:
Sorry, but I am a little confused at this point. Is there any chance of you Uploading a subset of your DB, stripped of any sensitive information, along with an exact explanation of what you are looking for and what the correct results should be?
Mar 21 '16 #16
Thanks for taking the time on this. I have uploaded the DB for you to look at. The DB just contains the functions to make this work as a concept.

First I click button1 which runs the function in module 1. This imports the directory listing of the specifc folder including subfolders, which creats [tbldirectory]

Button2 then creates a new table from [tbldirectory] that combines the path and CSV file name into [tbldirectory2]. It then imports the contents of the CSVs into seperate tables. Each table name is the ID of [tbldirectory2]. (You will see these are 1011, 1012 etc)

Button3 then appends the contents of all the above tables into one [Maintable] Its at this point I need the table name of each table (1011, 1012 etc) to be appended to the maintable. This is so I know the location of each CSV. Otherwise [maintable] is just data with no reference to where it orginated from.

Let me know if you need anymore information, thanks.
Attached Files
File Type: zip LM.zip (103.7 KB, 39 views)
Mar 22 '16 #17
ADezii
8,800 Expert 8TB
I've looked at your Database and this is what I see:
  1. You want to Append the Data in Tables 1011, 1012, 1013, 1014, 1015, and 1016 to the Table maintable.
  2. Tables 1011 thru 1016 consist of different Fields.
  3. maintable is a conglomeration of all the Field Names in Tables 1011 thru 1016.
  4. If the above statements are correct, then you have a difficult task ahead of you. As you Append each Table to maintable you must first synchronize the Fields to Append. A case in point would be:
    Table 1011 has the following Fields which must be Appended to maintable along with the Table Name:
    Expand|Select|Wrap|Line Numbers
    1. [db_name]
    2. [min]
    3. [max]
    4. [name1]
    5.  
  5. Tables 1012 thru 1016 may/may not have these Fields as well as additional Fields.
Mar 22 '16 #18
I see what you mean. There may be a way of appending the table name to each table individually before appending them all together.

Something along the lines of for each table ID in tbldirectory2, append the ID to the table with the same ID. I can then combine all these into one table which I have done already. That is all I need to do TBH.
Mar 22 '16 #19
ADezii
8,800 Expert 8TB
I think that it can actually be done, but I will not be able to attempt it until Friday at the earliest. I'll check back in then, and if you are still interested, I'll see what I can come up with.
Mar 23 '16 #20
I've got it :) I started from scratch and it though it may be easier to append the folder path using Excel. So the method I used to automate the whole process was to first import the folder structure into Access using button1 in the DB. Then I exported this folder structure into Excel. I then used the below code to import the content in:

Expand|Select|Wrap|Line Numbers
  1. Sub step1()
  2.  
  3. Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("sheet1")
  4.  
  5. wsMstr.UsedRange.Clear
  6.  
  7. Call step2
  8.  
  9. End Sub
  10.  
Expand|Select|Wrap|Line Numbers
  1. Sub step2()
  2.  
  3. Call Combined
  4.  
  5. End Sub
  6.  
Expand|Select|Wrap|Line Numbers
  1. Sub Combined()
  2.  
  3. If Sheets("sheet2").Range("A2") = "" Then
  4. Exit Sub
  5.  
  6. Else
  7.  
  8. Call ImportCSVsWithReference
  9.  
  10. Rows("2:2").Select
  11. Selection.Delete Shift:=xlUp
  12.  
  13. Call step2
  14.  
  15. End If
  16.  
  17. End Sub
Expand|Select|Wrap|Line Numbers
  1. Sub ImportCSVsWithReference()
  2.  
  3. Dim wbCSV   As Workbook
  4. Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("sheet1")
  5. Dim fPath   As String:      fPath = Sheets("sheet2").Range("A2") 'path to CSV files, include the final \
  6. Dim fCSV    As String
  7.  
  8. Application.ScreenUpdating = False  'speed up macro
  9.  
  10. fCSV = Dir(fPath & "*.csv") 'start the CSV file listing
  11.  
  12.     Do While Len(fCSV) > 0
  13.       'open a CSV file
  14.         Set wbCSV = Workbooks.Open(fPath & fCSV)
  15.       'insert col A and add CSV name
  16.         Columns(1).Insert xlShiftToRight
  17.         Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name & fPath
  18.       'copy date into master sheet and close source file
  19.         ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
  20.         wbCSV.Close False
  21.       'ready next CSV
  22.         fCSV = Dir
  23.     Loop
  24.  
  25. Application.ScreenUpdating = True
  26.  
  27. End Sub
  28.  

I then imported the resulting 25k rows back into Access :)

Thank you all again for your help in getting this to a resolution. Really appreciate it.
Mar 23 '16 #21
ADezii
8,800 Expert 8TB
Not sure if you are still interested, but I do believe that I did come up with a purely based Access solution. Won't go into details unless you request them, just look at the Attached Demo.
Attached Files
File Type: zip LM_Revised.zip (38.8 KB, 41 views)
Mar 24 '16 #22
This is perfect. I will use your one as it does exactly what I need and does not need the steps to Excel.

Appreciate the time you took to create this. Thanks :)
Mar 26 '16 #23
ADezii
8,800 Expert 8TB
Glad it all worked out for you. Just keep in mind that the Code is based on two MAJOR assumptions:
  1. The Imported *.csv Files will always be imported into Tables whose Names are Numeric, namely: 1011, 1022, 1234, 6724, etc. No other Tables in your Database, aside from the Imported CSVs, have Names that are purely Numeric.
  2. All Fields in the imported Tables have exact counterparts, both in Name and Data Types, in maintable.
Mar 27 '16 #24

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mark Everett | last post: by
reply views Thread by Jaco Karsten | last post: by
1 post views Thread by moijes12 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.