Read Excel workbook to database

  • Hello

    I'm working on different kinds of excel workbooks, that need to be read to databases.

    Which kind of approach do you suggest i should use?

    Read the sheets to a single recordset, and then dump it into the database?

    Read row by row?

    Do you know if there is some ready-made solution for this kind of task?


  • Re: Read Excel workbook to database

    What database solution are you using? If you're using SQL Server you can use SSIS to import the data for you providing nothing is dynamic

  • Re: Read Excel workbook to database


    The database is sybase based.

    I have different excel sheets that should first be saved into databases, and then merge those into a single database.

    The database structure is identical in all of them:

    • cust_id (PK) -> id number
    • cust_alt (PK) -> alternative name identifier
    • cust_name -> customer name
    • date -> date

    I thought the best solution could be:

    1. Save excel sheet to .csv with tab delimited
    2. Read .csv to database
    3. Combine databases

    Do you have other suggestions ?

    On step 3. what would be the best way to combine them ? Can you give me SQL help how to execute combining ?


  • Re: Read Excel workbook to database

    I am a little short of time at the moment so I don't have time to clean these up to be more generic, but you may find the following two bits of code useful:

    The second piece of code is simply a file browser. The first pulls a CSV file into a temporary table and then updates it onto an existing table.

    You should be able to adapt these to suit your exact situation. You may not need to create a temporary table in your case but simply update the new CSV file onto the existing table

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!