Import Excel Spreadsheet to SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import Excel Spreadsheet to SQL

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-05-27 : 09:52:46

Hi,

I am importing a table called tblA in sql. The problem is I have a field in the excel spreadsheet with precedding zeros e.,

003
056
047

When I import the spreadsheet to Sql database I receive;

3
56
47

The preceding zeros are not there. I have tried to set the field datatype to varchar, but still it ignores the zeros.

The field in the spreadsheet is format custom - 000.

Any help please Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 10:22:25

The driver doesn't know what that custom format means. It is an integer format with leading zeros. You can change the format in Excel to text, convert the number to string during import, create a custom type in sql or just update the data after you've imported it.

Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-05-27 : 10:23:34

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 10:45:44

You're welcome.

UPDATE yourTable
SET yourColumn = RIGHT ('000'+yourColumnn,3)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources