Categories
View Replies (13)

Resolved XLS Import - 0 records

  1. johnf
    johnf
  2. RO CSVI
  3. Monday, 08 July 2019
  4.  Subscribe via email
CSVI version: 7.12.0
Joomla version: 3.9.8
PHP version: 7.2.19

I have been using CSVI to perform a daily import of an online XLS file for the last year. Recently, my import stopped working. Now, when I 'Preview' the first few records of the import, I see the message, 'Import file contains 0 lines.' Of course, running the import does not do anything and simply posts '0' in the Records column of the Log file. However, when I open the XLS file with Google Sheets, or LibreOffice, the file opens as expected and I can view all of the records. At this point if I save the file (using LibreOffice for example) as an XLS file, the newly saved version of the file can be imported correctly by CSVI. This leads me to believe that some header information has recently changed in some XLS files and is causing trouble for CSVI. I have no control over the creation of the publicly posted online XLS file and really can't download the file manually and save it again as an XLS file every day before performing the import. Any help solving this issue would be greatly appreciated. The XLS file in question is available here.
0
Responses (13)
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

Can you post your debug log as requested in the blue box at the top of this page? It can also be useful to post a backup of your template, so we can run the import with your template and see what happens.

This sounds like we just need to see how the information is received. Doing a manual import kind of defeats the purpose :)
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
johnf
johnf
Accepted Answer Pending Moderation
Thanks for the quick response. Per your request, I have attached the debug file and a backup of my import template.

John
Attachments (2)
com_csvi.log.5167.zip
csvi_templates_ecscreening.com_20190710.xml
  1. more than a month ago
  2. RO CSVI
  3. # 2
rolandd
rolandd
Accepted Answer Pending Moderation
Hello John,

Thank you for the information. We have been investigating this issue and it hasn't been easy for sure. We were able to drill it down to some bogus bytes, they cause the script not being able to read it and that is where it stops. By re-saving the file as you do now, will fix this encoding issue and it can be read for import.

There is one more thing I am going to try and see if it works but needs some time to work that out but at least wanted to give you an update on what we found so far.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
rolandd
rolandd
Accepted Answer Pending Moderation
Hello John,

We seem to have figured this out. Using a different library to read the XLS file, it is now possible to import the file that you have trouble with. I am going to send you an email with the patch file for you to give a go.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 4
johnf
johnf
Accepted Answer Pending Moderation
Hi Roland,

I got the patch file and uploaded it. I received a lot of errors in the install log. I have attached the debug log that was created during the patch process.

Now, when I run the Import for the XLS file I just get a blank screen - no errors but the XLS import doesn't work at all. All other (non-XLS) Imports seem to continue to work fine.

Thanks,

John
Attachments (1)
com_csvi.log.5326.zip
  1. more than a month ago
  2. RO CSVI
  3. # 5
rolandd
rolandd
Accepted Answer Pending Moderation
Hello John,

The import problems you have now must be a result of the failed applying of the patch. What you can do, if you have FTP access to your site is unzip the content of the patch file locally and upload it to the corresponding folders on the site.

Can you do that and let me know how it goes?
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 6
johnf
johnf
Accepted Answer Pending Moderation
Hi Roland,

I reinstalled using FTP and that seems to have fixed it. It now lets me preview the XLS file correctly so I am guessing it will work. I am just waiting for my Cron job to run to verify that the import actually happens. I will let you know what happens. Thanks for your help with this.

John
  1. more than a month ago
  2. RO CSVI
  3. # 7
rolandd
rolandd
Accepted Answer Pending Moderation
Hello John,

Thank you for the update. Perhaps the patch was a little too much to handle for the upload :)

We did run the import ourselves and that worked but let's wait the result of your cron to be entirely sure but I have good hopes.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 8
johnf
johnf
Accepted Answer Pending Moderation
Hi Roland,

The patch you provided has enabled me to import the XLS file that was previously causing errors and works as part of my cron job. Thank you! However, the import is no longer working as it was before. While the import reads the XLS file and successfully imports the records, I end up with about 40K of mostly empty rows in my table from the XLS file which has a little over 5K rows. I don't know if it is related, but the import that I am using makes use of the Skip plugin to conditionally skip records during the import process. Could the patch have caused trouble for the Skip plugin? (I am not sure if I remember correctly but was there a patch for the Skip plugin that you issued to another user in another thread that I may have used at some point in the last year?)

At the moment I have written some PHP code to delete the empty rows in my table after the import but would prefer to have the import run the way it used to. Thanks again for your help with this.

John
  1. more than a month ago
  2. RO CSVI
  3. # 9
rolandd
rolandd
Accepted Answer Pending Moderation
Hello John,

The import is working too well :) What I think is happening is that the Excel sheet actually has the 40K empty rows, you just can't see this in Excel because they are empty. However any program reading the XLS file will also read the empty rows because even though they are empty they exist.

What I would suggest is to create a second skip rule, you can add as many as you like to a field, and set it to Skip empty fields. Using this, the empty rows should not be imported.

Let me know if that will work for you.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 10
johnf
johnf
Accepted Answer Pending Moderation
Thanks Roland,

This makes sense. Obviously the source file has changed significantly and it would not surprise me that they have saved the file with empty rows at the end. I have added a skip rule to take care of this and will wait for the cron to run to see if works. In terms of the original issue that you resolved (CSVI unable to read past a certain point in the XLS file), do you think it was because of a new/different version of Excel that the source file provider is using or some other reason? - I'm just curious.)

I will let you know how the new Skip rule works but I am guessing it will be fine.

John
  1. more than a month ago
  2. RO CSVI
  3. # 11
rolandd
rolandd
Accepted Answer Pending Moderation
Hello John,

do you think it was because of a new/different version of Excel that the source file provider is using or some other reason?
I do not know what software they use to generate these files but it definitely has a different signature than my own Excel files generated by Excel. That must be some other type not supported by our original library. The new library we are using in your case is able to read that type. We already had this planned but was expedited in your case. It is proving to be OK.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 12
johnf
johnf
Accepted Answer Pending Moderation
Hi Roland,


Thanks for all of your help with this. Everything appears to be working as expected!

John
  1. more than a month ago
  2. RO CSVI
  3. # 13
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!