Support is offline. We are back Monday 23 November, 09:00.

When exporting hikashop order to google sheet, prices begin with an ' #64196

Resolved Locked pouce opened this topic Tuesday, 22 September 2020 · 17 replies
Author

Hello, first of all thank you so much for this extension that saves me lots of time

i export about 18 fields of hikashop to a google sheet, there are full price and shipping price, shipping price tax

But each time i export the data, these fields are never number always text it seems because there is a ' before the number

I tried lots of ways to try to remove this ' of the number to be able to make some sum division etc.. on my spreadsheet

Have you got a trick to sort me out of this

REgards

Patrick

Support

Hello Patrick,

But each time i export the data, these fields are never number always text it seems because there is a ' before the number

This is something which Excel adds to make the column as Text instead of Number. Did you try changing the column to Number instead of Text from Format -> Number -> Number option in Google sheets?

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Author

Hello Tharuna,

I don't use Excel i just use RO csvi and export to Google sheets, but i don't want to format the cells on each export in case i set a cron

and also if i'd like to make sum or other mathematics on the sheets but i get a sum equal to 0. google sheets can't calculate

Patrik

Support

Hello Patrick,
We found the cause for this issue and have solved with the attached patch file.

  1. Load the attached patch file.
  2. In your Google sheets export template on Source tab you will find a new option called Value input option after To cell, set the value to User entered.
  3. Save the template.
  4. Run the export.

Let me know if that solves the number issue you have.

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Author

Hello and thank you for your reply,

I tested the patch but got an error see the snapshot and the log file attached to this message.

Thank you also to make custom development !! i really appreciate.

Patrick

  • Capture d’écran 2020-09-23 à 17.19.48.png
Author

Sorry it works

Thank you very much Tharuna :D

Support

Hello Patrick,
When you copied the patch file, did you see success message for all the file attached in the patch?

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Support

Hello Patrick,
Thank you for confirming that patch is working. This fix will be included in our upcoming RO CSVI Release.

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Hi,

I experienced the same issue (numbers are not a number because they are preceded by a ' character) when creating an export to Excel.

I downloaded the patch, however when running the template I now get this error message:

Your requested sheet index: -1 is out of bounds. The actual number of sheets is 0.

The site is running on Joomla 4.4.1 and has RO SCVI 8.9.3.

Kind regards, Frits

Support

Hello Frits,
This fix is already included with RO CSVI 7.18.0 version. Since you are using RO CSVI 8.9.3 version the fix is already included in the code. By loading patch file you are updating code to old version and there are possibilities to have errors. Please revert this change by updating RO CSVI VirtueMart addon 8.9.3 version. That will reset the file. Once you have done that, in Google export template on Source tab set Value input option to User entered. Save the template and run the export. Check if that fixes the issue.

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Hi Tharuna,

Thanks for your reply. Rolling back to version 8.9.3 helped to get rid of the error message.

However, I think I did not explain my situation clearly. I am experiencing the same issue, namely that numbers are not a number because they are preceded by a ' character. However, my export is in Excel format, not Google sheets. And (maybe because of this difference) I did not find the "Value input" option on the Source tab of my template (and neither on the other tabs).

Frits

Support

Hello Frits,
Sorry for late reply on this.

However, I think I did not explain my situation clearly. I am experiencing the same issue, namely that numbers are not a number because they are preceded by a ' character.

RO CSVI does not do any processing on number field values on export. It must be something to do with the excel software you are using. What if you try to open the export file using Microsoft excel or any other excel file software? Do you still see the same issue?

However, my export is in Excel format, not Google sheets. And (maybe because of this difference) I did not find the "Value input" option on the Source tab of my template (and neither on the other tabs).

Value input option is available only for Google sheet export and so you don't see it for normal excel export.

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Hi Tharuna,

I appended my question to this thread because of this part of the original title: "prices begin with an ' ". However, I do not use Google sheets, neither does our customer. In our setup, the Excel exports / reports from CSVI are downloaded directly to the computer of the user (which would be our customer or me).

It was the customer who found out that the amounts cannot be added up in Excel. I think that they are using Microsoft Excel. In order to know this for sure I mailed him but I got an auto replay saying they are out of office for some days. So unfortunately we will have to wait for that.

As for me, I am using LibreOffice on a MacBook.

Today I created a new Excel file. I opened it for viewing but I did not edit or save it on my computer. I uploaded it to this conversation. Could you please remove it from public view after downloading, for privacy reasons?

I also included a screenshot in the upload, showing the ' character.

Furthermore, I tried exporting the Excel file to csv (using LibreOffice) and after that importing the csv in LibreOffice again. When I do that, the formatting of the numbers as amounts (with 2 decimals) is removed, as is the ' character. As a result, I can make calculations with the numbers in the imported csv.

In general, about this issue: I have the impression that this was not the case when I created the export. Would it be possible that it occurred after an update of CSVI?

Kind regards, Frits

Sorry, I forgot the attachment.

And I should also add this information: the export is based on a custom sql which formats the amount like this:

IFNULL (FORMAT (t.amount, 2, "nl_NL"), "0,00") AS Bedrag
Support

Hello Frits,

Today I created a new Excel file. I opened it for viewing but I did not edit or save it on my computer. I uploaded it to this conversation. Could you please remove it from public view after downloading, for privacy reasons?

I had a look at the excel file and both Microsoft excel or libreoffice did not show me the ` character. See attached screenshot. Also I removed the attachment from your reply.

In general, about this issue: I have the impression that this was not the case when I created the export. Would it be possible that it occurred after an update of CSVI?

I don't think so RO CSVI update would have created this issue.

And I should also add this information: the export is based on a custom sql which formats the amount like this:

The ** 0,00** in query could be the problem. Excel must be treating it as text with comma character. Please try to use 0.00 in query instead. Check the excel export after that and let us know.

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Hi Tharuna,

Yes, this solves the issue indeed. Took me a while to realize that this formatting was involved, sorry about that.

And actually, formatting is not necessary when creating a report for downloading. I introduced this because we also created an on-screen version of this report - and our other reports as well. When viewing on-screen, the viewers do not need to make calculations, the reports just need to be comprehensive and clear. Hence the formatting.

When downloading a report as Excel, the users can alwaysadd formatting when they need it.

Kind regards, Frits

ps Could you please remove the last screenshots? Thx!

Support

Hello Frits,
Glad to know that fixed the issue.

And actually, formatting is not necessary when creating a report for downloading. I introduced this because we also created an on-screen version of this report - and our other reports as well. When viewing on-screen, the viewers do not need to make calculations, the reports just need to be comprehensive and clear. Hence the formatting.

Yes that makes reports clear and user friendly for users.

Could you please remove the last screenshots?

Done.

Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory

Thanks!

This topic is locked. New replies are not possible.

Log in to reply to this topic.