Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 19th, 2007, 04:57 AM
Authorized User
 
Join Date: Oct 2003
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem with importing excel to db -ssis

Hi,

I am struggling with SSIS and will appreciate any help offered.
Can someone please help me?
Thanks

I have an excel document that contains a heading and subheadings with values such as

HEADING LTD.

AC AT ->(SUBHEADINGS)
XX E12
UU GG01

I need to insert the values into a table in sql server. The table contains the columns AC and AT also. No nulls are allowed in the table.
How can I ignore the HEADING LTD. when importing to sql?
Also, if i have 12 rows in my excel the ssis package will try to insert more than 12 rows and i get the error:
[OLE DB Destination [901]] Error: There was an error with input column "A#C" (1161) on input "OLE DB Destination Input" (914). The column status returned was: "The value violated the integrity constraints for the column.".

 
Old December 1st, 2007, 10:32 AM
Registered User
 
Join Date: Nov 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Don't know if this is going to be of any help,but if you use the import/export wizard in SQL server for that database, from the drop down menu on the first screen for the datasource ,choose Microsoft Excel, choose your excel spreadsheet, there is an tick box which should be checked by default 'First Row has column names'. If ticked then the import wizard will ignore the first row which contains your column headings and not use these as data values but use them as the column headings for your table . If you are importing a CSV file, choose 'Flat File Source' as your datasource and tick the tickbox 'Column names in first data row' and put double quotes in the 'Text qualifier' field. You may have to remove the double double quotes from the file via notepad before importing. e.g. replacing """ with " to signify an empty value in the column or surrounding the data in the field i.e """ <value>""" with "<value>".

Hope this helps

 
Old December 1st, 2007, 07:07 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Is the "AC" in your example ALWAYS in the same cell on the spreadsheets?

--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem , importing thousand records from Excel azizur123 ASP.NET 2.0 Professional 2 November 12th, 2008 04:07 AM
Problem importing XML data to Excel bluerattle XML 2 July 3rd, 2007 03:35 PM
Problem importing XML data to Excel bluerattle Excel VBA 0 July 3rd, 2007 03:34 PM
problem in importing data from excel to sql hiren1977 General .NET 0 January 2nd, 2007 06:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.