Values shown as NULL - SQL Server Forums

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

Values shown as NULL

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-11-04 : 05:24:13

Hallo,

I am importing data to a table in sql server using the import wizard. The table is in Excel spreadsheet and a sample is shown below;

Name Account No

Riyadh 82643837
Jim 03536343
John 78364363
James 06456454
Julie 07464643

After the import, the table is shown as;

Name Account No

Riyadh 82643837
Jim NULL
John 78364363
James NULL
Julie NULL

Notice, in the Account No field any data starting with "0" is set to NULL though it is not. The field data type is set to varchar(50).

In Excel the format cell for Account No is set to 'Text'. It worked fine with sort codes, though...

Any ideas??

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-04 : 08:30:42

i get around this by saving the excel sheet as a CSV, then i import the CSV.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-11-04 : 09:39:21

When I try to save as .csv, the '0's disappear. For example 03536343 becomes 3536343.

Any ideas /work around?

thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-06 : 10:29:30

in excel, change the type of column to TEXT, then save as csv.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources