Resolved - Need help Exporting Tracking Number in Sales Report

[Dirty Butter]

I thought I had this all figured out for myself, but there's some formatting something or another I'm not doing.

Here's what I've done in reports.index.php:

Added line <td>{$LANG.orders.shipping_tracking}</td> and line <td>{$data.ship_tracking}</td> to create column for Tracking and data to fill it.

In sources/reports.index.inc.php:

Added 'ship_tracking' to the $orders array.

The Sales Reports shows my added column with the correct Tracking Number, such as 9400110200830512096641.

BUT, when I export the data as a csv file to Excel, the tracking number shows as 9.40011020083051E+21. I thought I could just use the Excel format column to change it back into the complete number, but instead of getting the correct number I get 94001102008305100000000.

So I suspect I need to add something to reports.index.inc.php so the tracking number gets exported as a string, rather than as a number.

Can someone offer a way to fix this?

[bsmither]

Having had some experience at getting Excel to treat incoming values in a CSV file as a certain data type, the solution (if I recall) is to have quotes around the value.

Several ways (choose just one) to do this: *Fix Excel, *pre-process the field(s), or *change the logic of the "to quote or not to quote" test.

I see in reports.index.inc.php at about line 97, this:

$values[] = (is_numeric($value) || !strpos($value, ',')) ? $value : sprintf('"%s"', addslashes($value));

This says, "If the value looks like a number or if the value does not have a comma (which is the delimiter) in it, use the value of this field as is. Otherwise, put quotes around it."(See *)

You want numbers to be numbers so your spreadsheet can do functions on them, so making everything a string isn't going to help.

We can make a one-case alteration, or we can make a more flexible edit.

The problem with how this statement is logically constructed makes adding a new condition somewhat problematic. We need the logical inverse. Let's start by rewriting the statement:

From: (is_numeric($value) || !strpos($value, ',')) ? $value : sprintf('"%s"', addslashes($value));

To: (!is_numeric($value) && strpos($value, ',')) ? sprintf('"%s"', addslashes($value)) : $value;

This says, "If the value is not numeric-looking and if it has a comma in it, then quote it. Otherwise, use the value of this field as is."

There is nothing wrong with putting quotes around anything that is not a number. So now we can drop the strpos() test.

To: (!is_numeric($value)) ? sprintf('"%s"', addslashes($value)) : $value;

"If the value is not numeric-looking, quote it."

One-case: Let's add our new condition.

To: (!is_numeric($value) || $field == 'ship_tracking') ? sprintf('"%s"', addslashes($value)) : $value;

"If the value is not numeric-looking or if it is specifically 'ship_tracking', quote it."

A bit more flexible: Above the foreach() statement (line 95), add: $force_quoted = array('ship_tracking'); and add to this array each new field you really need quoted.(See *) Then:

To: (!is_numeric($value) || in_array($field, $force_quoted)) ? sprintf('"%s"', addslashes($value)) : $value;

* I would also suggest quoting the cart_order_id value. I have experienced Excel treating such data as a formula and showed the answer to 120912 minus 065301 minus 3812.

OR:

We might try pre-processing the field like the statements at lines 87-89 do.

$order_summary['ship_tracking'] = sprintf('"%s"', $order_summary['ship_tracking']);

OR:

We might try fixing Excel. Open a blank spreadsheet and apply specific formatting to the columns that you know give you trouble. For example, if column H will have the 'ship_tracking' column, then format the cells in column H as 'text'. Save this blank worksheet as a template. Then using a working document taken from the template, "import" the external data file (Files of type: text). When building the template, you might also put some additional analysis on Sheet 2 because we don't know how many rows will be on Sheet 1.

[Dirty Butter]

I really appreciate your help, Bsmither.

I added this special case to the others about line 87:

$order_summary['ship_tracking'] = sprintf('"%s"', $order_summary['ship_tracking']);

I added this above the foreach section about line 95:

$force_quoted = array('ship_tracking');

And I changed line 100 to

$values[] = (!is_numeric($value) || in_array($field, $force_quoted)) ? sprintf('"%s"', addslashes($value)) : $value;

This is what I ended up with in the ship_tracking column in Excel: 9405510200830517219956""

Is this the way it should look in Excel? I did a find/replace to get rid of the back slashes, but when I tried to take the double quotes out of the Excel column it reverted to the E+21 format.

