Odd character display (ASCII 128-159)

[edeloup]

I have a couple of widely used ACCESS(2003) report generators taking data
from a csv dump(with quote text delimiter) from a 3rd party Internet hosted
application. Users input to this 3rd party application via an on-line form.
Often users cut and paste from other apps - eg Word and e-mail into the
on-line form. The problem appears to be that if the source doc uses "odd"
characters such as smart quotes, the final report shows some really strange
characters in their place. As these reports are reviewed by senior managers,
the overall appearance should be first-rate.

Is there any way to do a "bulk" character replacement at the CSV file level
eg replace all ASCII 128 to 159 with spaces. I could do a field by field
replace once in ACCESS but this is a last resort as the reporting structure
keeps changing. Note that the CSV records can be quite long and variable -
several descriptive fields. I did try to import initially as one memo field
per record but it appears that CR-LFs in some fields get treated as line
breaks - but are handled as internal field characters when linking/importing
as a delimited text file.

Thanks

 

[Steve]

Hi,

The quickest option would be to invest in a good text editor - don't know if
I can name names here or not but I've used Ultraedit in the past & it was
pretty outstanding. It - and others - can search and replace special
characters such as carriage returns and other ASCII characters with a null or
blank space using find and replace.

If that's not an option then you'll have to explore VB options. Potentially
opening the file as a filehandle and whizzing through it's contents replacing
unwanted ASCII characters as you go then re-writing the file and lastly,
importing it into Access. I can see that being problematic & would go with a
good text editor first to be honest.

If you look at my post on the 1/12/2010 in this forum titled 'Delete
'mystery' character from data (possible carriage return)' Jerry Whittles
response to that was really helpful to me - but deals with ASCII char 10
after it's imported into Access. That said, similar logic would apply if you
were to access the CSV file from an access vb procedure.