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