• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

Move Data from excel spreasheet to sql server 2008 table

I am trying to move data from an excel spreadsheet to a table in sql.
I have used the import export wizard to create an SSIS package.

When I run the package I get :

"Cannot insert the value NULL into column 'DisciplineMappingID', table 'ECARS.dbo.DISCIPLINE_MAPPING'; column does not allow nulls. INSERT fails.".
 (SQL Server Import and Export Wizard)

The column 'DisciplineMappingID', is the primary key of the table, but it is not an identity column. It is not nullable. Has a type of int.

This table was created by an outside consultation firm as a part of a new system, and I have to accept the table as it was designed, I cannot change it.



There is one column in the excel spreadsheet that has a column, that
Needs to be split into two different fields in the table on sql.

Not sure how I can use the import export wizard to do this.

Thanks in Advance&
1 Solution
The following would be an option to avoid complications using IMPORT/EXPORT wizard. For the column DisciplineMappingID. The quickest way will be too find max value of  DisciplineMappingID in database and populate the excel sheet column with values increasing from this value; this assumes that the value that is inserted is not generated via application code or uses a table in the databases to assign new DisciplineMappingID values to this table. I would probable perform the split on the data once it is in the database using SQL if the column size permits that it is going in before split.
I would alterntaively upload excel to a temp table in database excluding DisciplineMappingID  column. Add new column DisciplineMappingID to temp table ; add identify column setting the seed as max value in target table + 1. This will set value sequence; this assumes that the value that is inserted is not generated via application code or uses a table in the databases to assign new DisciplineMappingID values.      
Attache an excel sample and the create table script in SQL to better help you!

misdevelopersAuthor Commented:
Someone here where I work showed me a way to do.

using Excel

"      Insert a new column as the first column.
"      Place sequential numbers in the first column 1, 2,..

"      Add two new columns, on the right.
One column for the account number and the other for the cost center.
Put the account number from column I in the first new column,
Put the cost center from column I in the second new column.
This can be done by putting equations in the new columns referencing the I column.

"      Delete the I column.

Copy the contents of the excel spreadsheet, and paste into a new word document.

In the new word document, convert the table to text.
Using the ^ character as the delimiter.

Save the document as a text file. Name it :

Run the following script in the appropriate database:
set ansi_warnings off

FROM 'C:\abc.txt'

         FIELDTERMINATOR = '^',
         ROWTERMINATOR = '\n',
         FIRSTROW = 2,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now