[Solved] Export Data Tables - CSV corrupted data (hex 2 e-number)

#1

I have a database table containing a payload field which holds a string / ascii text representing the hex value, like “4E88F2”. When requesting the table to be exported to a CVS (auto emailed to my account) however, instead of providing the literal data - some of the (ascii-)hex values get mistakenly converted to e-numbers. And thereby destroying the hex-notation !!

For example: hex string “1814e6” gets exported to the CVS file, as “84E+09”.
Likewise: “eae898” gets converted to “7.452E+12”, which shows in excel as a value “1840000000”.

Certainly this shouldn’t be happening… Anyone else noticed this!?!

Thanks in advance,

Remco.

0 Likes

#2

I took a look at this - are you, perchance, using Excel to open the CSV? I created a sample data table with the two values you have above, and the raw CSV export looks correct:

id,hexString,updatedAt,createdAt
5b1167bc6ff9a60007b9f11f,1814e6,2018-06-01T15:35:24.228Z,2018-06-01T15:35:24.228Z
5b1167c5515c9500070e45cc,eae898,2018-06-01T15:35:33.284Z,2018-06-01T15:35:33.284Z

However, when I open that CSV it in Excel, Excel seems to assume things about the hex strings:

I don’t know how to keep Excel from doing that, although I’m looking around for an answer. In any case, Losant seems to be exporting the raw CSV correctly, as far as I can tell!

0 Likes

#3

Looks like if you tell Excel to import the file (easy way is to rename it as a txt file and then opening it), instead of just opening the CSV, you get an Excel dialog that lets you choose the data type of the columns. If you mark your hex string column as “text” in this import, Excel will open the file properly.

0 Likes

#4

Hi Michael,

It was my mistake indeed. Soon after importing, the CVS file got overwritten by an excel Save action; thus inserting the E+ / conversion issue. Not aware of this at the time, and looking at the (“original”) CVS file, my suspicions went immediately to Losant of course :wink:

my bad.

Remco.

0 Likes

#5

Great, glad that was your issue - I didn’t realize Excel would do that to CSV data either!

0 Likes