Import spreadsheet with text and numbers

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Import spreadsheet with text and numbers

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-07-27 : 07:32:24

Hi,
I am trying to import a excel spreadsheet 2000 into SQL 2000, but it inserts a NULL rather than the number in one row.
the data in the spreadsheet looks like :
F
F
N
5
4
K

when imported in SQL it looks like :
F
F
N
<NULL>
<NULL>
K

why is this ?
The format of the spreasheet is general.....

regards,
JAmie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 07:39:09

What is the datatype in SQL Server table?
As an alternate, try this also
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-07-27 : 08:31:21

nvarchar isthe data type.
i am using the import wizard.
If I change the datatype it is still null for numbers !

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-07-27 : 08:36:12

doing :
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\script.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

also creates NULLS ///

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 09:09:42

When entering numbers in the Excel cell, enter '4 (add single quote before the number)
and then try it

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-07-27 : 09:45:29

cool, that has seemed to work. but its a bit pf a pain editting the spreadsheet.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 09:57:32

Anyway you need to do this to have proper data

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-27 : 10:18:27

If the apostrophe prefix works, maybe physically formatting the column to TEXT (in Excel) will do too?

It continues to amaze me what a PITA importing into SQL from Excel is - and its such an obvious, no-brain, route ....

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-27 : 10:29:14

>>If the apostrophe prefix works, maybe physically formatting the column to TEXT (in Excel) will do too?

No it doesnot seem to work

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-07-27 : 11:09:13

It continues to amaze me what a PITA importing into SQL from Excel is - and its such an obvious, no-brain, route ....

and exporting from SQL to Excel !

Do you know if these problems have been ironed out in SQL 2003 ?

Kristen
Test

22859 Posts

Posted - 2005-07-27 : 14:07:47

"SQL 2003"

hehehe .. some sort of Service Pack?

Kristen

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-07-28 : 07:31:09

ha ha.
you know what I mean ! :o)

2005 ...

(my keyboard is microsoft so not compatible with my fingers!) :o)

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 08:18:25

"you know what I mean"

Yup, naturally! but I'm afraid I don't know the answer w/r to SQL 2005

Kristen

xiard
Starting Member

1 Post

Posted - 2006-11-16 : 10:05:02

I came across another useful piece of information while researching this issue, and I thought I would post it to help anyone else out who comes across this thread.

Yes, using this SQL statement creates NULLs in the data as well:

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\script.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

However, if you add IMEX=1 to the second parameter to OPENROWSET, SQL server will treat the numbers as text in the way you intended, like so:

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\script.xls;HDR=YES;IMEX=1',
'SELECT * FROM [Sheet1$]')

Here's the link where I found this information, which explains how to set the IMEX attribute in the properties for a DTS package. Fortunately, the same attribute works in this script as well.

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

David

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 10:14:09

Thanks xiard for that link

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources