Importing Excel into SQL Server 2005 with Import Wizard

I have several spreadsheets given to me which are in Excel 2007 file format. So, I've saved them as Excel 2003.

I run the SQL Server Import and Export Wizard in SQL Server 2005 Management Studio from my local PC againist an online 3rd party hosted database.

Initially I was getting errors yet the data was imported, so I wasn't too concerned.

Now, on one file, only 8 records out of 285 records are imported. Here is the latest error messages extract:

Messages
 * Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
 An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unspecified error".
  (SQL Server Import and Export Wizard)
 
 * Error 0xc020901c: Data Flow Task: There was an error with input column "PRODUCTID" (187) on input "Destination Input" (102). The column status returned was: "The value violated the integrity constraints for the column.".
  (SQL Server Import and Export Wizard)
 
 * Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (102)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (102)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
  (SQL Server Import and Export Wizard)

 

My Product ID column in the database is not an numeric (int) data type and is not an auto increment column. So I have to supply a product id.

Any idea(s)?

Also, what do the numbers 187 next to PRODUCTID and 102 next to Destination Input actually mean? Confused

0
Keep
8/6/2008 8:19:28 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

7 Replies
1041 Views

Similar Articles

[PageSpeed] 43
Get it on Google Play
Get it on Apple App Store

 Hi,

It seems your ProductID is referencing to other Table. Please check the Foreign Keys of Product Table.

 

Expand your current Product Table --> Expand Keys. Here you should not see any Foreign Key Reference to the ProductID.


Thanks and Regards,
Venu B Pavuluri
0
venu_babu80
8/6/2008 7:37:48 PM

Thanks for the reply. Yes . How did you know that? What part of the error indicates that?

Yes the Products table is related as a "Child" table to another table e.g TABLE1 and is a "Parent" table to another table e.g. TABLE3 (which is empty i.e. has no data).

TABLE1 (has data) => Products (trying to import data) => TABLE3 (no data yet)

I have checked the foriegn key id data in the spreadsheet; it exists and matches with the parent table (TABLE1) primary id data.

Any idea?

0
Keep
8/7/2008 12:17:42 AM

 John,

If you see your Error Report:

* Error 0xc020901c: Data Flow Task: There was an error with input column "PRODUCTID" (187) on input "Destination Input" (102). The column status returned was: "The value violated the integrity constraints for the column.".
  (SQL Server Import and Export Wizard)
 

Its showing ProductID is violating integrity constraint. As per my knowledge you have to analyze your Datamodel corresponding with Excel Sheet data and Find all Primary Table data and Foreign Table data. First Insert all Primary Tables data into  Primary Tables.

As per your error, you have to save Product IDs into separate Excel Sheet and Run Winzard for Parent Product Table then try whole excel sheet for child table.


Thanks and Regards,
Venu B Pavuluri
0
venu_babu80
8/7/2008 12:39:06 AM

Venu_babu80, it seems you replied just before I edited my reply.

I have checked the spreadshseet and all seems OK. I have populated Parent table first and then proceeded with Child table import.

Still doesn't make sense

0
Keep
8/7/2008 12:44:22 AM

Are you getting Same Error?

You should not get same error if your child ProdIds exist in Parent Product table.


Thanks and Regards,
Venu B Pavuluri
0
venu_babu80
8/7/2008 12:54:30 AM

Now I'm getting a different error - even though I am doing the exact same thing and using the same spreadsheet.

Anyhow, now it's generating an error on a column that is mandatory (that makes sense). When I "Preview" in the Wizard some column data for the mandatory field aren't there BUT the data is there in the source spreadsheet. What could be the cause of this? FYI, the original format in spreadsheet given to me for this mandatory column was Number - so I changed it to General in the source file becuase the data type in database is varchar(10)

As a test, I took the first record that it could not see and saved it as a sepearet spreadsheet with only this one row and imported it fine, no problem! Strange, yet again.

I just found out: Although I saved the column as General in the source file, in the Wizard it's showing DOUBLE(15), (which still doesn't explain why a single record import worked.)

0
Keep
8/7/2008 1:42:58 AM

Keep it Simple:
As a test, I took the first record that it could not see and saved it as a sepearet spreadsheet with only this one row and imported it fine, no problem! Strange, yet again.

What that usually means for me is there is a problem with the record before the one it didn't import. Check to see if all of the columns of the previous record imported correctly.

The only issues I've had with preview data not showing is with csv files where the column delimiters were stuffed up. Usually things like not enough columns were delimited, so it ignored an end of line and tried to put too much data into the column above. Of course this shouldn't be an issue with an excel file I wouldn't have thought.

Anyway, try checking the data on the previous line and see if that sheds any light on the issue.

 

HTH


Steve
0
drktrnq
8/7/2008 2:21:56 AM
Reply:

Similar Artilces:

Import SQL 2005 Express to SQL 2005 Server
Hi, Can anyone give me a "dummies guide" to transfering my DNN 4.0 site from my development machine, running SQL Express 2005 to my host/production server which is running SQL Server 2005? Any other general advice for transferring sites between machines? Thanks, DG ...

How to Import .DBF file to sql server 2005 using Import Export wizard?
PlZ tell me How to Import .DBF file to sql server 2005 using Import Export wizard? Thanxs in advance        http://www.mydatabasesupport.com/forums/sqlserver-server/360729-how-import-dbf-file-sql2005.htmlFred--Thats what my buddies call me Mark this as Answer, if this reply answers your post...

importing an excel spreadsheet into a sql database using sql server 2005 express
hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?    Try this thread for many options.  Hope this helps. http://forums.asp.net/thread/1442470.aspxKind regards,Gift Peddie thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. FrankBollard:thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. I am glad I could help.Kind regards,Gift Peddie...

How to import excel sheet into SQL Database in SQL Server from Remote SQL Client
 Hi all, I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using  SQL Sever Machine.but it doesn't work using SQL Client  machine.I need to do it using  SQL query.it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.Please tel me how I do this.Following are my code.------------------------------------------------------------------------------------------------------------------------------------declare@service_provider varchar(300),@path varchar(300),@dbtype varchar(500),@query varchar(300),@datasource varchar(300)set @service_provider='Microsoft.Jet.OLEDB.4.0'   set @dbtype='Excel 8.0;DATABASE='set @path='C:\Documents and Settings\HARSHA\My Documents\ToExportExcel\Book1.xls;HDR=yes'set @query='Select Name,ID from [Personal$]'set @datasource=@dbtype+@pathEXEC(    '        SELECT Name,ID    FROM    OPENROWSET    (    '''+@service_provider+''',    '''+@datasource+''',    '''+@query+ ''') a') ---------------------------------------------------------------------------...

How to export sql server 2005 express for import on web sql server
Hi,  I'm new to SQL Server 2005 Express and SQL.  I've been using Access for year and have not moved on to asp.net for my website.  I understand from 1and1.com that I will need to imort a backup of my SQL Server 2005 Express file to my on site database. I don't find any way to do that.  Please advise as to how I get the express version to work on my site. Thanks! Leesha Did you install the SQL Management Studio?  In the toolkit... JeffPlease: Don't forget to click "Mark as Answer" on the post that helped you. That way future rea...

Import data from Excel to sql server 2005 not working on server
I am trying to import data from an Excel file into SQL Server 2005 in a webform.  The code below works on my local machine, but on the Production server it throws an unspecified OLEDB error at OleDbConn.Open when attempting to open the connection.  I cannot figure out what is required on the server to make this work, as the error gives no details.  I do not have Excel installed on the webserver and do have it on my local, but don't know if that is the problem, because I get the same error when I change the connectionstring to open a CSV file.   Any help wo...

Import excel data from Microsoft excel to SQL Server usin C sharp .net
Hi Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp Thanks      Here's a simple way to do it without the need to have excel installedhttp://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx Hope this helpsJaime Don't forget to click "Mark as Answer" on the post that helped you. That wa...

import text file to sql table in sql server 2005
hi guys i need  to import text file to sql table in sql server 2005 ...using query how do i import text file to sql table .......................  i need query i dont want go Import/export options Vino I think u can go with OLEDB Provider plz check that Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. ...

how to import data from excel to sql server 2005
hi all,         how to import data from excel to sql server 2005 using asp.net 2.0 and c#    AshokAshok Is this something that you going regularly. Do a data transformation, DTS from sql, into a table. and use it from there SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\1A.xls', 'SELECT * FROM [Sheet1$]')Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever. hi all~i have an other question here..can i apply the same ...

Import SQL Express Tables into SQL Server 2000/2005 possible?
Is it possible to import SQL Express tables into SQL Server 2000 or 2005?  I've seen a few posts in here touching on that subject but no real concise answers. Thanks,Richhttp://www.DevAndDesign.com/...

How to Import data from excel sheet within SQL Client into SQL Server
 Hi All,   I need to  import data from excel sheet to SQL Database in SQL Server.I already done it in machine which SQL Server run.but I cant do it within SQL Client machine.while try to do it this error was occure Server: Msg 7302, Level 16, State 1, Line 2Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.Please tell me How I do itThanks,Harsha Hi harsha  Can you post your code  If this post helps you, please mark it as Answer.Gnana Prakash SSoftware EngineerCoimbatorehttp://www.sgnanaprakash.com  Hi Gnana,this is My...

Importing Data from Excel 2003 to sql server 2005
Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script: EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO Insert into Pamphlet Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Pamphlet.xls;HDR=YES', 'SELECT * FROM [Sheet3$]') But it is giving the following error: Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(nu...

Importing Excel data into SQL Server 2005 with a twist!
Hi, I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table. However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products ...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Importing data from an excel spreadsheet to a table in Sql Server 2005
For the last several hours I have been looking through forums for some vb.net code that allows the user to select an excel spreadsheet, then when they click 'go' the application takes the data in the spreadsheet and copies it to a table. I understand that the data in the spreadsheet & database need to match up, which isn't a problem. Does someone have some vb.net code they could provide to do this? I found some code that seemed to work, except that it skips the first row in the spreadsheet. I'm assuming this is because there is usually a header row, but my spreadsheet won...

How can I import data from Excel to SQL server 2005 Express
I would like to import an Excel file into my SQL Server 2005 Express db. I'm using SQL Server Management Studio Express to manage my db. I appreciate your suggestions,Thank youJackxxx  You need to use SQL server Business Intelligence  Development studio and create SSIS package Using simple Drag and Drop controls  Watch The true story of internet ||Yet another forum |||I'm big fan of Open source flash chart for .net very very simple I n t e r v i e w T i p s Sorry, I do not have these tools to work with.  Thank youJackxxx  Ok, Right click on the Datab...

import excel file into dropdownlist then export to sql server 2005
i am handling a project where user can choose the excel file and the field in the excel file to export into sql server 2005. which mean there will be dropdownlist where the user can choose the field and so on. anyone know how to do this? I'm a bit uncertain how this question is Asp.Net related. Please clarify how it is.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se-------------------------------- which mean i will need to import the excel file into a dataset and then bind the dat...

SQL Attached mdf database files VS database imported into or created in SQL Server 2005
 Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases. AFAIK it is only for deployment conveniance, I have always imported the database directly into SQL Server and set up the connection string accordingly.Don't forget to click "Mark as Answer" on the...

How to import MS Excel data into SQL Server 2005 Express Ed.
I am using SQL Server 2005 Express Edition for testing and developping my website. How can i import MS Excel Data into a SQL Server table? The easy way? Open your excel spreadsheet, hit control-a (Select All), control-c (Copy). Open Management Studio, right click the table you want to "import" to, and select "Open Table".  Then hit control-v (Paste). So long as the table has the same number of columns as your spreadsheet, you're done.   Thans for this solution! :) I can use that! I also want to know what kind of other options can be used....

Importing data from .xls excel file to a SQL Server 2005 Database
Hi guys. I have a question for you. I have a web application and I need to import some data from an .xls (microsoft excel) file to a sql server 2005 table. So, I been searching for it and I found a couple of articles, but I would like to know How can I do this when the .xls file is in a remote web server??. Another thing is that I don't want to download the .xls file to the local machine where the application is running, I mean, I want to do the importing of the data by completely remote access, I want to access the file and run the process without to make a local cop...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks  Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem.  Please click "Mark As Answer" if this hepled in solving your problem. Hi,so meaning, i should upload my .MDF file and place it in App_Data directory?by the way, how do i change my connection string accordingly?do you have any tips?  <add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\YourDatabase.mdf;User Instance=True"/>   ----------------------------------------------- Please click Mark As Answer if this helped in solving your problem.Please click "Mark As Answer" if this hepled in solving your problem. usrnix: Hi, Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet?  thanks  Hi, If you want to use the Sql server 2005 instead of MDF file on t...

SQL server 2005 and SQL server 2005 lite
Hi, I have a web site hosted at a hosting company.. I'm in the process of designing a web site which has some forms that are going to hit a database, etc.. for a small company.. This small company has it's own Web server, and when I'm finished I'll copy the finsihed product to their IIS web server, etc.. Well, I have SQL server 2005 full edition as part of my web hosting, but I'm going to have to use SQL server Lite for the end user / client since they don't have SQL server. So, I'm wondering if I can do my work on MY remote host using the full edition of SQL server 2005, and then copy to th...

Web resources about - Importing Excel into SQL Server 2005 with Import Wizard - asp.net.sql-datasource

Viral Channels: Hi5 Adds Contact Importing to App Invite Flow
... social networking platform I’ve seen, hi5 announced tonight that it is adding the ability for users to invite non-hi5 friends to apps by importing ...

YouTube - iPad importing photos from floppy disk.
You are using an outdated browser, which YouTube no longer supports. Since some features on YouTube may not work, you are viewing a lightweight ...

Tony Abbott considers importing US-style school program with private industry involvement
Tony Abbott is considering an unprecedented Americanisation of the school education system with radical changes that could see HECS-style fees ...

Importing fuel will prove better for the consumer
Importing fuel will prove better for the consumer

Qantas airline steward faces jail for importing drugs
A former Qantas airline steward who worked business class flights to America for several months with a ''raging'' ice habit faces jail for importing ...

Importing more fads from failed systems
Fast-tracking talented professionals into the classroom is yet another imported fad from England and the US - both of which are failed education ...

Urine test fail for man facing drug importing sentence
A Canberra man who pleaded guilty to importing a commercial quantity of drugs to his front door has admitted&nbsp;taking a banned drug&nbsp;weeks ...

Hong Kong man charged with importing 150kg of crystal methamphetamine into Sydney
A Hong Kong national is charged with importing 150 kilograms of ice following a joint-agency investigation in Sydney.

Man, 91, charged with importing cocaine hidden in soap boxes into Australia
A 91-year-old Sydney man has been charged with importing cocaine hidden in soap into Australia, prompting police to warn travellers to beware ...

Google Drive adds support for importing OpenOffice (ODF) file formats
Google announced today that it’s now officially supporting ODF files in Google Drive allowing users to import all three major ODF file formats. ...

Resources last updated: 3/11/2016 5:53:17 PM