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.

-----------------------------------------------------------------------

Also,

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&
misdevelopersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tpi007Commented:
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.      
 
0
PedroCGDCommented:
Attache an excel sample and the create table script in SQL to better help you!

Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net
0
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 :
abc.txt

Run the following script in the appropriate database:
 
set ansi_warnings off
BULK INSERT DISCIPLINE_MAPPING

FROM 'C:\abc.txt'

   WITH
      (
         FIELDTERMINATOR = '^',
         ROWTERMINATOR = '\n',
         FIRSTROW = 2,
         CODEPAGE='RAW',
         KEEPNULLS
      )



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.