Best way to Import data from EXCEL using a TSQL

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.
IanIf 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 (drsql@.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" <ian@.NoWhere.com> wrote in message
news:%23kDlrwjeEHA.2804@.TK2MSFTNGP11.phx.gbl...
> 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
>
>
>
>
>
>
>
>
>|||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 wrote:
>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
>
>
>
>
>
>
>
>
>
>|||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" <skass@.drew.edu> wrote in message
news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> 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 wrote:
> >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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||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" <ian@.NoWhere.com> wrote in message
news:%235F6xokeEHA.3028@.TK2MSFTNGP12.phx.gbl...
> 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" <skass@.drew.edu> wrote in message
> news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> > 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 wrote:
> >
> > >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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>|||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 wrote:
>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" <skass@.drew.edu> wrote in message
>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
>
>>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 wrote:
>>
>>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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>|||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" <skass@.drew.edu> wrote in message
news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
> 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 wrote:
> >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" <skass@.drew.edu> wrote in message
> >news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> >
> >
> >>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\ImportMixedType
s
> >
> >
> >>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 wrote:
> >>
> >>
> >>
> >>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
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >|||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.
SK
Ian wrote:
>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" <skass@.drew.edu> wrote in message
>news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
>
>>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 wrote:
>>
>>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" <skass@.drew.edu> wrote in message
>>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
>>
>>
>>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\ImportMixedType
>>
>s
>
>>
>>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 wrote:
>>
>>
>>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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>|||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" <skass@.drew.edu> wrote in message
news:O%23q8QKleEHA.720@.TK2MSFTNGP11.phx.gbl...
> 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.
> SK
> Ian wrote:
> >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" <skass@.drew.edu> wrote in message
> >news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
> >
> >
> >>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 wrote:
> >>
> >>
> >>
> >>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" <skass@.drew.edu> wrote in message
> >>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> >>
> >>
> >>
> >>
> >>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\ImportMixedTyp
e
> >>
> >>
> >s
> >
> >
> >>
> >>
> >>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 wrote:
> >>
> >>
> >>
> >>
> >>
> >>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
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
begin 666 InputOutPut.txt
M4U%,(%1A8FQE($]U='!U= T*#0I&;&]A=$9I96QD"0E497AT1FQO871&:65L
M9 D)5&5X=$9L;V%T1FEE;&14=V\-"C$P+C S,3$U"0DQ,"XP,S$R"0D),3DX
M+C P, T*,C N,#,P,# P,# P,# P,# Q"3(P+C S"0D)-#4-"C,P+C S,#0T
M-30Y.3DY.3DY.0DS,"XP,S T"0D),C4U+C<X,PT*-# N,#,P-#0U-3@.Y.3DY
M.3DY"30P+C S,#0)"0DT-34N,# Y#0HU,"XP,S T,C(Q.3DY.3DY.3<)-3 N
M,#,P- D)"3$P+C P, T*-C N,#,P-#,Y.3DY.3DY.3DY"38P+C S,#0)"0DP
M,3DN,#(P#0H-"@.T*1F]R;6%T($9L;V%T"0E&;W)M870@.5F%R8VAA<@.D)1F]R
M;6%T(%9A<F-H87(-"@.T*#0H-"@.T*#0I%>&-E;"!);G!U= T*#0I&;&]A=$9I
M96QD"0E497AT1FQO871&:65L9 D)5&5X=$9L;V%T1FEE;&14=V\-"C$P+C S
M,3$U"0DQ,"XP,S$Q-0D),3DX+C P, T*,C N,#,)"0DR,"XP,PD)"30U#0HS
M,"XP,S T-#4U"0DS,"XP,S T-#4U"0DR-34N-S@.S#0HT,"XP,S T-#4U.0D)
M-# N,#,P-#0U-3D)"30U-2XP,#D-"C4P+C S,#0R,C()"34P+C S,#0R,C()
M"3$P+C P, T*-C N,#,P-#0)"38P+C S,#0T"0DP,3DN,#(P#0H-"D9O<FUA
H="!'96YE<F%L"0E&;W)M870@.1V5N97)A; D)1F]R;6%T(%1E>'0-"@.``
`
end|||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:
>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" <skass@.drew.edu> wrote in message
>news:O%23q8QKleEHA.720@.TK2MSFTNGP11.phx.gbl...
>
>>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.
>>SK
>>Ian wrote:
>>
>>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" <skass@.drew.edu> wrote in message
>>news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
>>
>>
>>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 wrote:
>>
>>
>>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" <skass@.drew.edu> wrote in message
>>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
>>
>>
>>
>>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\ImportMixedTyp
>>
>e
>
>>
>>s
>>
>>
>>
>>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 wrote:
>>
>>
>>
>>>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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>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
>|||Thanks Steve
That is great.
Know i understand what is going on.
Thanks heaps for your help.
Ian
"Steve Kass" <skass@.drew.edu> wrote in message
news:eEsOoBveEHA.1652@.TK2MSFTNGP09.phx.gbl...
> 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:
> >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" <skass@.drew.edu> wrote in message
> >news:O%23q8QKleEHA.720@.TK2MSFTNGP11.phx.gbl...
> >
> >
> >>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.
> >>
> >>SK
> >>
> >>Ian wrote:
> >>
> >>
> >>
> >>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" <skass@.drew.edu> wrote in message
> >>news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
> >>
> >>
> >>
> >>
> >>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 wrote:
> >>
> >>
> >>
> >>
> >>
> >>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" <skass@.drew.edu> wrote in message
> >>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> >>
> >>
> >>
> >>
> >>
> >>
> >>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\TypeGuessRow
s
> >>
> >>
> >>
> >>
> >>to 20
> >>Set
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTy
p
> >>
> >>
> >e
> >
> >
> >>
> >>
> >>s
> >>
> >>
> >>
> >>
> >>
> >>
> >>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 wrote:
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>>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
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
> >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
> >
> >|||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
"Ian" wrote:
> Thanks Steve
> That is great.
> Know i understand what is going on.
> Thanks heaps for your help.
> Ian
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:eEsOoBveEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > 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:
> >
> > >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" <skass@.drew.edu> wrote in message
> > >news:O%23q8QKleEHA.720@.TK2MSFTNGP11.phx.gbl...
> > >
> > >
> > >>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.
> > >>
> > >>SK
> > >>
> > >>Ian wrote:
> > >>
> > >>
> > >>
> > >>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" <skass@.drew.edu> wrote in message
> > >>news:uWvJ1xkeEHA.720@.TK2MSFTNGP11.phx.gbl...
> > >>
> > >>
> > >>
> > >>
> > >>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 wrote:
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>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" <skass@.drew.edu> wrote in message
> > >>news:eG%23FAAkeEHA.2044@.TK2MSFTNGP10.phx.gbl...
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>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\TypeGuessRow
> s
> > >>
> > >>
> > >>
> > >>
> > >>to 20
> > >>Set
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> >
> >>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTy
> p
> > >>
> > >>
> > >e
> > >
> > >
> > >>
> > >>
> > >>s
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>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 wrote:
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>>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
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >
> > >
> > >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
> > >
> > >
>
>