'', hexadecimal value 0x1A, is an invalid character

Question

I am using closedXML to take a datatable and place this into an Excel file.

The code I am working with works, with 99% of the files I put through the application, but I get an error with a file every now and then. (no this is not a debugging issue)

The problem must originate from the data, however I am at a loss on how to resolve this.

The code I'm using is

Public Sub WriteToExcel(dt As DataTable, filePath As String)

    Dim workbook = New XLWorkbook()
    Dim worksheet = workbook.Worksheets.Add(dt, "Call Validate Export")
    Try
        workbook.SaveAs(filePath)
        Process.Start(filePath)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

When saving the file I get the error

'', hexadecimal value 0x1A, is an invalid character.

between the '' there is a little arrow pointing to the right.

When reading the file in to the datatable it reads in fine, looking at the file I see no hex characters.

The file being read in is a ^ delimited csv file.

So, my question is how to I check and repair\replace the bad characters in the output that will allow me to save 100% of the time.

That is the [substitute character](https://en.wikipedia.org/wiki/Substitute_character) which should not be included in normal text and probably cannot be included in strings in Excel files. Try to loop over your datatable and remove all occurences of the character. — Raidri, Apr 20 '16 at 14:54

score 3 · Answer 1 · answered Sep 27 '16 at 14:15

From the XML specification ( https://www.w3.org/TR/xml/#charsets )

Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF] /* any Unicode character, excluding the surrogate blocks, FFFE, and FFFF. */

Which implies that character #x1A is an invalid character. You should manually remove it.

score 0 · Answer 2 · answered Aug 31 '16 at 20:12

@Simon,

It looks like whatever you are trying to export to Excel, it contains some invalid characters (Arrow pointing to right side).

I was receiving the similar error and upon detailed look, I come to know that I am exporting DataTable into Excel and one of the cell value of DataTable was looking like:

Please Note -> Some comment (Here, -> is actually a single character : Arrow pointing to right side)

I have removed those characters and it is working fine now.

Hope this helps.

score 0 · Answer 3 · answered Oct 14 '16 at 08:05

0

https://github.com/ClosedXML/ClosedXML/pull/66 is a pull request which could solve your issue. Please try it.

answered Oct 14 '16 at 08:05

Francois Botha

4,520
1
34
46

'', hexadecimal value 0x1A, is an invalid character

3 Answers3