Solved

Import multiple Excel files to Database

Posted on 2011-02-20
11
199 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:hydrazi
  • 4
  • 4
  • 3
11 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 34940063
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34940066
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34940073
A photo finish!
0
 

Author Comment

by:hydrazi
ID: 34940092
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
 
LVL 41

Expert Comment

by:dlmille
ID: 34940103
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:hydrazi
ID: 34940137
Here is what each one looks like: invoice-workticket.csv
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34940150
Are the originals Excel workbooks, or CSV files?  If the former, then please post a (sanitized) Excel file.
0
 

Author Comment

by:hydrazi
ID: 34940167
Ok, here is the raw file: INVOICE.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34944454
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
 

Author Comment

by:hydrazi
ID: 34944497
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34944615
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now