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.
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
> 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.
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
>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.
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.
> 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
> -- 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.
> > 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.
> 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.
> > 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.
> > -- 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.
> > >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.
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.
>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.
>> 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.
>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTyp es
>>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.
>>> 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.
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.
> 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.
> >> 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.
>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.
> >>> 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.
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.
>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'.
>>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.
>>>> 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 pe
>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.
>>>>> 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.
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.
> 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.
> >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'.
> >>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.
> >>>> 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.
> >>>>> 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.
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.
>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.
>>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.
>>>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'.
>>>>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
>>>>>>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.
>>>>>>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)
> 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.
> >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.
> >>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.
> >>>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'.
> >>>>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
> >>>>>>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
> >>>>>>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)
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.
> > 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.
> > >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.
> > >>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.
> > >>>>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
> > >>>>>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
> > >>>>>>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
