"SET SCIENTIFIC NOTATION OFF"

Question

"SET SCIENTIFIC NOTATION OFF"

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
April 15, 2011 at 7:03 am

#389292

Hi there,
I am having serious troubles importing Excel data in to MS SQL 2008. Excel messes up data and the geniuses in that department have decided not make an on/off switch. One can not prevent Excel from changing data it is supposed to leave untouched. One result is that leading zeroes are stripped off from telephone numbers. Because multiple customers have multiple Excel-layouts, I cannot say, for example, "column 1 must be regarded as text". For each xls that is different.
When I import it data (with OPENROWSET) I get the omitted leading zeroes.
I want to try to import into a predefined temporary table with varchar columns (hoping that Excel stops messing around with MY data), but now it is imported in a scientific notation.
Question: is there a "SET SCIENTIFIC NOTATION OFF" thingie in SQL?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

I do not think this is related to SQL Server. I suspect it is the Excel driver that is inferring your data types and causing your data to be munged.

The SELECT statement below read data from a worksheet where column A was formatted as Text and contained numbers with leading zeroes and column B was formatted as Scientific and contained long decimals like 1.00004909090909 which displayed in Excel as 1.000E+00.

The data was imported into SQL Server untouched, i.e. column A was selected as 001234 and column B as 1.00004909090909.

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=c:\@\Book1.xls;',

'SELECT * FROM [Sheet1$]');

If you're still having issues finding a solution please provide your query using OPENROWSET so we can see the connection string, and attach a sample Excel file that goes with your query so we can recreate the issue.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 2

opc.three (4/15/2011)
I do not think this is related to SQL Server. I suspect it is the Excel driver that is inferring your data types and causing your data to be munged.
The SELECT statement below read data from a worksheet where column A was formatted as Text and contained numbers with leading zeroes and column B was formatted as Scientific and contained long decimals like 1.00004909090909 which displayed in Excel as 1.000E+00.
The data was imported into SQL Server untouched, i.e. column A was selected as 001234 and column B as 1.00004909090909.
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\@\Book1.xls;',
'SELECT * FROM [Sheet1$]');
If you're still having issues finding a solution please provide your query using OPENROWSET so we can see the connection string, and attach a sample Excel file that goes with your query so we can recreate the issue.

Rumor has it that the JET drivers aren't availablle for 64 bit SQL Server. Has anyone heard otherwise 'cause I'd love to use them if they did exist.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

I haven't tried them myself but a 64-bit Jet<edit>-alternative</edit> driver was released: http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Dup post...

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 5

opc.three (4/15/2011)

I haven't tried them myself but a 64-bit Jet<edit>-alternative</edit> driver was released: http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en%5B/quote%5D

Thanks for the link. I'll take a look.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

I just got around to trying the new Office 2010 drivers, albeit the 32-bit ones. I don't have a 64-bit sandbox available to me at the moment.

I was able to select data from an Excel 2007 file using the updated OLE DB provider and OPENROWSET:

SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\@\Sampleformslist.xlsx;',

'SELECT * FROM [Sheet1$]') ;

I was also able to select data from an Excel 2010 file:

SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\@\00-ChartingIntroduction.xlsm;',

'SELECT * FROM [Sparklines$]');

I did pickup that the Microsoft download article I linked to is incorrect, there is no "installable ISAM" for Excel 14.0 so use Excel 12.0. http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/282a05c2-2c1c-431f-8276-75055befeb76

Many reports I have seen have been positive re: the ACE provider. Have you had a chance to try them on 64-bit?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 7

opc.three (4/20/2011)
Many reports I have seen have been positive re: the ACE provider. Have you had a chance to try them on 64-bit?

Not yet. I hoping to do some experiments with 64 bit ACE over this upcoming weekend. Thanks for the feedback on the 32 bit stuff. I'm glad they didn't mess it up in the process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125560 More actions · Answer 8

The issue with truncation of leading zeros is due to the Excel column being formatted as numeric. When using Excel to exchange data with SQL Server, it's best to format all columns as Text, so values like "001", "+200", "1430000250095.1256", and "2011-04-21 07:48:52.127" are contained as literal strings without being typed as floating numeric or date. For data exchange, Tab Delimited Text is better, because there is no formatting.

If these Excel sheets are something like a data entry workbook maintained by end users, then there is no hope of pulling data out of it with any reliability, because they will constantly be doing stuff like tinkering with data types, adding new columns, and changing header names.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 9

Not yet. I hoping to do some experiments with 64 bit ACE over this upcoming weekend. Thanks for the feedback on the 32 bit stuff. I'm glad they didn't mess it up in the process.
--Jeff Moden

We are currently using 64 bit ACE, and that works well. It replaced our own built workaround with a VB6 exe.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 10

r.vanlaake-1086273 (4/22/2011)
Not yet. I hoping to do some experiments with 64 bit ACE over this upcoming weekend. Thanks for the feedback on the 32 bit stuff. I'm glad they didn't mess it up in the process.
--Jeff Moden
We are currently using 64 bit ACE, and that works well. It replaced our own built workaround with a VB6 exe.

That's encouraging. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)