importing from excel

Question

importing from excel

keywestfl9

SSCertifiable

Points: 5215
More actions
January 30, 2009 at 12:16 pm

#130255

i am trying to do data transformation from excel to sql table.
it goes thru fine and stops at 37229.
table desc:
idnumber(bigint,notnull) -- identity colum
rest of the table allows null
at 37230 record it says null can't be inserted in the idnumber column
anybody knows why? please let me know

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Look at the column mappings in your Data Transfer Task, are you mapping a source column from the spreadsheet to this column in the table?

If so then the data in the spreadsheet is null.

IF So I would suggest you do not map a column from your spreadsheet to this column in your table

kbatta SSCrazy Points: 2274 More actions · Answer 2

Sometimes Excel will have blank rows after the rows you can see data in. You will need to check for that.

Panks-913490 SSCrazy Points: 2131 More actions · Answer 3

kbatta (1/30/2009)
Sometimes Excel will have blank rows after the rows you can see data in. You will need to check for that.

That is the problem i faced too once.

You will have to check if your excel contain 'ghost' rows and delete them.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Since data imported from Excel almost always requires tweaking, it makes sense to import it into a staging table where it can be manipulated in isolation from the rest of your data. It won't then mattter if the datatype is incorrect, there are ghost rows etc. You can correct it in the staging table or in the load to the db - or both.

^{“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

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

My advice is to save the Excelfile as a TAB sep textfile and import that file to a staging table

to clean the data (if needed)

/Gosta

John Dempsey Hall of Fame Points: 3225 More actions · Answer 6

You could also use the Conditional Split object in the Data Flow. You would check for null on the column that can not be null and place those down a dummy ADO recordset path if you want to see them and pass the good records to your data insert portion of the data flow.