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
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
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
Hello Patrick,
We found the cause for this issue and have solved with the attached patch file.
- Load the attached patch file.
- 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.
- Save the template.
- 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
Sorry it works
Thank you very much Tharuna :D
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
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
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
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
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!
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!
Log in to reply to this topic.