I have an ASP web app that takes a user-submitted CSV file and populates a SQL database table using the data. However, some users are exporting Excel files into CSV format, and its not always plain text. Examples would be typographer's quotes instead of plain text ", fancy apostrophe's instead of ', etc. So I end up with garbage characters in the table.
Is there some way I can reformat the data "on the fly" so the garbage characters will be converted properly? I don't want to simply delete them, they need to be intelligently converted into their corresponding ASCII character, whenever possible. I'm sure there are some characters that can't be converted. In BB Edit (my text editor), if I do a convert-to-ASCII it is smart enough to convert a © to (C), ® to (R), etc. That would be really nice, though not sure if its practical in this case.
I also don't know if this needs to be done on a character-by-character basis (which may slow the app down considerably), or if there is some global way to do the conversion?
I found this script, but can't seem to get it to work:
Would appreciate any advice. Thanks!