Can Not Upload Products Database Excel File

[truesdell]

My clients have corrupted their Products database.

I dumped it into an Excel file,
manually fixed everything (I think),
and now I'm trying to upload that file back into the database.

What I get is "Connecting..." forever and it eventually times out.

Can anyone offer tips on getting this working? ShopSite 11.
Should I be using something other than Excel?
I do not trust Microsoft file formats.

I've tried saving as older versions of Excel, as tab-delimited text, all to no avail.

My guess it that it's a file format issue, but I'm stabbing around in the dark here.

[Jim]

What method are you using to upload the file? Are you doing it from the backoffice Utilities > Database > Upload or are you using a API script?

For Excel files they must be in the older xls format not the newer xlsx in order for ShopSite to import them.

If you are using an automated API script make sure that the store has not enabled the ShopSite login method unless the script has been upgraded to use the Oauth authentication method. If the ShopSite login has been enabled when the merchant logs in to the store they will be prompted with 3 fields, User, Password and Storeid, if the older login method is enabled they will just be prompted with two fields User (which will be the storeid) and password.

[truesdell]

backoffice Utilities > Database > Upload.

file is older .xls.
_________________________

experiment last night:

• Download Products database.
• Empty Products database.
• Upload file I just downloaded minutes ago.

Connecting... ... ... ... time-out

[Jim]

How big is the file that was downloaded? Your server may have a time limit on a process and if the file that is being uploaded is large it might timeout before the file completes. If that is the case you could try splitting the file into smaller files and upload each file separately. You might also be able to upload the file using ftp and place it in the output directory of the store. Then you can select the file from the server location instead of having the upload transfer if from your local machine.

[truesdell]

filesize is 1.2 MB
permissions are 644

Error

Unable to open upload file [/www/company/company_products_2013_04_16_edit_04.xls] for reading.
Either the file you specified does not exist on the server, or you do not have access rights to the file.

I'm thinking permissions are screwed up somewhere in my ShopSite config.

[Jim]

644 should be ok in most cases, however, if you FTP'd the file to the server, the ftp user may have a different user/group than the webserver user. Make sure the user and group are the same as what files in the directory published by ShopSite have.

[truesdell]

That's scary territory.

Most of the ShopSite files have owner/group set to nobody/nobody.

If I chown my uploads to that they will be forever untouchable by me.

[truesdell]

Backstory:

The president of the company gave me a file to upload price changes.
Unbeknownst to me, prices were not the only fields that had been edited.

He (or someone) edited some of the SKUs and Product Names.
Many of the SKUs were changed from like:

"123456"
to
"123456 - Oversized Call For Shipping Price"

In other instances, the SKUs remained valid but Product Names were changed.

After the price update upload there were massive numbers of duplicate records, some duplicates with different versions of the "Oversize" text SKUs and some duplicates with identical SKUs but different Product Names.

Since that one upload I have not been able to upload a [painfully hand-repaired] file -- either .txt or .xls.

I even tried downloading the Products database, not touching it at all, then reuploading that virgin file, as delivered from ShopSite. It goes into the Connecting... loop forever.

[Jim]

You could get a demo store at shopsite.com/demo.html and see if you can upload the file there, using Utilities > Database > Upload. That will indicate if the file is what is causing the problem or if it is something on the server your store is on.

Cleaning up a store that has had changes made to skus, name and different combinations of each can be a headache. There isn't really an easy way to do it other than to sort the data by one or the other and then deleting the ones that aren't valid.

If there is a backup of the store from before the uploads were made that may be a better way to get the store back to a known state and then you can make needed changes. Be aware that restoring from a backup would remove order that had been placed between the time the backup was made and the time the restore was done. Orders in the last few days could be retrieved by renaming the iorder###.done files for the missing orders to iorder###.new (in the store data/ip directory) and then going to the backoffice order screen. The iorder###.done files are only kept for 5-7 days so anything before that time would not be salvageable.

As for uploading by ftp and setting the permissions you could give the file 666 permissions and I think that should allow you to access it after you change the owner to nobody:nobody

[truesdell]

Follow-up:

Per Jim's advice I created a demo store and attempted an upload.

• Same deal: "Connecting..." forever, then a time-out.
• So I split the file into 3 parts of approx 250 line-items each.
• Part 1: Upload OK.
• Part 2: "Connecting..."
• So I split Part 2 into 3 parts (Part 2a, Part 2b, Part 2c) At this point I didn't think it was a file size issue because the file was not substantially larger than Part 1; my intention was merely to subdivide into smaller sets I needed to comb through to find an error. But apparently there was no error.
• Part 2a, Part 2b, Part 2c, Part 3: Upload OK.
• Check backoffice > Products: everything is there.

So I went back to my client's site and repeated the 5-part upload and everything turned out better than expected.
Filesize appears to be a pretty critical issue. This has been a very time-consuming and stressful lesson for me.
Very.
______________

During the Publish operation I was greeted with several hundred errors mostly dealing with "[142]: Template Error: IF using PAGE tag in non-page template" which is an artifact of the original developer's straying very far off the ShopSite reservation.

This site was implemented very poorly mostly as a result of the client president telling the original developers "Faster! Cheaper!"
I am very careful not to point a finger, but I have mentioned the site appears like it was "rushed" and that a lot of "corners were cut" which, without having to admit anything, the president acknowledges that the site was rushed. I have an outstanding rapport with the president and he has admitted the site needs pretty much to be entirely refactored.

It's going to involve basically starting from scratch.

[Jim]

If there are less than 1000 products, there must be a lot of data in the product fields (description, moreinfo page info etc) for the upload to fail. During testing I often upload data for an entire store with 32000+ products and several thousand pages and it never times out.

In the publish error message "[142]: Template Error: IF using PAGE tag in non-page template" the [142] indicates what line number in the file the problem is on. (It could be off a line or two depending on the editor you look at the file in). The template name should also be displayed in the error message. But you will be looking for a tag with [-- IF PAGE.<value> --] tag which are not allowed in product templates since it is a tag for page data. The reason PAGE.<value> are not allowed in product templates is that when that part of the html is generated it does not know which page the product is being generated for, since products can be on more than one page.

[truesdell]

If there are less than 1000 products, there must be a lot of data in the product fields (description, moreinfo page info etc) for the upload to fail. During testing I often upload data for an entire store with 32000+ products and several thousand pages and it never times out.

Very much this.

I came across Product Descriptions and More Info Page Text that were being used as full-blown manifestos complete with large amounts of sketchy HTML, in-field delimiters and so on. Some Descriptions were bigger than 1K. Basically using Product fields like Page fields and worse.

Thank you Jim for pointing me in the right direction. Being able to play with a not-live database helped a lot.

And thanks for the pointers on the error messages. I have saved them so I can attack them one at a time.