+ Reply to Thread
Results 1 to 4 of 4

Import Spreadsheet Data into Database

  1. #1
    Registered User
    Join Date
    03-26-2010
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    3

    Import Spreadsheet Data into Database

    Hi,

    I attached an image that describes the data in the spreadsheet I am working with. The spreadsheet is about 700 rows and was created using Excel.

    The end goal is to import a portion of the data into a database (MySQL and probably Oracle). The only data that should be imported is the Fiscal Year, Building, and the monthly data (July through June).

    I really do not want to resort to manually copying/pasting the data into a text file and importing it into the database. Please tell me there is an easier way :-)

    Would you believe all of this data was manually entered ?!

    Thank you for your help,

    Nick
    Attached Images Attached Images

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Import Spreadsheet Data into Database

    Hi welcome,

    yes it can be automated.

    Some questions:
    1. how often is this needed or is it a one off?
    2. how much data is there? Is this all?
    3. Do you have Microsoft access on your PC? It could make the process of getting to your final database easier and more reliable.
    4.What is the table name and field names and types of the target table?

    If the target system is a corporate database will you have permission to update it programatically? Most won't. But there is often an interface or import process for journals or budgets etc.


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Registered User
    Join Date
    03-26-2010
    Location
    Earth
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Import Spreadsheet Data into Database

    Thank you for the quick reply!

    1. how often is this needed or is it a one off?
    It's a one off.

    2. how much data is there? Is this all?
    About 700 rows of data.

    3. Do you have Microsoft access on your PC? It could make the process of getting to your final database easier and more reliable.
    Yes, Access and Excel 2007.

    4.What is the table name and field names and types of the target table?
    I attached a file which includes the SQL for the database.
    The EHEnergyConsumptions table is where the monthly data will live.

    5. If the target system is a corporate database will you have permission to update it programatically?
    I will have administrator access to the database. Our DBA's are available to me if I need their help.

    Thank you for your help!

    Nick
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Import Spreadsheet Data into Database

    The way I would do it is to use access.
    Create two Linked tables:
    1. using your Excel spreadsheet as the source (if the data is neatly organised on one sheet with one header row that is easiest, or you can select the data and create a named range).
    2. using the Db as a source

    Then it is a simple matter to write an update query (there is a wizard or your dba's can do it) to read the rows from the excel table and update the database.

    Before you do the update you are in a perfect position to do some validation queries. eg account numbers in the spreadsheet don't match any in the database.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1