Import multiple Excel files to Database

Howdy,

I have a client with a TON of Excel files he has been using for YEARS to create work tickets and invoices.  He has hundreds and hundreds.  The information is all in certain fields (Example:  Date is K12, Invoice Number is K4, Description is D4, etc).

What is the best approach to puling this info into a database?  A visual basic script?  I want to pull the all in and use the Invoice Number field to distinguish between invoices and work tickets. (Easy enough as all work tickets are labled WORKTICKET).

A point to a direction is what I need!
hydraziAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
In that case, I would run a simple procedure like this to dump the data into a single worksheet, and then simply use Access's import wizard to import the data from there.



Sub Consolidate()

    Dim fso As Object, fld As Object, fil As Object
    Dim SourceWb As Workbook
    Dim SourceWs As Worksheet
    Dim DestWs As Worksheet
    Dim DestRow As Long
    Dim WbWasOpen As Boolean

    Workbooks.Add
    Set DestWs = ActiveSheet
    DestWs.[a1:f1] = Array("Date", "Description", "InvoiceID", "ContactInfo", "Address", "Total")
    DestRow = 1

    Application.ScreenUpdating = False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder("c:\myinvoices")
    For Each fil In fld.Files
        If LCase(fil.Name) Like "*.xls*" Then
            On Error Resume Next
            Set SourceWb = Workbooks(fil.Name)
            If Err = 0 Then
                WbWasOpen = True
            Else
                Err.Clear
                WbWasOpen = False
                Set SourceWb = Workbooks.Open(fil.Path)
            End If
            On Error GoTo 0
            DestRow = DestRow + 1
            DestWs.Cells(DestRow, 1).Resize(1, 6) = Array(SourceWs.Range("data1"), SourceWs.Range("data12"), _
                SourceWs.Range("NO"), SourceWs.Range("e17"), _
                SourceWs.Range("data6") & " " & SourceWs.Range("data7"), SourceWs.Range("TOT"))
            If Not WbWasOpen Then SourceWb.Close False
        End If
    Next

    Application.ScreenUpdating = True

    MsgBox "Done"

End Sub
0
 
dlmilleCommented:
I have done something similar with spreadsheet surveys.  Just put all the files in one directory, write a VBA routine to go through each file, opening up, copying and pasting into your primary sheet. Before you know it, you're done.

Let me know if you need assistance (will need a non-confidential copy of a couple of the files).

Dave
0
 
Patrick MatthewsCommented:
hydrazi,

A macro is probably for the best.

Is the database to be Access, or some other RDBMS?  Can you supply a sample workbook?

Patrick
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
dlmilleCommented:
A photo finish!
0
 
hydraziAuthor Commented:
Wow, no kidding!  :)  I am looking to eventually get it into a MySQL db, but Access is fine for a start.  Just need to get it out of Excel.  I will see if I can get a safe copy for you.  Many thanks for the quick responses.
0
 
dlmilleCommented:
Sounds like matthewspatrick might have a few tricks with Access, and that could be a handy tip.  I think I'll step aside and witness :)

Dave
0
 
hydraziAuthor Commented:
Here is what each one looks like: invoice-workticket.csv
0
 
Patrick MatthewsCommented:
Are the originals Excel workbooks, or CSV files?  If the former, then please post a (sanitized) Excel file.
0
 
hydraziAuthor Commented:
Ok, here is the raw file: INVOICE.xls
0
 
Patrick MatthewsCommented:
hydrazi,

Depending on exactly what details need to get captured and ported, and how the data are to be stored in the database (one big denormalized heap, or an actual normalized design), this has the potential to be fairly simple or very complicated.

Can you provide some more detail on what the expected output would be?

Patrick
0
 
hydraziAuthor Commented:
I would like to put them all into a single table for now.  I will only need to do this once and they will be using a different system from now on.

I would like to get the Date, Description, Invoice ID, Contact Info, Address, and total.

Not much more than that.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.