Unexpected chars in Excel

Question

I am using ADO.NET to fill a datatable from an Excel (xls) worksheet.

I got unexpected chars. At first I thought they came somehow during the import and so I tried to emininate them in the C# program but nothing I tried worked.

Finally I traced the chars back to Excel and I was able to use the replace function in Excel to replace the char with ''. These chars show up as blanks in Excel and I only found them by working backwards from their location in the datatable which I had dumped to a text file.

In Excel I also tried the clear formatting function. But that didn't do the job.

How do I filter the input in the datatable for only ascii chars (33 to 127)?

What kind of string do I get when I turn the datatable (typeof(System.String)) column into a string. I don't seem to be able to identify the chars when I convert the string to an array of chars.

Any suggestions? Since these chars were unexpected I want to be sure the spreadsheet input is filtered to keep only the visible printing chars and blank spaces. The text being imported should be just text, no numeric data...

The unexpected char that appears in the text file when I dump the table is ÿ.

Answer 1

Does your origin fields contain carriage return ("\r"), newline ("\n"), tab ("\t") characters _{(Jon Skeet answering even outside stackoverflow)} or NULL fields?

Try striping all those characters before sending the information to the database.

Answer 2

Thanks, Voyager, for your reply.

Not that I could tell. There were some nulls from empty cells but I had gotten rid of them. I tried to filter away any \r, \n, \t and other non printing chars. I've done that sort of thing in C many times, but I didn't seem to be able it in the C# program.

Finally I dropped down to the excel worksheet itself and with a vba macro (module or whatever it is called) got rid of all the offending chars ( less than 32 and greater than 126) There were a lot hanging around.

But all the data passes throught the C# program , one program vs many spreadsheets, so of course I'd prefer to fix the issue in Excel.