Best way to Import data from EXCEL using a TSQL

[Ian]

Hi All

I have a excel spread sheet that i need data from. This is the Data.

MyID
TextField
DateField
IntField
FloatField

1
Ian One
01/04/04
10
10.0304455900

2
Ian Two
02/04/04
20
20.0304455900

3
Ian Three
03/04/04
30
30.0304455900

4
Ian Four
04/04/04
40
40.0304455900

5
Ian Five
05/04/04
50
50.0304455900

6
Ian Six
06/04/04
60
60.0304455900

I have a table call tbl_Import which is built based on the field names
above the data.
MyID - Int
TextField - Varchar
DateField - DateTime
IntField - Int
FloatField - Float.

I have tried 2 different methods for getting this data in to the table
correctly.

First

Declare @ExcelSource as Varchar(255)
SET @ExcelSource ='\\Server\RPT\TestImport.xls'

Insert Into tbl_Import
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="\\Server\RPT\TestImport.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...Import_Data

Select * from tbl_Import

Delete from tbl_Import

This method gives me the following error message.

"Error converting data type nvarchar to float."

So i then change the data type in the table to VarChar on the Fload field

And it does the import but the data columns are all out of order in the
tables like it just guest them.

SECOND

BULK INSERT tbl_Import
FROM '\\Server\RPT\TestImport.xls'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
DATAFILETYPE = 'char'
)

Select * from tbl_Import

Delete from tbl_Import

And i get this message.

"Bulk insert data conversion error (type mismatch) for row 1, column 1
(MyID)."

Is there a better easer why of doing this that acktualy works.

I will be need ing to include it into a Stored Procedure once i have got it
importing the data corectly.

Ian

[Louis Davidson]

If it is a one time import, then just use dts ( I am assuming that your data
is formatted:
myId textField dateField etc

And not each value in a different row. This will be very hard to do using
SQL, since once you import telling which groups with which will be a
nightmare. )

Just right click on the database in enterprise manager and choose import.
It is pretty simple from there

--
----------------------------------------------------------------------------
Louis Davidson (dr...@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Ian" <i...@NoWhere.com> wrote in message
news:%23kDlrwj...@TK2MSFTNGP11.phx.gbl...

[Steve Kass]

Ian,

You can import Excel data conveniently with OpenRowSet. Here is a
script that should be close to what you need. You may need to modify
some registry values so that the mixed-type column will be imported as
text. See
http://groups.google.com/groups?q=29C76785-22D9-46A2-A398-39393E666E76
for relevant threads on this.

set nocount on
go

-- modify registry entries
Set
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
to 20
Set
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes
to 'Text'

-- specify IMEX=1 in the connection string of OpenRowSet
-- don't ask what this does - there's virtually no documentation of it :(

create table Staging (
excelRow int identity(1,1) primary key,
s varchar(100)
)

insert into Staging
select F1 from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=NO;IMEX=1'
,Sheet1$
)
go

create table Data (
pk int not null primary key,
tx varchar(20), -- increase from 80 as needed
dt datetime,
n integer,
f float
)

-- if there are blank rows higher up than
-- the row before #1, change this appropriately
declare @start int
set @start = (
select min(excelRow)
from Staging
where s is null
)
declare @blocksize int
set @blocksize = (
select min(excelRow) - @start
from Staging
where s is null and excelRow > @start
)
set @start = @start + 1

insert into Data
select
(select s from Staging where excelRow = A.Block),
(select s -- set datetime mdy or dmy previously if needed
from Staging where excelRow = A.Block+1),
(select s from Staging where excelRow = A.Block+2),
(select s from Staging where excelRow = A.Block+3),
(select s from Staging where excelRow = A.Block+4)
from (
select distinct excelRow as Block
from Staging
where (excelRow - @start) % @blocksize = 0
and excelRow >= @start
) A
go

select * from Data

go

SK

drop table Staging, Data

[Ian]

Hi Steve

Thanks for your time.

I have read the code you sent.
But I am not sure that it will cater for the fact that the first row in the
Spread sheet has the field names.

The spread sheet in fact is used by people and a VB application before it is
imported by my Stored Procedure and they need to know the column names.
A B C D
E
1 MyID TextField DateField IntField
FloatField
2 1 Ian One 01/04/04 10
10.0304455900
3 2 Ian Two 02/04/04 20
20.0304455900

Sorry but the lay out of the sample data got a little screwed up when I sent
it. I hope this time it looks more accurate.
Above is what it should have looked like.

What you have got here is really well thought up and actually might have
solved another of my problems.
If I am reading the code right then I think what it is doing is thinking all
the data and column names are in the first column. so that is where you get
your single column from in the staging table. Then you select the 5 rows in
the staging table to make up each record.

Why is it that the registry has to be changed.
I ask because this is for a work server.
If it is the only why to import data from Excel as it's correct data type
then I will do it.

Ian

"Steve Kass" <sk...@drew.edu> wrote in message
news:eG%23FAAke...@TK2MSFTNGP10.phx.gbl...

[Ian]

Still the lay out is not correct.

The Data is in rows just like it would be in a table with the first row as
the field names.

Ian

"Ian" <i...@NoWhere.com> wrote in message

news:%235F6xok...@TK2MSFTNGP12.phx.gbl...

[Steve Kass]

Ian,

It's much easier if this is in the form of a table. This should
select the information:

select MyID, TextField, DateField, IntField, FloatField
from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=YES'
,Sheet1$
)

