Import CSVs and location from multiple folders and sub folders

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #16
    @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?

    Comment

    • Gareth Jones
      New Member
      • Feb 2011
      • 72

      #17
      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

      Comment

      • ADezii
        Recognized Expert Expert
        • Apr 2006
        • 8834

        #18
        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:
          Code:
          [db_name]
          [min]
          [max]
          [name1]
        5. Tables 1012 thru 1016 may/may not have these Fields as well as additional Fields.

        Comment

        • Gareth Jones
          New Member
          • Feb 2011
          • 72

          #19
          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.

          Comment

          • ADezii
            Recognized Expert Expert
            • Apr 2006
            • 8834

            #20
            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.

            Comment

            • Gareth Jones
              New Member
              • Feb 2011
              • 72

              #21
              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:

              Code:
              Sub step1()
              
              Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("sheet1")
              
              wsMstr.UsedRange.Clear
              
              Call step2
              
              End Sub
              Code:
              Sub step2()
              
              Call Combined
              
              End Sub
              Code:
              Sub Combined()
              
              If Sheets("sheet2").Range("A2") = "" Then
              Exit Sub
              
              Else
              
              Call ImportCSVsWithReference
              
              Rows("2:2").Select
              Selection.Delete Shift:=xlUp
              
              Call step2
              
              End If
              
              End Sub
              Code:
              Sub ImportCSVsWithReference()
              
              Dim wbCSV   As Workbook
              Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("sheet1")
              Dim fPath   As String:      fPath = Sheets("sheet2").Range("A2") 'path to CSV files, include the final \
              Dim fCSV    As String
              
              Application.ScreenUpdating = False  'speed up macro
              
              fCSV = Dir(fPath & "*.csv") 'start the CSV file listing
              
                  Do While Len(fCSV) > 0
                    'open a CSV file
                      Set wbCSV = Workbooks.Open(fPath & fCSV)
                    'insert col A and add CSV name
                      Columns(1).Insert xlShiftToRight
                      Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name & fPath
                    'copy date into master sheet and close source file
                      ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
                      wbCSV.Close False
                    'ready next CSV
                      fCSV = Dir
                  Loop
               
              Application.ScreenUpdating = True
              
              End Sub

              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.

              Comment

              • ADezii
                Recognized Expert Expert
                • Apr 2006
                • 8834

                #22
                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

                Comment

                • Gareth Jones
                  New Member
                  • Feb 2011
                  • 72

                  #23
                  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 :)

                  Comment

                  • ADezii
                    Recognized Expert Expert
                    • Apr 2006
                    • 8834

                    #24
                    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.

                    Comment

                    Working...