Best way to Import data from EXCEL using a TSQL

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" 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 /, [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" 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" 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" 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=29...8-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
> > >
> > >
>
>
>