Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Import multiple Excel files to Database

Posted on 2011-02-20
11
Medium Priority
?
210 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 42

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 93

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 42

Expert Comment

by:dlmille
ID: 34940073
A photo finish!
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 42

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
 

Author Comment

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

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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

580 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