Re: Best way to Import data from EXCEL using a TSQL

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 08/04/04 

Date: Wed, 04 Aug 2004 12:28:02 -0400

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 *** 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Quantcast