text area to text field with mime type for excel

[tester321]

Hello peoples, i ran into a funny issue,

I have a text area box that some enters their address into. I'm writting the record set with this value in it to a .csv

Upon opening the csv, if the address was entered with a return in it, then this breaks up the record in the csv, putting it on two lines instead of one.

How can I handle for this?
Thank you.

 

[tester321]

Hi Again, i found that the csv was splitting the recordset value to a next line if the value contains a comma. which makes sense since it is a csv, i tried to replace the comma like so:
.....
Response.Write(",")
Response.Write(Trim(Replace(objRS("Address1"), ",", " ")))
Response.Write(",")
....

No luck though...

 

[tester321]

figured it out, i had to Instr for a comma and then replace with vbCrLf

I had to do it in two steps otherwise the csv would not produce.

Cheers

 

[rac2]

Commas, carriage returns, line feeds, and tabs create problems when storing and retrieving from databases as well as reading and writing CSV files. You may wish to look at stripping these characters immediately, first thing when processing your form data. See

http://msdn2.microsoft.com/en-us/library/hh277t8e.aspx

It may be that both the comma and the new line characters are causing problems when you write the CSV. It seems like your REPLACE() function would eliminate the comma. The following should eliminate the carriage return and line feed. It might be a good idea to eliminate each one specifically, rather than assuming the combination will always occur.

Replace(objRS("Address1"), vbCrLf, " ")
Replace(objRS("Address1"), vbCr, " ")
Replace(objRS("Address1"), vbLf, " ")

 

[Tarwn]

Another option would be to use an ADO object to read/write the data, exactly as you would from a table in the database. This lets the ADO object handle doing all of the necessary escapes and so on to write the data and later read it.
Basically it works just like connecting to a database. You create a connection object with a delimited text connection string (see here). Then you can execute SQL queries against the CSV file, using the filename in place of a table name.

The advantage of this method is that you don't have to worry about escaping your values properly (deleting all commas in a field is not what I would consider proper escaping) and you don't need anything complex for a reader.

If you decide not to go that route then you will probably want to escape your values properly. Any value that has a carriage return, line feed, or comma inside it needs to get double quotes around it. If you have a double quote in an entry (other than the two surrounding it) then you can escape it by replacing it with two quotes.
To read the data back in I would suggest a RegExp object with a suitable pattern. There is a VB6 example here:

http://www.xbeat.net/vbspeed/c_ParseCSV.php

that could be converted to VBScript fairly easily (the last example).

-T