Excel Sheet Export

[Ricky_Singh]

Hi All,

I have an Excel sheet which I recieve every week. Its not too much data
and the present process is to manually load that data into a SQL Server
2000 EE table.
But I would like to automate this process. I have tried using the
DTS, but it doesn't work. What I wud basically need is that a cell in
the Excel sheet could be loaded into a particular column. So suppose,
A12 goes into a column named Amount. There are around 10 columns in
the table.

Is a way to do that??

Thanks,
RIcky

--
Posted via [url]http://dbforums.com[/url]

[BobMcClellan]

I use this in an Active Script Task in a DTS to write to an .xls file....
I'm sure you can reverse it and use the .xls as the source and the
SQL table as the target.

hth
bob

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim xlApp
Dim xlBook
Dim i

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\~otg.util\ReservationList .xls")

Dim sqlText
sqlText = "SELECT " _
& "QRCHeader.Status, " _
& "QRCHeader.[QRC#], " _
& "Units.Location, " _
& "Addresses.Lastname, " _
& "QRCHeader.Active ha, " _
& "QRCDetail.Active da, " _
& "QRCDetail.[Unit#], " _
& "QRCDetail.OnRentDate, " _
& "Units.Status us, " _
& "Units.RentalStatus " _
& "FROM " _
& "QRCHeader " _
& "INNER JOIN QRCDetail ON " _
& "QRCHeader.[QRC#] = QRCDetail.[QRC#] AND QRCHeader.Division =
QRCDetail.Division " _
& "INNER JOIN Addresses ON " _
& "QRCHeader.[Addr#] = Addresses.[Addr#] " _
& "LEFT JOIN Units ON QRCDetail.[Unit#] = Units.[Unit#] " _
& "WHERE " _
& "QRCHeader.Active='Y' AND " _
& "QRCDetail.Active='Y' AND " _
& "Units.Status='R' AND " _
& "Units.RentalStatus<>'RE' " _


dim con
dim rs

set con = createobject("ADODB.Connection")
set rs = createobject("ADODB.recordset")

con.open = "Provider = SQLOLEDB.1; data source = (local); initial catalog
= EIS; user id = 'sa'; password = ;"
rs.open sqltext, con

i = 1

xlBook.Worksheets(1).Range("A5..m500") = Null
xlBook.Save

'For i = i To rs.recordcount

xlBook.Worksheets(1).Range("A" & i) = now()
i = 5
do while not rs.eof
xlBook.Worksheets(1).Range("A" & i) = rs.fields("Status")
xlBook.Worksheets(1).Range("B" & i) = rs.fields("qrc#")
xlBook.Worksheets(1).Range("C" & i) = rs.fields("Location")
xlBook.Worksheets(1).Range("D" & i) = rs.fields("LastName")
xlBook.Worksheets(1).Range("E" & i) = rs.fields("ha")
xlBook.Worksheets(1).Range("F" & i) = rs.fields("da")
xlBook.Worksheets(1).Range("G" & i) = rs.fields("unit#")
xlBook.Worksheets(1).Range("H" & i) = rs.fields("OnRentDate")
xlBook.Worksheets(1).Range("I" & i) = rs.fields("us")
xlBook.Worksheets(1).Range("J" & i) = rs.fields("RentalStatus")

rs.movenext
i = i +1
'Next
loop
xlBook.Save

rs.close
set rs = nothing

xlBook.Close
Set xlBook = Nothing

xlApp.Quit
Set xlApp = Nothing

Main = DTSTaskExecResult_Success
End Function




"Ricky_Singh" <member32195@dbforums.com> wrote in message
news:3062704.1057077041@dbforums.com...

>
> Hi All,
>
> I have an Excel sheet which I recieve every week. Its not too much data
> and the present process is to manually load that data into a SQL Server
> 2000 EE table.
> But I would like to automate this process. I have tried using the
> DTS, but it doesn't work. What I wud basically need is that a cell in
> the Excel sheet could be loaded into a particular column. So suppose,
> A12 goes into a column named Amount. There are around 10 columns in
> the table.
>
> Is a way to do that??
>
> Thanks,
> RIcky
>
> --
> Posted via [url]http://dbforums.com[/url]

[Steve Kass]

Ricky,

Depending on how the data is laid out on the Excel sheet,
you might capture it with something like this. A couple of
warnings are worth giving, though.

The extended stored procedure master..xp_regwrite is
undocumented and not recommended for production code.
You could change the registry entry permanently instead,
perhaps.

Perhaps more important, I don't believe there is any documentation
guaranteeing that this procedure will get the row numbers right. If
you have enough information in the spreadsheet to be able to identify
a row by the actual data in that row, you will be better off.

I wouldn't be surprised if there are other issues I'm not aware of,
but it might be an option if what others suggest doesn't work for you.


There are some other registry settings the influence how the Jet driver
behaves, and if you use this, you should do a good bit of testing to
be sure you don't import NULL where there is actually data in the table
or get other results that are wrong.


create table HoldExcelData (
rownum int identity(1,1),
-- use insert into with identity at your own risk
A varchar(20),
B varchar(20),
C varchar(20),
D varchar(20),
E int,
F int
-- whatever is appropriate for your data
)
go

-- may be needed if the first 8 rows of any column are empty
exec master..xp_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Jet\4.0\Engines\Excel',
@value_name='TypeGuessRows',
@type='REG_DWORD',
@value=100
go

insert into HoldExcelData(A,B,C,D,E,F)
select F1, F2, F3, F4, F5, F6
-- These are the names generated by the Jet driver for
-- the sheet columns. You can select whichever ones you need.
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=e:\excel\Example.xls',
'select * from [Sheet1$]')
-- If the sheet has been renamed, you may need to change this
go

-- reset registry key
exec master..xp_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Jet\4.0\Engines\Excel',
@value_name='TypeGuessRows',
@type='REG_DWORD',
@value=8
go
declare @cellC18 varchar(20)

set @cellC18 = (
select C from HoldExcelData
where rownum = 18
)

select @cellC18
go

drop table HoldExcelData

Steve Kass
Drew University

Ricky_Singh wrote:

>Hi All,
>
>I have an Excel sheet which I recieve every week. Its not too much data
>and the present process is to manually load that data into a SQL Server
>2000 EE table.
>But I would like to automate this process. I have tried using the
>DTS, but it doesn't work. What I wud basically need is that a cell in
>the Excel sheet could be loaded into a particular column. So suppose,
>A12 goes into a column named Amount. There are around 10 columns in
>the table.
>
>Is a way to do that??
>
>Thanks,
>RIcky
>
>--
>Posted via [url]http://dbforums.com[/url]
>
>