Professional Web Applications Themes

Excel Sheet Export - Microsoft SQL / MS SQL Server

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

  1. #1

    Default Excel Sheet Export


    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]
    Ricky_Singh Guest

  2. #2

    Default Re: Excel Sheet Export

    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" <member32195dbforums.com> wrote in message
    news:3062704.1057077041dbforums.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]

    BobMcClellan Guest

  3. #3

    Default Re: Excel Sheet Export

    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
    undoented 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 doentation
    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]
    >
    >
    Steve Kass Guest

Similar Threads

  1. How to get the name of an Excel sheet?
    By zz3top in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 11th, 05:36 AM
  2. Export to Excel shows an empty sheet
    By Paul D. Fox in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: April 11th, 03:07 PM
  3. exporting to an excel sheet
    By El Bucanero in forum PHP Development
    Replies: 7
    Last Post: June 16th, 08:47 AM
  4. Export IPTC-Headers into Excel-Sheet
    By Rike in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 0
    Last Post: August 26th, 08:08 AM
  5. Export to Excel sheet
    By Luke Zhang [MSFT] in forum ASP
    Replies: 2
    Last Post: July 1st, 02:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139