import data from an excel to sql server

Question

import data from an excel to sql server

gxdatareal

Ten Centuries

Points: 1022
More actions
February 12, 2016 at 3:40 am

#308454

hi
i am import an Excel to a SQL Server ( using DB>TASKS>IMPORT DTA ...) and so on
i am creating an NEW table called 'aaa' from the Excel
the problem is that the data in one column in the original Excel has the following type
3461196008
3461196017
0100035.2
0100036.2
tha datatype of the specific column (table aaa) is float
the problem is when i am trying to see the data in SQL Server ( select * from aaa)
where tha data have the "0100035.2" is all of them null
i tried to change the datatype but the problem remaining , i cannot see the "0100035.2"
i will appreciate any help
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

I would suggest you don't load the data into a float datatype unless you're sure that's what you want. Best to use a high precision numeric.

In the import wizard, I think you can intervene on the sql which is used for the create table. Otherwise tweak your table manually and then point to it during the import.

make sure your datatype makes sense. It looks like numeric data, but what is with the leading zeroes? Also if you're searching for numerical data, then don't put it in quotes.

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 2

You may have to format the Excel worksheet before loading it. If the column in question is formatted as General, you'll need to change the entire column's data type to get it to load properly.

Because ... Excel .... SUCKS ... that way. :pinch:

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

If you want to preserve the presence of leading zeros, don't import the data as any form of numeric. Import it as some form of string.

--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: 182276 More actions · Answer 4

Brandie Tarvin (2/12/2016)
Because ... Excel .... SUCKS ... that way. :pinch:

I would say it as "because integrating with Excel sucks that way." It is not technically Excel that sucks, it is the driver, now ACE. The JET drivers also sucked in this way. Excel is just a (mostly) innocent data source. The driver is the thing that scans the number or rows before making a semi-educated guess at tbe data types per the driver's TypeGuessRows setting.

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

georgheretis (2/12/2016)
hi
i am import an Excel to a SQL Server ( using DB>TASKS>IMPORT DTA ...) and so on
i am creating an NEW table called 'aaa' from the Excel
the problem is that the data in one column in the original Excel has the following type
3461196008
3461196017
0100035.2
0100036.2
tha datatype of the specific column (table aaa) is float
the problem is when i am trying to see the data in SQL Server ( select * from aaa)
where tha data have the "0100035.2" is all of them null
i tried to change the datatype but the problem remaining , i cannot see the "0100035.2"
i will appreciate any help
thanks

Try adding IMEX=1 to the extended properties of your Excel connection string and redefine your initial staging table where all columns character types (N/VARCHAR).

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

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 6

Orlando Colamatteo (2/13/2016)
Brandie Tarvin (2/12/2016)
Because ... Excel .... SUCKS ... that way. :pinch:
I would say it as "because integrating with Excel sucks that way." It is not technically Excel that sucks, it is the driver, now ACE. The JET drivers also sucked in this way. Excel is just a (mostly) innocent data source. The driver is the thing that scans the number or rows before making a semi-educated guess at tbe data types per the driver's TypeGuessRows setting.

Point taken.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.