• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1121
  • Last Modified:

ASP to .CSV using GetString?

I'm trying to use the code below to produce a CSV compatible file. The data is dragged from a recordset containing 2 columns: 'username' and 'EmailAddress'.

It creates the file ok but its not formatted correctly, Excel moans 'the file is not in a recognisable format' and then just opens it as a single column. Its because the 'username' and 'EmailAddress' column headers arent there and the thing isnt formatted:

Dim strReturn
strReturn = RSEmailAddrs.GetString(2,,",",vbCrLf,"")
Response.AddHeader "Content-Disposition", "attachment; filename=CSV_Mail_Export.csv"
Response.Charset = "UTF-8"
Response.ContentType = "text/plain"
Response.BinaryWrite strReturn
Response.Flush
Response.Clear

How do i get this code to produce a 'proper' CSV file by dynamically including the DB column headers in the recordset and formatting it to keep Excel happy?

I've always done this sort of thing by looping through the RS and using FSO to physically create the file but this way looks loads neater.

Thanks.
0
PD69
Asked:
PD69
  • 6
  • 4
  • 4
  • +1
2 Solutions
 
jitgangulyCommented:
Try Response.ContentType = "application/vnd.ms-excel"
0
 
peh803Commented:
you should be able to achieve this using tab-delimiting .. perhaps that's what excel is wanting..?

Try it this way:
strReturn = RSEmailAddrs.GetString()

Since all arguments are optional for the function, this just tells the method to get a vbTab record-delimited and vbCrLf row-delimited string.

Does that help?

Thanks,
peh803
0
 
PD69Author Commented:
Ta, but tried that already, no joy.

Its the actual data thats being saved thats wrong. If i open the CSV file with notepad it looks like:

a@b.com,joebloggs
x@y.com,paultest
p@p.pp,johnsmith

See what i mean, its not CSV format theres no reference to columns.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PD69Author Commented:
A proper CSV format file should be along the lines of:

driverID, teamID, driverName, driverNationality
1, 1, Michael Schumacher, Germany
2, 1, Rubens Barrichello, Brazil
0
 
jitgangulyCommented:
No matter what you have to use Response.ContentType = "application/vnd.ms-excel"

Could you try withotu using getstring. I mean simple RS then loop through
0
 
PD69Author Commented:
Just found this working bit of VB code that creates the correct CSV file format shown above:

function ADOToCSV() {
Response.ContentType = "application/octet-stream";
Response.AddHeader('content-disposition', 'attachment;filename=drivers.csv');
// output column names
for(var x=0;x<objRS.fields.count;x++) {
Response.Write(objRS.fields(x).name + (x<objRS.fields.count-1?', ':''));
}
Response.Write('\n');
while(!objRS.EOF) {
for(var x=0;x<objRS.fields.count;x++) {
Response.Write(objRS.fields(x).value + (x<objRS.fields.count-1?', ':''));
} Response.Write('\n'); objRS.moveNext();
}
}

As you see the syntax isnt the ASP/VBScript i'm after but this one outputs the column headers correctly. Anyone know how to convert this to something compatible with my original code (without looping the recordset again if possible)?
0
 
jitgangulyCommented:
Instead of Binarywrite try with Response.write

Response.Write(strReturn)
0
 
peh803Commented:
to get the recordset field names in there, add code that looks something like this:

Dim strReturn = ""
for each field in RSEmailAddrs.fields
  if strReturn = "" then
    strReturn = field.name
  else
    strReturn = strReturn & "," & field.name
  end if
next
strReturn = left(trim(strReturn), (len(trim(strReturn))-1)) & vbCrLf
strReturn = strReturn & RSEmailAddrs.GetString(2,,",",vbCrLf,"")

Regards,
Phil / peh803
0
 
PD69Author Commented:
Doesnt matter how its written, its always going to be missing those column headers as theres nothing in my code to grab them in the first place.

What i need to do is:

1. get a list of all RSEmailAddrs.Fields.Name, comma seperated in sequence
2. Add a new line so Excel etc knows that these are the column headers
3. Render all the recordset entries as comma seperated values on a new line each (this is already working)

So:

a@b.com,joebloggs
x@y.com,paultest
p@p.pp,johnsmith

Becomes:

UserEmail,UserName
a@b.com,joebloggs
x@y.com,paultest
p@p.pp,johnsmith

As for the Response.ContentType = "application/vnd.ms-excel", doesnt matter, it could just be "application/octet-stream" or "text/csv" or "text/plain" as i dont want to open it embedded within excel, just saved as a file. They all do the same job in this case.
0
 
ThaSmartUnoCommented:
just to add to peh803's code ...

if you change this
strReturn = strReturn & RSEmailAddrs.GetString(2,,",",vbCrLf,"")
 to
strReturn = strReturn & """" & RSEmailAddrs.GetString(2,,""",""","""" & vbCrLf & """","")
strReturn = Left(strReturn,Len(strReturn)-1) ' since GetString will add an extra quote at the end

this is just in case someone's "name" for an email address has a , in it ... this will format like
the column row again is from Phil's code

Field1,Field2,Field3,Field4
"field1item","field2item","field3item","field4item"
"field1item","field2item","field3item","field4item"
etc...
0
 
PD69Author Commented:
peh803 'n ThaSmartUno - Cheers! that was the sort of thing i was after, it now sucessfully renders the column names as the top line.

Just got one final problem, Excel still doesnt like the file, the top line is not seen as column headers, just another line.

If i open the drivers.csv file using notepad theres little rectangles seperating the entries but none in my own file, i'm guessing this is a line break?

Do you know how i tell Excel that this top line denotes column names?

thanks again
0
 
ThaSmartUnoCommented:
actually I think you want quotes around the tables headers too
0
 
ThaSmartUnoCommented:
so ... like this
please note this is mostly peh803's code

Dim strReturn = ""
for each field in RSEmailAddrs.fields
  if strReturn = "" then
    strReturn = """" & field.name & """"
  else
    strReturn = strReturn & ",""" & field.name & """"
  end if
next
' not sure of this line here ---v
' strReturn = left(trim(strReturn), (len(trim(strReturn))-1))
' not sure of this line here ---^
strReturn = strReturn & vbCrLf
strReturn = strReturn & """" & RSEmailAddrs.GetString(2,,""",""","""" & vbCrLf & """","")
strReturn = Left(strReturn,Len(strReturn)-1) ' since GetString will add an extra quote at the end
0
 
peh803Commented:
SmartUno : thx for the credit.

Also, you're correct here:
   ' not sure of this line here ---v
   ' strReturn = left(trim(strReturn), (len(trim(strReturn))-1))
   ' not sure of this line here ---^

This line should be removed.

Again, thanks for the great additions of the quotes.  I should have included that myself.

Regards,
Phil / peh803
0
 
PD69Author Commented:
Gentlemen, thank you.
0
 
peh803Commented:
I'm happy I was able to help out.

@TheSmartUno: Once again, thanks for filling in the cracks in my code :)

Best regards,
Phil / peh803
0
 
ThaSmartUnoCommented:
Glad we could help.

Phil, I'm always better at critiqueing code than writing it on my own =)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now