export data to Excel

Discussion in 'ASP General' started by atse, Oct 4, 2003.

  1. atse

    atse Guest

    Hi experts,

    I retrieve data from the database and display on ASP, then I export these
    data to a file, like Excel (the best) or text file. Is it possible? I think
    it is possible, but how can I do that? Thanks for any help.

    Atse
     
    atse, Oct 4, 2003
    #1
    1. Advertising

  2. atse

    Ray at Guest

    There are a couple of things you can do.

    1. Build your data in a table and add this to the top of your page:
    <% Response.ContentType = "application/vnd.ms-excel" %>


    2. Build yourself a comma delimited string and save the string to a file
    with a .csv file and link to it.

    3. Use Office Web Components:
    http://office.microsoft.com/downloads/2002/owc10.aspx. Before you'd go that
    route, read here though. http://support.microsoft.com/?id=317316

    If you need more details on 1 or 2, post back with a sample of your data
    querying and what not.

    Ray at home


    "atse" <> wrote in message
    news:7Vnfb.206907$...
    > Hi experts,
    >
    > I retrieve data from the database and display on ASP, then I export these
    > data to a file, like Excel (the best) or text file. Is it possible? I

    think
    > it is possible, but how can I do that? Thanks for any help.
    >
    > Atse
    >
    >
     
    Ray at, Oct 4, 2003
    #2
    1. Advertising

  3. atse

    atse Guest

    Thanks Ray. I remember you have given me great helps before. Yes, I really
    want to export a csv file.
    I have big csv files containing customers' contact info. I want to export
    them by zip code and type to respective csv files. What is the simplest way
    to do that? Thanks again.

    Atse


    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:...
    > There are a couple of things you can do.
    >
    > 1. Build your data in a table and add this to the top of your page:
    > <% Response.ContentType = "application/vnd.ms-excel" %>
    >
    >
    > 2. Build yourself a comma delimited string and save the string to a file
    > with a .csv file and link to it.
    >
    > 3. Use Office Web Components:
    > http://office.microsoft.com/downloads/2002/owc10.aspx. Before you'd go

    that
    > route, read here though. http://support.microsoft.com/?id=317316
    >
    > If you need more details on 1 or 2, post back with a sample of your data
    > querying and what not.
    >
    > Ray at home
    >
    >
    > "atse" <> wrote in message
    > news:7Vnfb.206907$...
    > > Hi experts,
    > >
    > > I retrieve data from the database and display on ASP, then I export

    these
    > > data to a file, like Excel (the best) or text file. Is it possible? I

    > think
    > > it is possible, but how can I do that? Thanks for any help.
    > >
    > > Atse
    > >
    > >

    >
    >
     
    atse, Oct 4, 2003
    #3
  4. atse

    Ray at Guest

    I will pretend that you have data as such:

    CustID Firstname Lastname Address City State ZIP
    1 Bo Brady 1 Street Somewhere XX 10001
    2 Hope Brady 1 Street Somewhere XX 10001
    3 Jack Deveraux 2 Road Somewhere XX 10002
    4 Jennifer Deveraux 2 Road Somewhere XX 10002
    5 Abe Carver 3 Ave. Somewhere XX 10003
    6 Lexie Carver 3 Ave. Somewhere XX 10003
    7 Tony Dimera 4 Lane Somewhere XX 10004
    8 Rex Dimera 5 Way Somewhere XX 10005
    9 Cassie Dimera 4 Lane Somewhere XX 10004
    10 Greta Von Amberg 6 Swamp Somewhere XX 10006




    So, like, you want a bunch of files like:
    10001.csv, 10002.csv, etc.? Maybe something like this:



    <object runat="server" progid="Scripting.FileSystemObject"
    id="oFSO"></object>
    <%

    Dim oADO, oRS
    Dim sOutput
    Dim aZIPs, i, sZIP

    Const OUTPUT_PATH = "D:\Path\"


    sSQL = "SELECT DISTINCT(ZIP) FROM Customers"
    Set oADO = Server.CreateObject("ADODB.Connection")
    oADO.Open YourConnectionString
    Set oRS = oADO.Execute(sSQL)
    aZIPs = oRS.GetRows()
    oRS.Close : Set oRS = Nothing

    For i = 0 To UBound(aZIPs, 2)
    sZIP = aZIPs(0, i)
    sSQL = "SELECT CustID,Firstname,Lastname,Address,City,State,ZIP FROM
    Customers WHERE ZIP='" & sZIP & "'"
    Set oRS = oADO.Execute(sSQL)
    sOutput = oRS.GetString(,,",",vbCrLf)
    oRS.Close : Set oRS = Nothing
    oFSO.CreateTextFile(OUTPUT_PATH & sZIP & ".csv", True).Write sOutput
    Response.Write "<a href=""" & sZIP & ".csv"">Click here to download CSV
    for ZIP code " & sZIP & "</a><br>"
    Next

    oADO.Close : Set oADO = Nothing
    %>



    What that'll do is get all the zips, then loop through them all, query all
    the data for each zip, and write a CSV from each resultset.

    Ray at home





    "atse" <> wrote in message
    news:n8pfb.207524$...
    > Thanks Ray. I remember you have given me great helps before. Yes, I really
    > want to export a csv file.
    > I have big csv files containing customers' contact info. I want to export
    > them by zip code and type to respective csv files. What is the simplest

    way
    > to do that? Thanks again.
    >
    > Atse
    >
    >
    > "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    > news:...
    > > There are a couple of things you can do.
    > >
    > > 1. Build your data in a table and add this to the top of your page:
    > > <% Response.ContentType = "application/vnd.ms-excel" %>
    > >
    > >
    > > 2. Build yourself a comma delimited string and save the string to a

    file
    > > with a .csv file and link to it.
    > >
    > > 3. Use Office Web Components:
    > > http://office.microsoft.com/downloads/2002/owc10.aspx. Before you'd go

    > that
    > > route, read here though. http://support.microsoft.com/?id=317316
    > >
    > > If you need more details on 1 or 2, post back with a sample of your data
    > > querying and what not.
    > >
    > > Ray at home
    > >
    > >
    > > "atse" <> wrote in message
    > > news:7Vnfb.206907$...
    > > > Hi experts,
    > > >
    > > > I retrieve data from the database and display on ASP, then I export

    > these
    > > > data to a file, like Excel (the best) or text file. Is it possible? I

    > > think
    > > > it is possible, but how can I do that? Thanks for any help.
    > > >
    > > > Atse
    > > >
    > > >

    > >
    > >

    >
    >
     
    Ray at, Oct 4, 2003
    #4
  5. atse

    atse Guest

    Great! Ray.

    You know what I want!! Ok, what if retrieve from a .csv (e.g. customer.csv)
    file, instead of the SQL db?
    Further questions are:

    1. I don't know how many groups of Zip (your example has group10001, group
    10002..group 10006. totally 6 groups), and I want to export 6 files, like
    10001.csv; ... 10006.csv which means the number of output file .csv would be
    a variable.

    2. If there is no field name in a .csv file but column/comma/table or A, B,
    C...X in an Excel file, how can I select them?

    3. Actually I have another field name "language" (eng/fre), I want them to
    be exported to files eng_10001.csv, eng_10002.csv, fre_10001.csv,
    fre_10003.csv. Is it possible for me to do that. Of course, I am sure you
    can.

    Thanks again!
    Cheers,

    Atse


    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:%23%...
    > I will pretend that you have data as such:
    >
    > CustID Firstname Lastname Address City State ZIP
    > 1 Bo Brady 1 Street Somewhere XX 10001
    > 2 Hope Brady 1 Street Somewhere XX 10001
    > 3 Jack Deveraux 2 Road Somewhere XX 10002
    > 4 Jennifer Deveraux 2 Road Somewhere XX 10002
    > 5 Abe Carver 3 Ave. Somewhere XX 10003
    > 6 Lexie Carver 3 Ave. Somewhere XX 10003
    > 7 Tony Dimera 4 Lane Somewhere XX 10004
    > 8 Rex Dimera 5 Way Somewhere XX 10005
    > 9 Cassie Dimera 4 Lane Somewhere XX 10004
    > 10 Greta Von Amberg 6 Swamp Somewhere XX 10006
    >
    >
    >
    >
    > So, like, you want a bunch of files like:
    > 10001.csv, 10002.csv, etc.? Maybe something like this:
    >
    >
    >
    > <object runat="server" progid="Scripting.FileSystemObject"
    > id="oFSO"></object>
    > <%
    >
    > Dim oADO, oRS
    > Dim sOutput
    > Dim aZIPs, i, sZIP
    >
    > Const OUTPUT_PATH = "D:\Path\"
    >
    >
    > sSQL = "SELECT DISTINCT(ZIP) FROM Customers"
    > Set oADO = Server.CreateObject("ADODB.Connection")
    > oADO.Open YourConnectionString
    > Set oRS = oADO.Execute(sSQL)
    > aZIPs = oRS.GetRows()
    > oRS.Close : Set oRS = Nothing
    >
    > For i = 0 To UBound(aZIPs, 2)
    > sZIP = aZIPs(0, i)
    > sSQL = "SELECT CustID,Firstname,Lastname,Address,City,State,ZIP FROM
    > Customers WHERE ZIP='" & sZIP & "'"
    > Set oRS = oADO.Execute(sSQL)
    > sOutput = oRS.GetString(,,",",vbCrLf)
    > oRS.Close : Set oRS = Nothing
    > oFSO.CreateTextFile(OUTPUT_PATH & sZIP & ".csv", True).Write sOutput
    > Response.Write "<a href=""" & sZIP & ".csv"">Click here to download

    CSV
    > for ZIP code " & sZIP & "</a><br>"
    > Next
    >
    > oADO.Close : Set oADO = Nothing
    > %>
    >
    >
    >
    > What that'll do is get all the zips, then loop through them all, query all
    > the data for each zip, and write a CSV from each resultset.
    >
    > Ray at home
    >
    >
    >
    >
    >
    > "atse" <> wrote in message
    > news:n8pfb.207524$...
    > > Thanks Ray. I remember you have given me great helps before. Yes, I

    really
    > > want to export a csv file.
    > > I have big csv files containing customers' contact info. I want to

    export
    > > them by zip code and type to respective csv files. What is the simplest

    > way
    > > to do that? Thanks again.
    > >
    > > Atse
    > >
    > >
    > > "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    > > news:...
    > > > There are a couple of things you can do.
    > > >
    > > > 1. Build your data in a table and add this to the top of your page:
    > > > <% Response.ContentType = "application/vnd.ms-excel" %>
    > > >
    > > >
    > > > 2. Build yourself a comma delimited string and save the string to a

    > file
    > > > with a .csv file and link to it.
    > > >
    > > > 3. Use Office Web Components:
    > > > http://office.microsoft.com/downloads/2002/owc10.aspx. Before you'd

    go
    > > that
    > > > route, read here though. http://support.microsoft.com/?id=317316
    > > >
    > > > If you need more details on 1 or 2, post back with a sample of your

    data
    > > > querying and what not.
    > > >
    > > > Ray at home
    > > >
    > > >
    > > > "atse" <> wrote in message
    > > > news:7Vnfb.206907$...
    > > > > Hi experts,
    > > > >
    > > > > I retrieve data from the database and display on ASP, then I export

    > > these
    > > > > data to a file, like Excel (the best) or text file. Is it possible?

    I
    > > > think
    > > > > it is possible, but how can I do that? Thanks for any help.
    > > > >
    > > > > Atse
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    atse, Oct 4, 2003
    #5
  6. atse

    Ray at Guest

    "atse" <> wrote in message
    news:Nhrfb.8669$ko%...
    > Great! Ray.
    >
    > You know what I want!! Ok, what if retrieve from a .csv (e.g.

    customer.csv)
    > file, instead of the SQL db?


    8|
    You're storing your customer data in a text file? 8| 8| 8!
    I suppose you could use a text connection string and try that.
    http://www.connectionstrings.com/



    > Further questions are:
    >
    > 1. I don't know how many groups of Zip (your example has group10001, group
    > 10002..group 10006. totally 6 groups), and I want to export 6 files, like
    > 10001.csv; ... 10006.csv which means the number of output file .csv would

    be
    > a variable.


    IN what I posted, that number is not known either. That is what the first
    query determines. (select count(distinct) zip...)



    >
    > 2. If there is no field name in a .csv file but column/comma/table or A,

    B,
    > C...X in an Excel file, how can I select them?


    What do you mean?


    >
    > 3. Actually I have another field name "language" (eng/fre), I want them to
    > be exported to files eng_10001.csv, eng_10002.csv, fre_10001.csv,
    > fre_10003.csv. Is it possible for me to do that. Of course, I am sure you
    > can.


    Yes, bring in that column and name your file
    rs.fields.item("language").value & "_" & sZIP & ".csv."


    Ray at home
     
    Ray at, Oct 4, 2003
    #6
  7. atse

    atse Guest

    > > You know what I want!! Ok, what if retrieve from a .csv (e.g.
    > customer.csv)
    > > file, instead of the SQL db?

    >
    > 8|
    > You're storing your customer data in a text file? 8| 8| 8!
    > I suppose you could use a text connection string and try that.
    > http://www.connectionstrings.com/
    >


    Yes, the data are mostly csv, dat or text format. Do I have to convert them
    to a unique format?

    >
    > > Further questions are:
    > >
    > > 1. I don't know how many groups of Zip (your example has group10001,

    group
    > > 10002..group 10006. totally 6 groups), and I want to export 6 files,

    like
    > > 10001.csv; ... 10006.csv which means the number of output file .csv

    would
    > be
    > > a variable.

    >
    > IN what I posted, that number is not known either. That is what the first
    > query determines. (select count(distinct) zip...)
    >
    > > 2. If there is no field name in a .csv file but column/comma/table or A,

    > B,
    > > C...X in an Excel file, how can I select them?

    >
    > What do you mean?
    >


    I mean the data file doesn't have the field(column) name, but separated by
    comma or tab. If it is open with Excel, you will see column A, B...X. Then,
    how can I do the query string
    "select [something] from [what] where [what] = '"& sZIP & "' and [what] ='"&
    Lang&"'"

    Having exported a file on D:\path\eng_10001.csv, how can I make it
    downloadable when the wwwroot is on C:\inetpub\

    Thanks,

    Atse
     
    atse, Oct 4, 2003
    #7
  8. atse

    Ray at Guest

    "atse" <> wrote in message
    news:3jEfb.216330$...
    > > > You know what I want!! Ok, what if retrieve from a .csv (e.g.

    > > customer.csv)
    > > > file, instead of the SQL db?

    > >
    > > 8|
    > > You're storing your customer data in a text file? 8| 8| 8!
    > > I suppose you could use a text connection string and try that.
    > > http://www.connectionstrings.com/
    > >

    >
    > Yes, the data are mostly csv, dat or text format. Do I have to convert

    them
    > to a unique format?


    No, it's just a bit odd in my opinion that you wouldn't be using a
    database - Access at least.

    >
    > >
    > >
    > > > 2. If there is no field name in a .csv file but column/comma/table or

    A,
    > > B,
    > > > C...X in an Excel file, how can I select them?

    > >
    > > What do you mean?
    > >

    >
    > I mean the data file doesn't have the field(column) name, but separated by
    > comma or tab. If it is open with Excel, you will see column A, B...X.

    Then,
    > how can I do the query string
    > "select [something] from [what] where [what] = '"& sZIP & "' and [what]

    ='"&
    > Lang&"'"


    Is converting to a database an option here? Even if that's only 1% feasable
    for whatever reason, go with that 1%. Excel files, csv files, text files,
    etc. are all great for storing a little bit of data and using it for
    personal use, but driving a website off such data is going to lead to
    multiple headaches. Again, just my opinion! :]

    Ray at home
     
    Ray at, Oct 5, 2003
    #8
  9. atse

    atse Guest

    > Is converting to a database an option here? Even if that's only 1%
    feasible
    > for whatever reason, go with that 1%. Excel files, csv files, text files,
    > etc. are all great for storing a little bit of data and using it for
    > personal use, but driving a website off such data is going to lead to
    > multiple headaches. Again, just my opinion! :]
    >
    > Ray at home
    >


    Yes, I ever thought of this way, and I have to do that if no other choice.
    The problem is about the format of the customer info in the .csv of .txt
    files. For example, in the .csv file, sometimes the first column is the
    title, the 2nd the last name... and totally there are 10 columns. But
    another, there may be 9 or 11 columns and in the different column position.
    How can I import the files to db? How can I know how many columns there are
    in this file?
    Thanks for any idea?

    Atse

    By the way, can you please show me the Excel connection string. I did try
    one from http://www.connectionstrings.com/
    but it somehow doesn't work. It complaint with below:

    Microsoft JET Database Engine error '80040e37'

    The Microsoft Jet database engine could not find the object 'xls'. Make sure
    the object exists and that you spell its name and the path name correctly.

    /list_item.asp, line 9



    the connection is below:

    con_xls="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\'test.xls;Extended
    Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    set conn=server.CreateObject("ADODB.Connection")
    conn.open(con_xls)

    'line 9 in list_item.asp

    set rs = conn.execute ("select * from test.xls")
     
    atse, Oct 6, 2003
    #9
  10. atse

    Ray at Guest

    You have an erroneous ' in your connection string there, right after D:\.

    Ray at home

    "atse" <> wrote in message
    news:Ng2gb.34595$ko%...

    >
    > Microsoft JET Database Engine error '80040e37'
    >
    > The Microsoft Jet database engine could not find the object 'xls'. Make

    sure
    > the object exists and that you spell its name and the path name correctly.
    >
    > /list_item.asp, line 9
    >
    >
    >
    > the connection is below:
    >
    > con_xls="Provider=Microsoft.Jet.OLEDB.4.0;Data

    Source=D:\'test.xls;Extended
     
    Ray at, Oct 6, 2003
    #10
  11. atse

    atse Guest

    Sorry, I copy and paste by mistake only. It wouldn't be there in my file.
    Any other problem? Thanks
    Atse


    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:...
    > You have an erroneous ' in your connection string there, right after D:\.
    >
    > Ray at home
    >
    > "atse" <> wrote in message
    > news:Ng2gb.34595$ko%...
    >
    > >
    > > Microsoft JET Database Engine error '80040e37'
    > >
    > > The Microsoft Jet database engine could not find the object 'xls'. Make

    > sure
    > > the object exists and that you spell its name and the path name

    correctly.
    > >
    > > /list_item.asp, line 9
    > >
    > >
    > >
    > > the connection is below:
    > >
    > > con_xls="Provider=Microsoft.Jet.OLEDB.4.0;Data

    > Source=D:\'test.xls;Extended
    >
    >
     
    atse, Oct 6, 2003
    #11
  12. atse

    Ray at Guest

    IIRC, you were using a "file" variable in your querystring. And I assume
    that was used in building your connection string. So, it sounds to me that
    the variable was empty.

    Response.Write YourConnectionStringVariable
    Response.End

    Ray at home

    "atse" <> wrote in message
    news:wh5gb.234912$...
    > Sorry, I copy and paste by mistake only. It wouldn't be there in my file.
    > Any other problem? Thanks
    > Atse
    >
    >
    > "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    > news:...
    > > You have an erroneous ' in your connection string there, right after

    D:\.
    > >
    > > Ray at home
    > >
    > > "atse" <> wrote in message
    > > news:Ng2gb.34595$ko%...
    > >
    > > >
    > > > Microsoft JET Database Engine error '80040e37'
    > > >
    > > > The Microsoft Jet database engine could not find the object 'xls'.

    Make
    > > sure
    > > > the object exists and that you spell its name and the path name

    > correctly.
    > > >
    > > > /list_item.asp, line 9
    > > >
    > > >
    > > >
    > > > the connection is below:
    > > >
    > > > con_xls="Provider=Microsoft.Jet.OLEDB.4.0;Data

    > > Source=D:\'test.xls;Extended
    > >
    > >

    >
    >
     
    Ray at, Oct 6, 2003
    #12
  13. atse

    atse Guest

    I did try that too. It displays a correct string.

    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:...
    > IIRC, you were using a "file" variable in your querystring. And I assume
    > that was used in building your connection string. So, it sounds to me

    that
    > the variable was empty.
    >
    > Response.Write YourConnectionStringVariable
    > Response.End
    >
    > Ray at home
    >
    > "atse" <> wrote in message
    > news:wh5gb.234912$...
    > > Sorry, I copy and paste by mistake only. It wouldn't be there in my

    file.
    > > Any other problem? Thanks
    > > Atse
    > >
    > >
    > > "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    > > news:...
    > > > You have an erroneous ' in your connection string there, right after

    > D:\.
    > > >
    > > > Ray at home
    > > >
    > > > "atse" <> wrote in message
    > > > news:Ng2gb.34595$ko%...
    > > >
    > > > >
    > > > > Microsoft JET Database Engine error '80040e37'
    > > > >
    > > > > The Microsoft Jet database engine could not find the object 'xls'.

    > Make
    > > > sure
    > > > > the object exists and that you spell its name and the path name

    > > correctly.
    > > > >
    > > > > /list_item.asp, line 9
    > > > >
    > > > >
    > > > >
    > > > > the connection is below:
    > > > >
    > > > > con_xls="Provider=Microsoft.Jet.OLEDB.4.0;Data
    > > > Source=D:\'test.xls;Extended
    > > >
    > > >

    > >
    > >

    >
    >
     
    atse, Oct 6, 2003
    #13
  14. also worth checking out http://www.greggriffiths.org/webdev/both/excel/

    atse wrote:

    > Hi experts,
    >
    > I retrieve data from the database and display on ASP, then I export these
    > data to a file, like Excel (the best) or text file. Is it possible? I think
    > it is possible, but how can I do that? Thanks for any help.
    >
    > Atse
     
    Greg Griffiths, Oct 8, 2003
    #14
  15. "atse" <> wrote in message news:<7Vnfb.206907$>...
    > Hi experts,
    >
    > I retrieve data from the database and display on ASP, then I export these
    > data to a file, like Excel (the best) or text file. Is it possible? I think
    > it is possible, but how can I do that? Thanks for any help.
    >
    > Atse


    Atse,

    SoftArtisans ExcelWriter can do exactly that... database-to-Excel web
    reports, and you don't need to have Excel installed on the web server.

    http://officewriter.softartisans.com/

    -chris
     
    Chris Baldwin, Oct 8, 2003
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Deva

    Export data from grid to Excel

    Deva, Jan 13, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,372
  2. Joy
    Replies:
    2
    Views:
    2,693
    Sabre
    Aug 22, 2003
  3. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,160
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  4. =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=

    Export to Excel (Default File Type - Excel)

    =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=, Apr 24, 2006, in forum: ASP .Net
    Replies:
    15
    Views:
    17,658
    syed hassan
    May 21, 2009
  5. Grey
    Replies:
    4
    Views:
    2,111
    Mark Rae [MVP]
    Oct 17, 2007
Loading...

Share This Page