DataExport widget changes the column formatting after export

[Rocky_2021]

I have an infotable (from SQL query and processed in JS) in which one column is "Serial number" which sometimes has pure number data type and sometimes it is alpha-numeric. and hence the column values is converted into varchar

When this infotable is provided to "DataExport" widget, it downloads a ".csv" file, which shows the "Serial Number" column formatting as "General" and hence the pure numeric values displayed as "2.303036E+13".
Where as it should show something like 2303601000059.

I tried the filed definition datatype as "Number" as well as "String", but getting the same result.
Please suggest a solution on this.



Please find the screenshots below.




Thank you

[jensc]

Hello,

If I'm not mistaken, it looks like that because excel automatically converts long numbers into scientific notation.

There are multiple ways to prevent this it seems.

You could perhaps try adding a " ' " before the number as per this article: https://hub.acctivate.com/articles/stop-excel-from-changing-numbers-to-dates 

But I think also it should change if you increase the column size manually in excel? I don't quite remember though.

Hope this helps you.

Regards,

Jens

[Rocky_2021]

Hi @jensc,
Thanks for your suggestion, I wish this workaround could work, but I tried with concatenating the number with " ' ".

But If I do so, two things are happening, 
1. Loosing Column name 

2. " ' " is visible in the column values
&
The column size in excel doesn't make any changes.

[jensc]

Hello,

Ah okay... 

Have you tried converting the decimal number into an integer before exporting it?'

In JS you can use: Math.floor(yourNumber) and in SQL you can use: CAST(@yourNumber AS INT)

I wonder if either the export widget or excel does some weird conversion for that.

Or when you used SerialNumber as a string, that's when the ".00" was added?

What is the original data type?

Regards,

Jens

[Rocky_2021]

The infotable is generated by SQL query, and I Tried below but not working.
1. casting into INT,BIGINT and kept datashape as Number, Integer.
2. casting into varchar and kept datashape as String, Text

[jensc]

Hello,

This is very strange. I just did a quick test on 9.3.5 using the out of the box export widget:

Its source data binding is this:

With the data being sent to it being:

The CSV output looks like this when NOT opened in excel: 

So you are correct, it does make number variables into scientific notations.

But my string variable does return as a correct string, so that yours doesn't seems very strange to me.

I'll attach my test project so you can test it out on your platform to see if you can figure it out.

Regards,

Jens

[Rocky_2021]

imported project you provided and tested in my environment.
It gives same result as yours if opened in notepad.
but if the .csv file is opened in excel (which is the preferred application for .csv files), it gives the wierd result again.
please see the screenshot below

1. If open in MS Excel

2. If opened in Notepad

[wcui · Accepted solution]

I think thingworx write the correct content to csv file however it can not control how EXCEL render the format.

Our suggestion is either to directly use CSV file or set up beforehand how EXCEL handle the content.

Please let us know if you need any help for Thingworx side operation.