*The cart_order_id behaved itself in Excel, so I didn't bother that. But I did the same process for the ship-date to get the format that Google Trusted Sites requires - with the same backslash and quotes results - 2012-09-17"".

[bsmither]

Of these options:

* I added this special case to the others about line 87:

* I added this above the foreach section about line 95: And I changed line 100 to:

Please choose just one.

[Dirty Butter]

I double checked, but I had already tried all the different combinations, and the only combination that kept the full string value in Excel was when I used all three edits.

[bsmither]

Let's try putting everything back the way it was, then apply just this at about line 87:

$order_summary['ship_tracking'] = sprintf('"%s"', $order_summary['ship_tracking']);

Export the file.

Now, open the CSV file in a text editor. See if the quotes are actually in the CSV file.

[bsmither]

Stupid Excel. Even with quotes, to Excel, the value still appears to be a number and treats it that way.

Hang on.

[Dirty Butter]

I saved it and opened it with Notepad. It shows the whole tracking code with quotes, as long as I don't open it in Excel.

[bsmither]

Ok, so Excel uses quotes as a "Text Qualifier", which means to me that this thing between commas is not to be treated specifically as 'text', per se (and that is according to the rules of CSV - no data-type indicators allowed). But rather, ignore what could be considered delimiters (commas, tabs, spaces, semicolons, etc) found between the quote marks. Then discard the quote marks and apply the cell's formatting to the value. A default spreadsheet has "General" formatting -- which means to go ahead and figure out what the cell's contents appear to be and display it that way.

Excel will always remove leading zeros from fields before displaying them. It will also always remove leading spaces. So, zip-codes will get munged.

So, what I thought might work is to not use quotes, but rather apostrophes. Alas, the apostrophes do show in the spreadsheet cell. But otherwise, it's text.

According to some solutions I found for Excel (read, Microsoft) just being too smart for its own good, this will work, but probably only for Excel.

$order_summary['ship_tracking'] = sprintf('"=""%s"""', $order_summary['ship_tracking']);

Best to copy/paste that.

[Dirty Butter]

THAT worked!! Hurray! Whoopie!! BRavo!!

[bsmither]

So, the correct way to do this is to create a module in the /modules/external/ folder to create a file specifically for Excel's quirks.

I would also like to see a module for XML-formatted report data.

[Dirty Butter]

So, the correct way to do this is to create a module in the /modules/external/ folder to create a file specifically for Excel's quirks.

I would also like to see a module for XML-formatted report data.

The same process worked to give me the ship_date in the format Google requires.

$order_summary['ship_date'] = sprintf('"=""%s"""', $order_summary['ship_date']);

Sounds like something else for you to suggest on the bug tracker.

Just for your information, as it's not a problem for my needs at all, the odd "="" shows on the table in Admin.

[Toucan Web Design]

I was tearing my hair out over this a few weeks back.. it's hard to believe there's no way to turn off them -changing- vital information in what is essentially a data storage software, but.. nope

[bsmither]

Sorry, I couldn't parse your phrasing. Could you elaborate on: "no way to turn off them -changing- vital information"?

[Toucan Web Design]

There's no way to turn off the automatic rewriting of numbers to various notations. You can usually get round them converting to scientific format if you explicitly set them as numbers, but then that ruins any product codes beginning with a 0, setting to text to allow those triggers the scientific notation

You just can't stop excel reformating your data and it's a bit odd

[bsmither]

Ah! So we are blaming Excel?

Can't really place blame as the (un)official description of CSV describes the format as being simply a "data carrier". That is to say, whatever is between the delimiters could be anything. The CSV format does not allow for type casting. CSV does not even specify that the data be human readable or ASCII transportable. If you can manage to load the CSV file with binary data, CSV says to carry it.

Since there is no type-casting in CSV, we then must adapt to how any application chooses to deal with the values. Which is why one of my suggestions was to create an Excel template and import the data into the spreadsheet as opposed to opening the CSV file as a spreadsheet.

[Toucan Web Design]

Kind of can blame excel, seeing as it's excel that's doing it. The same behaviour happens in xls or xlsx format as well, regardless of type casting.

As I mentioned, if you have an export of data coming in that may either contain long numbers or leading 0s, excel flat out cannot handle it. You can put in bodge fixes for one, but then the other won't work

That's the issue I'm talking about, the fact that as a data reader, excel is somewhat unreliable due to the fact that it changes things, and you can't turn off the changes it makes.