Excel columns to a temp table in SQL

Hot Topic

Unable to complete login process due to delay in login response
Can't connect to SQL Server 2005
64-bit server deployment from 32-bit workstations
How to create an SSIS package correctly by programming?
Parse error
How can I remove !new from the end of my report names?
replication monitor error
Conversation ID cannot be associated with an active conversation
SqlExpress create unique column
How to connect SQL Server Mangement Console to Adventure Works

SQL Server

SCD and populating dimensions and facts
SQL Server 2000 DTS Tools Web Download
access denied to RSReportServer.config
SQL 2005 woes
SQL Locking up
Help Designing an App. to be Run from a Job
ODBC Data Source
SQL memo datatype (SECOND TIME)
Iterative Calculations Analysis Server 2000
SQL Deployment / Production Package Versioning

Excel columns to a temp table in SQL

I would like to transfer all the cells of an excel sheet to a temp table
in such a way that there is no data loss and no rows of columns of data
are made to Null or omitted.
I am keeping the datatypes of columns in the temp table as Varchar(255).


Thanks
Clayton.

Answer this question

Excel columns to a temp table in SQL

  • edison_torres

    Use the Import Export wizard to do this for you. It will create an SSIS package that you can re-use and adapt to meet your needs. If it needs amending then please reply here asking for help and please try and be more specific.

    Regards
    -Jamie

  • McWhirter

    I mean to say that when i transform a excel thru an excel source to a SQL destination database the problem is that the columns which are teh minority datatype in any row which contains a majority of integer numbers get passed
    as NULL.how do i prevent this

    eg: suppose in a column named Year i have and i enter 2000,20004,2005,2006,abc,2003,2002

    then this column with abc goes as blank i want to prevent this and i want abc to go to the SQL Destination DB.

    Thanks
    Clayton

  • Hugh Wood

    This is the oldest Excel driver problem in the book, and is documented in the BOL topic on the Excel Source (which is currently being expanded with still more "known issues" information).

    If you would like to write to me directly, douglasl@microsoft.com, I will send you a Word document that discusses at greater length all the potential known issues with Excel in SSIS.

    Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data types of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values in fields that contain data of the other type. Most cell formatting selections in the Excel worksheet do not affect this data type determination. As one possible solution, you can use an OLE DB Connection Manager instead of the Excel Connection Manager, and modify this behavior of the Excel ISAM driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties on the All page of the Connection Manager dialog box. Use a semicolon to separate this name-value pair from the Excel version specifier. For more information, see PRB: Excel Values Returned as NULL Using DAO OpenRecordset.

    -Doug

  • Sehar Iqbal

    I have just reproduced this error using the import/export wizard.

    The wizard produces a SSIS package.
    1. Open it up.
    2. Open up the data-flow
    3. Right-click on the source adapter and select "Show Advanced Editor..."
    4. Click on the "Input and Output Properties" tab
    5. Expand "Excel Source output"

    You will see that the datatypes of the columns are defined in here. Under "Output Columns" change the datatypes to whatever you require (e.g. DT_STR with a langth of 255)

    Hope this helps

    -Jamie


  • Excel columns to a temp table in SQL
Answer this question