Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Import multiple Excel files to Database

Posted on 2011-02-20
11
Medium Priority
?
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

610 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