locked
import data into SQL Server RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Using 'Import/Export' to merge data from an Excel spreadsheet into a SQL Server 2008 R2 database, the principal question is about the data, that format of 'the data' in each cell of the spreadsheet and the 'same results' of data format in the SQL database.

    The first row of the spreadsheet will be defined as 'text data' defining the contend of the remaining rows of data.

    The second through the last row will contain data defined either text data, numeric values and or date fields.

    This second question is about the results data in the database. Will the data defined in the spreadsheet be 'like defined' in the SQL database?

    RBBenson

    Monday, January 2, 2012 9:24 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Check this link it will help in giving you ideas on how to import data from Excel to SQL

    1- to import excel to sql don use Import/Export wizard , check the link you'll understand, ie, if the sheet name is fidderent , your package will have to be edited or if the file name changes and lost lost more,......

    2-maily to import data from Excel sheet you need to use IMEX=1 in your connection string, google IMEX =1

    3-first row is headers i am assuming, right?

    4- the other rows arebasically the data.

    5- Last question = YES , if you use IMEX=1 and you may need to convert some data within the DFT

    ie, lets say that the DATE is in a string format ( March 2nd, 2011) in this case the ETL will pick this up as a text not a date, but you can use a SCRIPT COMPONENT to convert it to DATE format ie 2011-04-02

    use a the advance option on the source excel in the DTF

    good luck

    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Tuesday, January 3, 2012 3:15 AM