Solved

Import multiple Excel files to Database

Posted on 2011-02-20
11
203 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 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 42

Expert Comment

by:dlmille
ID: 34940073
A photo finish!
0
Independent Software Vendors: 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!

 

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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

739 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