SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Location
    London, UK
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Importing Excel sheets into sql server

    I am developing an app for a demo. The thing is the data that i have to use are all in excel and i need to get this into a database and the data it self is full of formulas, with some of the column headers appearing of the vertically instead of horizontal. I have tried to rearrange the data but the formulas mean that some of the cut and paste are not working. I have there done what i know and imported the excel sheet into sql but now i need to normalise the tables.

    To do so i need to say copy all the data in some columns from one or more tables and create a new table from it. Can someone help me with such a query(s)? Also if anybody has dealt with this whole excel to sql server import thing, i'll appreciate some tips and advice.
    Prov. 13:20 - He that walketh with wise men shall be wise: but a
    companion of fools shall be destroyed

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2007
    Location
    London, UK
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well i have found a solution to copying table columns to form another table "INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1" but will still welcome any general tips when on copying excel sheet into sql server
    Prov. 13:20 - He that walketh with wise men shall be wise: but a
    companion of fools shall be destroyed

  3. #3
    SitePoint Zealot avolia's Avatar
    Join Date
    Jun 2001
    Location
    USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you first need to fix your spreadsheets. I am not sure if you have accomplished this yet or if you are still struggling with that.

    You can copy the values in your spreadsheets by doing a copy + past special then values. This will past the values and remove any formulas behind your cells.

    Then the import to SQL part. Once all your sheets in your spreadsheets are formatted correctly, then I find it best to export the data in the spreadsheets to csv or flat text format before importing to SQL.

    Then use SQL integration services (SQL 2005+) or DTS (SQL 2000) to create a text transform procedure and import your CSV files.

    The main thing is getting your spreadsheets formatted correctly. Otherwise, you will have no luck importing this into a database format.

    Good luck.
    NetMaxx.com: WordPress Premium Themes Directory


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
  •