1

I am coming across an issue where using JObject.Parse(); truncates my JSON read from a excel file. It does not work on my machine, but the will work for the exact same data on another person machine using the same method and implementation.

Basically, we are calling a method part of a internal framework that reads an excel doc (data provider) based on the test method that is calling it. Once the row is selected, it then pulls the data stored in the columns cell. The format of this data is a JSON format. I have used 3 different JSON validators to ensure the JSON is valid. JSON below this has just filler data as i cannot share the actual JSON

{
    "columns": [
        "column1",
        "column2",
        "column3",
        "column4",
        "column5",
        "column6",
        "column7",
        "column8"
    ],
    "data": []
}

when attempting to return the JSON as a JObject the below is done.

var data = JObject.Parse(MyObject.value.AColumn[0]);

Which returns the cell data as a JObject of the data in the cell specified in the above.

When debugging this, I have typed the JSON in the Excel cell and have gotten data to return, but at one point the data starts getting truncated as if there is a specific character limitation. But again, this works perfectly fine on someone else's machine.

I get this error because of the JSON being truncated:

Unterminated string. Expected delimiter: ". Path 'columns[10]' line 13, position 9.

We are using Newtonsoft to handle the JSON and Dapper for the connection.Query to execute a simple query against the xlsx spreadsheet.

What I am finding is that when executing the Query in the OLDB connection the returned string is maxing out only at 255 length. So this looks more like a Dapper / OLDBConnection issue where i need to set the max length higher.

Here is the code for that // executing the query and retrieving the test data from the specific column

var query = string.Format("select * from [DataSet$] where TestName = '{0}'", testName);

var value = connection.Query(query).Select(x =>
{
    var result = new MyObject{ TestName = x.testName };

    foreach (var element in x)
    {                           
        if (element.Key.Contains(column))
        {
            result.CustomColumns.Add(element.Value.ToString());
        }
    }

    return result;                        
}).FirstOrDefault();

Where x is a dynamic data type.

Has anyone come across this before? Is there some hidden character that is preventing this?

SomeEngineer89
  • 23
  • 4
  • 1
    is it possible that the JSON lib is correct? What is `Object.AColumn[0]` ? is it complete well-formed JSON? you need to isolate what part is failing - is it a fetch error ? a parse error? or a storage error? we can't tell you which (although my first guess would be on "storage", i.e. it is truncated in the original source data, and my second guess would be that the ADO.NET query has truncated it; I think the *least likely* option is that Json.NET forgot how to JSON) – Marc Gravell Jul 16 '18 at 22:33
  • Object.AColumn[0] is referencing to a Class Object that is defined in the framework. It contains 3 attributes, TestName, TestData and then a AColumn List of strings (to allow custom column names in the excel file) What this is doing is simply returning the data from the row and column that matched on the OLDBConnection query. The JSON is in a nutshell {"columns":["column1","column2"], "data":[]} There are several more columns. The error appears to be just a parse error due to the fact that when the json string is returned from the excel file..it is truncated.. – SomeEngineer89 Jul 16 '18 at 22:44
  • 2
    so: you're saying that when you look in Excel, the JSON content is intact, and when you query via ADO.NET, you're getting a truncated string? If so : great, we can forget about JSON.Parse or Json.NET in general: it isn't the fault of that *at all*. Have I understood correctly? If so: what ADO.NET provider are you using to query this? – Marc Gravell Jul 16 '18 at 22:50
  • Yes the JSON when placed into Excel is intact and does not overlap into different cells (was my initial guess as this has happened before to me) Updated question with some more details. but ADO.Net we are using is an OLEDB Connection with Dapper. Also keep it mind that this does work fine on another developers machine using same code and implementation. – SomeEngineer89 Jul 16 '18 at 22:52
  • If it works in one place but not in another, the string is probably different. Have you actually looked at (added a debug watch to) the string variable after the value has been retrieved from Excel? He sure to look for white space, linefeeds, and null characters, etc. – John Wu Jul 16 '18 at 23:08
  • @SomeEngineer89 looks like it is not reading full column on your machine. Most probably the version of Excel drivers does not allow the full string (JSON) to be read. Try debugging by seeing first what you get from `MyObject.value.AColumn[0]`. – TheVillageIdiot Jul 16 '18 at 23:18
  • It looks like it might be the Excel ODBC driver. Finding articles like this: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cedff479-99c5-40d3-9ec9-1bd05371bf2f/excel-source-text-truncation-of-columns-longer-than-255-characters?forum=sqlintegrationservices – Jacob Jul 16 '18 at 23:18
  • Your question is a little confusing... The title says that json.Parse is truncating your string. In addition to finding this unlikely reading on it seems more like your code for retrieving the json from an excel file is actually truncating the string... You might want to edit your question to clarify exactly where your problem is and while your at it to create an MCVE... – Chris Jul 16 '18 at 23:19
  • Related question: https://stackoverflow.com/questions/24865183/data-truncated-after-255-bytes-while-using-microsoft-ace-oledb-12-0-provider – Jacob Jul 16 '18 at 23:20
  • Thank you everyone for the information, the answer lies in the OLEDB Connection. Basically as mentioned by Jacob this was happening due to the first 8 rows being scanned and the type being determined. That being said, updating the Registry value fixed my issue. – SomeEngineer89 Jul 17 '18 at 00:37

1 Answers1

0

Answered in comments within the main question..

Issue was that the OLEDB connection was reading the fist 8 rows and determining the data type of subsequent rows.

The cells data being pulled was a JSON string. The OLEDB connection was reading the string, however when trying to Parse the string to a JObject, parsing was throwing exception.

Further debugging reviled that within the OLEDB connection when reading the row of data that the string was getting truncated at 255 characters. Formatting the columns did not fix then issue, nor did adding OLEDB settings when creating the connection.

What resolved this was updating the System Reg key used to determine how many Rows to read before determining data type/ length. This solution can be found in the comment section of the original Question. Or here Data truncated after 255 bytes while using Microsoft.Ace.Oledb.12.0 provider

SomeEngineer89
  • 23
  • 4