Import multiple Excel data files into one database

[thysonj]

This is a tricky one!
I am currently looking for the most efficient and quickest way to import multiple Excel files into a db. I will explain as much as possible and hopefully some of you have some ideas.

-We are talking about 700 Excel files
-The files are all formatted the same.
-Each file has 5 workbooks.
-The files are not formatted well for straight importation into a db.

Some ideas I have...
-I batted around the idea of writing a script to use each file as a datasource and to write out properlly formatted csv files or something in that ilk. This won't work since I would have to go through each file and worksheet to make named ranges for the queries.
-I also played with the idea of creating one more worksheet in each file that could colate the needed data into a nice tabular form that could easily be imported into a db(not really easily rather more easily than other ideas). This idea has promise except that the worksheets are not named the same in each file. 4 of the worksheets are named the same(VRS,Screenings,Training,Certifications) but one is different in every file as it is named for the person who file this is(these are personnel files). This means we'd have to create a new worksheet in each file and then copy+paste the code for the page and then change the references to some of the data on the differently named worksheets. If anyone can give me an idea of how to easily copy a worksheet from one book to another and have it perform the same way with proper reference changes and all that could be the best way.
-I played around with the idea of exporting each files worksheets into csv and then writing a script to format them. This could work but I don't know how to export the excel files(the worksheets) automatically. Going through and exporting 3500 worksheets by hand is NOT an option.

I have some other ideas floating around but I haven't vetted them enough yet to see if they are possible.

Any ideas would helpful.. even if the idea only solves part of the problem please mention as I think this may end up being a very collaborative effort.

 

[SkipVought]

thysonj,

Now we're in the right place!

I'd do as much programmatic conditioning in the Excel environment before sucking them into your db.

Put all your workbooks into a single folder if you can. Then they can all be accessed using the FileSystemObject.

But initially, I'd focus on the logic for conditioning a workbook's data to make consistent meaningful data. In general, you can loop thru the worksheets in a workbook and process the objects (ranges) on each sheet.

for each ws in activeworkbook.worksheets
  with ws
    with usedrange
      FirstRow = .row
      LastRow = FirstRow + .rows.count - 1
      FirstCol = .column
      LastCol = FirstCol + .columns.count - 1
    'insert a loop here to massage the data 


    end with
  end with
next

Since I don't know what your data looks like, I can't go any further.

Since you also mentioned that you have 4 worksheets that are named the same in each workbook, then maybe you do something like this...

for each ws in activeworkbook.worksheets
  with ws
    select case .name
      case "sheet1", "sheet2", "sheet3", "sheet4" 'or whatever names
        with usedrange
          FirstRow = .row
          LastRow = FirstRow + .rows.count - 1
          FirstCol = .column
          LastCol = FirstCol + .columns.count - 1
        'insert a loop here to massage the data 


        end with
      case else
    'this is the oddball sheet 
       ThisSheetName = .Name
'...
    end select
  end with
next

You'll have to ask some specific question if you need help going forward.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[thysonj]

This is very helpful. I have limited experience with VBA but I think I can get this working. I will most likely be back with specific questions over the next few days. Thanks.

 

[SkipVought]

Here's some code that will process Excel files in a folder, incorporating my former code...

Sub MainProcess()
   Dim fs, f, fl, fc, fn
   Dim ThisWs As Workbook, ws As Worksheet
   fn = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.GetFolder(ParsePath(fn))
   Set fc = f.Files
   For Each fl In fc
      Set ThisWb = Workbooks.Open(fl)
      For Each ws In ThisWb.Worksheets
        With ws
          Select Case .Name
            Case "sheet1", "sheet2", "sheet3", "sheet4" 'or whatever names
              With UsedRange
                FirstRow = .Row
                LastRow = FirstRow + .Rows.Count - 1
                FirstCol = .Column
                LastCol = FirstCol + .Columns.Count - 1
              'insert a loop here to massage the data
      
      
              End With
            Case Else
          'this is the oddball sheet
             ThisSheetName = .Name
      '...
          End Select
        End With
      Next
   Next
End Sub
Function ParsePath(fn)
   p = Split(fn, "\")
   For i = 0 To UBound(p) - 1
      If i = 0 Then
         ParsePath = p(i)
      Else
         ParsePath = ParsePath & "\" & p(i)
      End If
   Next
End Function

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[fredericofonseca]

The following can be used to save all worksheets on a folder.

Obviously you will need to tweek the file naming (e.g. add the original workbook name to the file name.

You can use FSO to loop on the folder containing the excel workbooks, or you can load the full list into a text file and then process that text file.


Set wWork_book = Workbooks.Open(FileName)
For iWorksheet = 1 To wWork_book.Worksheets.Count
wWork_book.Worksheets(iWorksheet).SaveAs FileName:="c:\temp\fredtst\" & wWork_book.Worksheets(iWorksheet).Name, FileFormat:=xlCSV
Next iWorksheet



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com