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 14 12808
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" <du******@yahoo.com> wrote in message
news:7V**********************@news01.bloor.is.net. cable.rogers.com... 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
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:O6**************@TK2MSFTNGP11.phx.gbl... 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" <du******@yahoo.com> wrote in message news:7V**********************@news01.bloor.is.net. cable.rogers.com... 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
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" <du******@yahoo.com> wrote in message
news:n8*********************@news01.bloor.is.net.c able.rogers.com... 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:O6**************@TK2MSFTNGP11.phx.gbl... 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" <du******@yahoo.com> wrote in message news:7V**********************@news01.bloor.is.net. cable.rogers.com... 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
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:%2******************@TK2MSFTNGP11.phx.gbl... 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" <du******@yahoo.com> wrote in message news:n8*********************@news01.bloor.is.net.c able.rogers.com... 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:O6**************@TK2MSFTNGP11.phx.gbl... 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" <du******@yahoo.com> wrote in message news:7V**********************@news01.bloor.is.net. cable.rogers.com... > 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" <du******@yahoo.com> wrote in message
news:Nh*****************@news04.bloor.is.net.cable .rogers.com... 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
> > 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" <du******@yahoo.com> wrote in message
news:3j**********************@news01.bloor.is.net. cable.rogers.com... 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
> 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")
You have an erroneous ' in your connection string there, right after D:\.
Ray at home
"atse" <du******@yahoo.com> wrote in message
news:Ng*******************@news04.bloor.is.net.cab le.rogers.com... 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
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:uP**************@TK2MSFTNGP10.phx.gbl... You have an erroneous ' in your connection string there, right after D:\.
Ray at home
"atse" <du******@yahoo.com> wrote in message news:Ng*******************@news04.bloor.is.net.cab le.rogers.com...
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
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" <du******@yahoo.com> wrote in message
news:wh**********************@news01.bloor.is.net. cable.rogers.com... 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:uP**************@TK2MSFTNGP10.phx.gbl... You have an erroneous ' in your connection string there, right after
D:\. Ray at home
"atse" <du******@yahoo.com> wrote in message news:Ng*******************@news04.bloor.is.net.cab le.rogers.com...
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
I did try that too. It displays a correct string.
"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:uw**************@tk2msftngp13.phx.gbl... 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" <du******@yahoo.com> wrote in message news:wh**********************@news01.bloor.is.net. cable.rogers.com... 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:uP**************@TK2MSFTNGP10.phx.gbl... You have an erroneous ' in your connection string there, right after D:\. Ray at home
"atse" <du******@yahoo.com> wrote in message news:Ng*******************@news04.bloor.is.net.cab le.rogers.com...
> > 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
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
"atse" <du******@yahoo.com> wrote in message news:<7V**********************@news01.bloor.is.net .cable.rogers.com>... 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Matt |
last post by:
I have an ASP page that calls ASP routines that I created that execute
a database query and return the results to a recordset. I then iterate
through the recordset and display the data in a table....
|
by: Elena |
last post by:
I'm trying to export data to an Excel worksheet. I can export the data in
the cell values perfectly.
I need the code to change a header and footer for the worksheet, not for the
columns.
Is...
|
by: Jiro Hidaka |
last post by:
Hello,
I would like to know of a fast way to export data source data into an Excel
sheet.
I found a way from C# Corner(Query Tool to Excel using C# and .NET) which is
a neat little way of...
|
by: Hemant Sipahimalani |
last post by:
The following piece of code is being used to export HTML to excel.
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
|
by: JawzX01 |
last post by:
Hello All,
First, thank you for any help you can provide.
I'm trying to do a simple export to excel. I've used the classic
code that is all over the internet, and of course it worked without a...
|
by: smaczylo |
last post by:
Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm...
|
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am...
|
by: Merlin1857 |
last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can...
|
by: =?Utf-8?B?YzY3NjIyOA==?= |
last post by:
Hi all,
I have a question for you.
I have a .csv file which has many lines of data.
Each line has many data fields which are delimited by ",".
Now I need to extract part of data from this...
|
by: hemantbasva |
last post by:
Note We need to have a template on server for generating report in multiple sheet
as we do not had msoffice on server
moreover this require a batch job to delete excel file created by the...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |