Data Import Problem

Question

Post reply

Data Import Problem

David Greene-246981

SSC Journeyman

Points: 85
More actions
March 22, 2007 at 1:01 pm

#172842

I am trying to import an Excel sheet into a database. I have import Excel sheets a 100 times and never had this problem before. I import the Excell sheet and then open the table to verify the data all came in ok. The table includes and employees name in one column and the SSN# in another column.
What's weird is that all of the SSN's that have a leading zero import fine. But all SSN's that have any number other than a zero the field says <NULL>.
What the heck is going on here?
Thanks in advance for your help...
David

Viewing 15 posts - 1 through 14 (of 14 total)

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

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

Have you defined the table explicitly, or are you letting DTS do that for you? If not, you might want to

CREATE TABLE dbo.NameSsn (EmployeeName varchar(50), EmployeeSsn varchar(9))

then in your DTS package tell it NameSsn is the destination table.

There is no "i" in team, but idiot has two.

David Greene-246981 SSC Journeyman Points: 85 More actions · Answer 2

Hi Dave,

I have tried it both ways.

I first created a table and made the Name column nvarchar and then made the SSN column different types like nvarchar, and text.

I also just tried importing it into a new table.

Both ways I go the same results.

Please help!

Thanks,

David

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

With Excel datasources, DTS reads the first N rows of the sheet to sample the data and makes assumptions about the data. Sorry, can't recall what number N is, but it's relatively small and can be tweaked in the registry.

If the first N rows of your data all have leading-zero SSN values, DTS may assume that the column contains only 16 byte integers. Once it hits rows that are further down the sheet, that don't have leading zeroes, it finds numbers that are too large to fit in 16 bytes, and passes them through as Nulls.

You can try modifying your spreadsheet, to place a row containing a larger SSN number as the 1st row of the sheet. This should cause it to allocate 32 byte ints for all the values in that column.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 4

What if you open the spreadsheet, format the SSN field as general, and then try to import it into a varchar(9)?

</long shot>

There is no "i" in team, but idiot has two.

htt SSCertifiable Points: 5242 More actions · Answer 5

Try to export the excel file to a flat file then from the falt file to SQL. It should work.

David Greene-246981 SSC Journeyman Points: 85 More actions · Answer 6

I tried turning it into a flat file (.txt) and when I tried to import it, I got the following error message:

Error Source: Microsoft Data Transformation Services Flat Rowset Provider

Error Description: too many columns found in the current row; non-whitespace characters were found after the last column's data.

Context: Error calling GetNextRows. Your provider does not support all the interfaces/methods required by DTS.

Heeellllpppppp!!!

I've never had this much of a problem importing data.

Thanks,

David

htt SSCertifiable Points: 5242 More actions · Answer 7

Have you googled for this error? Without the data I can't go further.

hth,

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 8

Do you actually have a rectangle in the spreadsheet? There are the same number of columns in every row?

There is no "i" in team, but idiot has two.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 9

Formatting in the spreadsheet does not matter; that is only for display purposes.

PW is correct, the Excel ODBC driver looks at the first 8 rows to guess at each column's type. Anything after that 1st 8 rows that does not fit the guesstimated column type is imported as Null.

This isn't a SQL Server or DTS problem, but the way the Excel ODBC driver works. This is documented in MSKB going back to 1996. Check out MSKB article 194214 PRB: Excel Values Returned as NULL Using DAO OpenRecordset for workarounds.

Hope this helps.

Mark

Sandra Larson Old Hand Points: 307 More actions · Answer 10

Here is another link that may help. I've used this a few times thanks to another member's input:

http://www.sqldts.com/default.aspx?254

Good luck, Sandi

Camilo Torres SSCrazy Points: 2034 More actions · Answer 11

Quick solution?. Create a new column in excel being the concatenation of underscore '_' and SSN column. After import run

update <table>

set <SSN_column_with_underscore> = replace(<SSN_column_with_underscore>,'_','')

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 12

OP, did you get it going? By now, you probably could have typed it into EM.

There is no "i" in team, but idiot has two.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 13

I never use the Excel DTS import function because of the nature described

by Mark and PW. Save the Excel sheet as a flat text file and use DTS or bulk insert. That always works for me. David have open the file in note pad etc. Is the file ok?

/Gosta

Sean May-415583 SSC Enthusiast Points: 144 More actions · Answer 14

<edit>Oops...didn't see Gosta's post. Didn't mean to repeat...

Another possible workaround: Converting your .xls to a .csv. Whether or not that's a good option depends on what you're doing, but you wind wind up using the text driver instead of Excel's.