Importing excel data with preceding '0's into SQL Server

Question

Importing excel data with preceding '0's into SQL Server

supratik.de

Grasshopper

Points: 13
More actions
September 20, 2010 at 1:27 am

#387824

Hello,
I am trying to import an excel file in SQL Server 2005 through "OPENROWSET" command.But the problem is in excel there is a column with cell values preceding with '0's.e.g. '000987','000008' etc..the excel file has rows more than a lakhs so manually edit each row is absurd. but while I'm trying to import the data its getting values without the preceding '0's....So please suggest me how do I import the data through SQL Server scripting...

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

is the length of the fields with preceding zeros all the same? for example are the 6 characters? '000008'?

you can change the column to a varchar, and then update them to have preceding zeros, since you've already been able to import the sheet;

UPDATE SOMETABLE SET SOMECOLUMN = RIGHT('000000' + SOMECOLUMN ,6)

or create a calculated column to leave the value an integer, easily index, and pull the other column when you need preceding zeros:

ALTER TABLE SOMECOLUMN ADD PRECZEROS AS RIGHT('000000' + CONVERT(VARCHAR,SOMECOLUMN) ,6)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I like your second solution, Lowell. If these values are all integers, why bother storing them as char/varchar?

I believe they are called computed columns in BOL.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Ed B Hall of Fame Points: 3669 More actions · Answer 3

This is a common problem with medical codes, 008.8 is not the same as 08.80.

I have always had to save as a text file, or open excel and add a string to every column in the first row.

I have read something about changing the column type to text stream in a connection.