if you use the appropriate file name. There should be no reason to
modify the registry in this case, but sometimes you will get additional
blank rows imported below the data and you can make the change if need
be. The reason for the changes, if needed, are because the Excel data
provider has various options that are controlled by the registry entries.

You can still rely on a staging table into which you import text if
needed, and you may need to be careful with the dates, because the
format aa/bb/cc is ambiguous.

SK

[Ian]

Hi Steve

when i execut

select MyID,TextField,DateField,IntField,FloatField
from OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\Server1\RPT\TestImport.xls;HDR=NO'
,Import_Data
)

I get this as an out put.

Server: Msg 207, Level 16, State 3, Line 34
Invalid column name 'MyID'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'TextField'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'DateField'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'IntField'.
Server: Msg 207, Level 16, State 1, Line 34
Invalid column name 'FloatField'.

BUT

When i do

select MyID,TextField,DateField,IntField,FloatField
from OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=\\Server1\RPT\TestImport.xls;HDR=YES;IMEX=1'
,Import_Data
)

Then it sort of works.

It does select the data but some of it is not exact.

Excel
30.030445111

Selected it is
30.030445110999999

And all the Integers
1

becomes.
1.0

Is there some thing else i need to set.

Ian

"Steve Kass" <sk...@drew.edu> wrote in message

news:uWvJ1xk...@TK2MSFTNGP11.phx.gbl...

[Steve Kass]

That's because you typed HDR=NO where I suggested HDR=YES. HDR means
"header row", and you have a header row. You can also look at select *
to see what the columns are, if there's still a problem, and for the
record, the columns are automatically named F1, F2, F3, ... when you say
HDR=NO.

[Ian]

Hi Steve

That is fantastic thank you for explaning that.

This is what i have done.

Insert Into tbl_Import
select MyID,TextField,DateField,IntField,FloatField,TextFloatField,
TextFloatFieldTwo

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

'Excel 5.0;Database=\\Server\RPT\TestImport.xls;HDR=YES;IMEX=1'
,Import_Data
)

Select * from tbl_Import

Delete from tbl_Import

It does work. I have attached a small TXT file with the input and output
values whe using Decimales.

In Excel the Value is 20.03 after import the float is 20.030000000000001

As the out put showes i may have to format the excel worsheet to Text and
the import the data because i need it to be exact. i cannot have it adding
fractions to my input.

Again. Thank you for you

Ian

"Steve Kass" <sk...@drew.edu> wrote in message

news:O%23q8QKl...@TK2MSFTNGP11.phx.gbl...

[Steve Kass]

Ian,

If you need to represent the value 20.03 exactly, then [float] is the
wrong data type to use. The numbers [float] can represent exactly are a
specific set of binary fractions, not decimal fractions. Because 20.03
cannot be written exactly in the form <integer>/<power of 2>, [float]
cannot store it exactly. SQL Server provides types to represent decimal
fractions exactly: use DECIMAL(p,s) for precision p up to 38 and scale s
between 0 and p. If you import the number 20.030000000000001 into a
column of type, say, decimal(20, 8), it will be stored as 20.03 exactly.

SK

Ian wrote:

>SQL Table Output
>
>FloatField TextFloatField TextFloatFieldTwo
>10.03115 10.0312 198.000
>20.030000000000001 20.03 45
>30.030445499999999 30.0304 255.783
>40.030445589999999 40.0304 455.009
>50.030422199999997 50.0304 10.000
>60.030439999999999 60.0304 019.020
>
>
>Format Float Format Varchar Format Varchar
>
>
>
>
>
>Excel Input
>
>FloatField TextFloatField TextFloatFieldTwo
>10.03115 10.03115 198.000
>20.03 20.03 45
>30.0304455 30.0304455 255.783
>40.03044559 40.03044559 455.009
>50.0304222 50.0304222 10.000
>60.03044 60.03044 019.020
>
>Format General Format General Format Text
>
>

[Ian]

Thanks Steve

That is great.

Know i understand what is going on.

Thanks heaps for your help.

Ian

"Steve Kass" <sk...@drew.edu> wrote in message

news:eEsOoBve...@TK2MSFTNGP09.phx.gbl...

[MY Cheng]

Hi,

I am trying to import data from excel using tsql and using the openrowset as
shown below. However, when I execute the command through query analyzer, I
got the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give any
information about the error.].

Actually, before i try openrowset, i try bulk insert command as well.
However, since some fields value in the file to be imported into sql contain
",", so I found a field value like" syndey, nsw" is split into 2 columns. Do
you have any advice on that? Thanks for your help in advance.

Regards,

Irene Cheng