Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Import excel file to sql server table Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2008 8:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:16 PM
Points: 237, Visits: 764
i want to append rows from excel file to sql server table

Table has a identity column.

How do i achieve it thru SSIS or thru DTSWizard? I get error if i the identify column with null value in excel sheet.
Post #446498
Posted Wednesday, January 23, 2008 11:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:43 AM
Points: 1,595, Visits: 6,603
Check the 'Keep Identity' checkbox off in the Destination Editor (it's the FastLoadKeepIdentity property in the Properties box, which must be false).

Peter
Post #446764
Posted Thursday, January 24, 2008 2:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, March 21, 2014 6:50 AM
Points: 451, Visits: 513
keywestfl9 (1/23/2008)
i want to append rows from excel file to sql server table

Table has a identity column.

How do i achieve it thru SSIS or thru DTSWizard? I get error if i the identify column with null value in excel sheet.


Identity column has null value? Impossible.
Post #446788
Posted Thursday, January 24, 2008 2:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 2:42 AM
Points: 739, Visits: 75
remove your identity column output before the import operation
Post #446791
Posted Thursday, January 24, 2008 8:51 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:58 AM
Points: 97, Visits: 355
Hi,

I do this all day. I don't know what's your knowledge level on SSIS, so I would assume it's basic.

use data flow task in SSIS. Under "Data Flow" tab, drag Excel source and OLE DB destination. Make connection to your excel file and database in Connection Manager. Make sure you are using excel 2003 sheet, It doens't recognize Excel 2007. In Data Flow tab, double click on excel source, slect appropriate connection, and then sheet. Click on Columns and check the columns you want to transfer. Take the green arrow and attach it the database destination task. Double click database destination task. Here is what's important, make sure you check "keep null values." Select correct database connection and the table. Its better to create the table here, becuase it creates the table by taking column names in your excel file. Also, make sure your column with null values isn't set as primary key. Then, execute the ask and you should be golden.

Ravi.


------------
:)
Post #446978
Posted Friday, January 25, 2008 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
Importing data into a live table isn't generally good practice unless it's a repetitive process and problems have been sorted on previous imports.
Instead, import into a staging table, which should be a full or partial copy (columnwise) of the target table with no constraints. Check the new data once it's in the staging table, then import into the live table. Then purge or delete the staging table. Much safer, much easier to check.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #447368
Posted Tuesday, April 29, 2008 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2008 10:24 PM
Points: 2, Visits: 4
hi ;
i m working on vista operating system and i m connected with a remote server(sql server 2000) and i m also connected with sql sever 2005 on my own system.
i m able to import excel sheet in sql server 2005 by 'openrowset' but
i m unable to import a excel sheet on remote server (sql server 2000)
from my system....
pls help me .....
Post #491990
Posted Friday, May 2, 2008 11:38 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:58 AM
Points: 97, Visits: 355
What DB engine are you using to connect to SQL 2000?♠

------------
:)
Post #494448
Posted Friday, May 2, 2008 10:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2008 10:24 PM
Points: 2, Visits: 4
actually i use sql server 2005 and i hv connected sql server 2000 which is on server..
and when i import an excel sheet which is on my own desktop to remote server by openrowset method then i face problem.......
pls tell me....
Post #494611
Posted Monday, July 21, 2008 1:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 10:45 AM
Points: 1, Visits: 19
Is it possible to SQLBULKCOPY
Post #537460
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse