Reading XML with numbers formatted with opposite decimal character (period/comma) in .NET

Question

My VB.NET app is importing a XML file generated by a 3rd-party website into a SQL Server table. The website (and my computer) use the period character for decimals (e.g. 42.015) and everything works great. But a European user reported that numbers imported were being multiplied by a factor of 1000 or 10000. It turns out that his computer is looking for a comma decimal (e.g. 42,015) and when it sees the XML input it converts it to 42015.00.

I'm using DataSet.ReadXML and SqlBulkCopy.WriteToServer and I'm not sure where I can step in to tell the program to expect period decimals. My code is below:

    Dim ds As New DataSet
    Try
        ds.ReadXml(tempfile, 0)
    Catch ex As XmlException
        Log($"Error reading XML: {ex.Message} with {ex.Data}")
        Exit Sub
    End Try
    Dim columnMap As New Dictionary(Of String, String) From {
        {"LOTID", "InventoryID"},
        {"ITEMTYPE", "ItemType"},
        {"ITEMID", "ItemNum"},
        {"COLOR", "ColorID"},
        {"CONDITION", "Cond"},
        {"REMARKS", "LocationName"},
        {"QTY", "Qty"},
        {"DESCRIPTION", "Description"},
        {"SUBCONDITION", "Completeness"},
        {"SALE", "Discount"},
        {"STOCKROOM", "Stockroom"},
        {"BULK", "Bulk"},
        {"BUYERUSERNAME", "Reserve"},
        {"PRICE", "Price"}
    }
    Using conn = New SqlConnection(GetDBConnectionString)
        Using sbc As New SqlBulkCopy(conn)
            conn.Open()
            DoSql(conn, "TRUNCATE TABLE dbo.Online_Inventories;")
            For Each column As DataColumn In ds.Tables("ITEM").Columns
                If columnMap.ContainsKey(column.ColumnName) Then
                      sbc.ColumnMappings.Add(column.ColumnName, columnMap(column.ColumnName))
                End If
            Next
            sbc.DestinationTableName = "Online_Inventories"
            sbc.WriteToServer(ds.Tables("ITEM"))
            conn.Close()
        End Using
    End Using

The XML imported looks like this:

   <ITEM>
      <LOTID>217770136</LOTID>
      <DATEADDED>9/20/2020 3:02:00 PM</DATEADDED>
      <CATEGORY>771</CATEGORY>
      <COLOR>0</COLOR>
      <PRICE>11.7563</PRICE>
      <QTY>1</QTY>
      <BULK>1</BULK>
      <IMAGE></IMAGE>
      <DESCRIPTION></DESCRIPTION>
      <CONDITION>U</CONDITION>
      <SUBCONDITION>I</SUBCONDITION>
      <ITEMTYPE>S</ITEMTYPE>
      <ITEMID>41110-1</ITEMID>
      <SALE>0</SALE>
      <REMARKS></REMARKS>
      <STOCKROOM>Y</STOCKROOM>
      <MYWEIGHT>0</MYWEIGHT>
      <ITEMWEIGHT>0</ITEMWEIGHT>
      <DATELASTSOLD></DATELASTSOLD>
      <BASECURRENCYCODE>USD</BASECURRENCYCODE>
   </ITEM>

So in this example, after the third line (ds.ReadXml), ds("Price")="11.7563", a string After the line sbc.WriteToServer, the value of dbo.Online_Inventories.Price is 117563.0 (actually an error in this case because Price is a NUMERIC(9,4))

How do I get .net to read periods as decimals when the user's home culture uses commas as decimals? Thanks!

The dataset pulls them in as strings and the SqlBulkCopy attempts to convert it into numbers to match the table definition. — Scott, Oct 06 '20 at 22:09
Try doing the conversion before, put real numbers in the dataset, parsing them taking into account the format in the XML file. — Alejandro, Oct 06 '20 at 22:17
Even if I define the dataset with Price as a numeric type, I still have the problem that in Europe the user will be pulling in period decimals and their system expects a comma — Scott, Oct 06 '20 at 22:40
That only becomes a problem when displaying the data. Numbers have inherently no format at all, so comma/period is of no concern. You first read the XML (which I assume comes with a fixed format you know beforehand), then store in the DB (again in a numeric column with no trouble for formatting), then at some other point you read it back and format it for displaying, here taking into account the user's locale for using the proper symbols. — Alejandro, Oct 06 '20 at 23:09
I'm sorry but that's not true. Reading the XML into a table is the problem. The XML has formatting which confuses the interpreter and the numbers are stored 1000 times higher than intended. — Scott, Oct 06 '20 at 23:21
That's why I'm suggesting putting a fixed format into the parsing portion, just when you get the data out of XML, using a locale-independent parse. Anything after that just deals with numbers, where formatting doesn´t exists. — Alejandro, Oct 06 '20 at 23:30
So that's the exact question. How do I use a locale-independent parse of the xml and what do I do differently from DataSet.ReadXml(filename)? — Scott, Oct 07 '20 at 01:00
For a simple hack, I believe that you should be able to resolve this by: 1) store the current thread CultureInfo before the code, 2) change the thread culture to InvariantCulture (`Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture`), 3) after the code, restore the thread culture to the stored value. This way the default parsing will use the InvariantCulture decimal mark (a period). — TnTinMn, Oct 08 '20 at 01:17
@TnTinMn this works, thanks! Do you want to add it as an answer? — Scott, Oct 08 '20 at 22:23

score 1 · Accepted Answer · answered Oct 08 '20 at 23:31

The default thread CultureInfo is based on the running machine's set culture. Default string parsing will use the default CultureInfo. You can change the thread CultureInfo to use the InvariantCulture (basically en-US) while executing the code you posted. The InvariantCulture uses a period(.) for the decimal mark.

Dim currentCulture As CultureInfo = Threading.Thread.CurrentThread.CurrentCulture
Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture
' ***
' insert your code here
' ***
Threading.Thread.CurrentThread.CurrentCulture = currentCulture

Reading XML with numbers formatted with opposite decimal character (period/comma) in .NET

1 Answers1