Excel - Rendering ASCII via ASP/JScript
 

The project I'm working on currently uses a ASP/JScript combination to
popuate a CSV file. The file is then parsed into excel. I've written a
function that handles some of the whitespace characters (via regular
expressions) that mess up how the data is displayed in the cells. Often, the
hidden char(10) and char(13) would push the data to another row. Due to the
way that this is written right now, I'm also searching for commas and
replacing them with the ASCII version as well as some other things to handle
data formatting. Due to the way these are put into the excel document they
aren't rendered as commas or anything else other than plain text. i.e Doe,
John would be rendered as Doe , John. So my question is, is there a way to
insert the ASCII code into the CSV file and have it be rendered in Excel as
its regular readable character. I'm aware that when parsing Excel tends to
infer that most of the input is text and as a result wont convert anything.
Is there a way around this programatically? If there isnt, is there a way to
include a macro that will run through the page and do ther conversion? I
realize this is kind of out there but I'm just looking for a solution to this
problem without having to re-write everything into an XML deal.

I'd take a different approach. I'd read the file in VBA, parse it there,
where I can control it, read non-printing characters, etc., then pass it
from VBA into the worksheet.

There used to be a decent article about File I/O on the MS web site, but
I can't find anything over there any more. Here are a few small articles
that tell only part of the story.

http://support.microsoft.com/?kbid=209231
http://support.microsoft.com/default...b;en-us;213642
http://support.microsoft.com/default...b;en-us;151262
http://msdn.microsoft.com/library/de...ssingfiles.asp
http://msdn.microsoft.com/library/de.../D6/S5B27D.asp

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

DMoe wrote:
The project I'm working on currently uses a ASP/JScript combination to
popuate a CSV file. The file is then parsed into excel. I've written a
function that handles some of the whitespace characters (via regular
expressions) that mess up how the data is displayed in the cells. Often, the
hidden char(10) and char(13) would push the data to another row. Due to the
way that this is written right now, I'm also searching for commas and
replacing them with the ASCII version as well as some other things to handle
data formatting. Due to the way these are put into the excel document they
aren't rendered as commas or anything else other than plain text. i.e Doe,
John would be rendered as Doe , John. So my question is, is there a way to
insert the ASCII code into the CSV file and have it be rendered in Excel as
its regular readable character. I'm aware that when parsing Excel tends to
infer that most of the input is text and as a result wont convert anything.
Is there a way around this programatically? If there isnt, is there a way to
include a macro that will run through the page and do ther conversion? I
realize this is kind of out there but I'm just looking for a solution to this
problem without having to re-write everything into an XML deal.