Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Contribution - Excel Import 1.51


dynamok

Recommended Posts

Ok guys (speacially all those who couldn't wait)!

 

I just uploaded the module, here is the link:

http://www.oscommerce.com/downloads.php/co...ions,677/type,3

 

If someone is not sure what this is read the description or this topic:

http://www.oscommerce.com/forums/viewtopic.php...der=asc&start=0

 

Ok, I off to lunch. Let me know how it works out.

Link to comment
Share on other sites

  • Replies 86
  • Created
  • Last Reply

Top Posters In This Topic

This looks like a real nice useful mod. BUT :(

 

I'm not sure if I'm doing anything wrong, but I've installed it fine, put this into the xls sheet as a test:

 

Image,Product Code (model),Category Code,Ukrainian Product Name,Ukrainian Product Description,English Product Name,English Product Description,Product Price,Product Weight,DateAvailable,Root!,TaxClass,Number of Units

sun/workstations/u2-med.jpg,Ultra 2,23, , ,"Sun Ultra 2 : 2x300mhz CPU , 256mb Memory, 9.1gb Hard Drive ++","TEST

",550,18,2002-09-24 13:57:02,2,1,1

 

Which was saved from XL as cvs.

 

When I upload I get:

 

File uploaded. 

Temporary filename: /var/tmp/php2baWuW

User filename: catalog.csv

Size: 368

| sun/workst | | | | | | | | | | | | 



No products_model field in record or incorrect root for category. I cannot import this record! 



| ".550.18. | | | | | | | | | | | | 



No products_model field in record or incorrect root for category. I cannot import this record! 



Thats all!

 

I've tried everything but can't get it to upload, like root code 1/2 etc even taking out hdr record!.

 

Can someone help!?

 

John

Link to comment
Share on other sites

Image;Product Code (model);Category Code;Ukrainian Product Name;Ukrainian Product Description;English Product Name;English Product Description;Product Price;Product Weight;DateAvailable;Root!;TaxClass;Number of Units

sun/workstations/u2-med.jpg;Ultra 2;10;;;Sun Ultra 2 : 2x300mhz CPU , 256mb Memory, 9.1gb Hard Drive ++;TEST;550;18;;3;;

 

I just tried this and it worked fine. Maybe your not putting the right Root and Category Code.

The Root is the code of the main category, like for example here:

http://www.designspecialists.net/catalog/a...l.php?param=eng

 

| ...3 | DVD Movies

or

| ...1 | Hardware

is the ROOT. Then:

----| ..10 | Action

----| ..17 | CDROM Drives

are some Category Codes.

Test it on my test shop, see if it works.

Link to comment
Share on other sites

Here is my list :

 

MS Excel import

| ..34 | Adapters

| ..31 | CDROMs

| ..27 | CPUs

| ..30 | Disk Packs

| ..28 | Hard Drives

| ..33 | Input Devices

| ..35 | Leads

| ..26 | Memory

| ..32 | Monitors

| ..36 | Others

| ..24 | PCI Cards

| ..25 | SBUS Cards

| ...2 | Software

| ..29 | Tape Drives

| ..23 | Workstations

 

 

I'm putting into workstations, exactly what txt goes into root!?

 

I tried | ..23 | or Workstations or 1 or 2 etc etc.

 

John

Link to comment
Share on other sites

Hi! Sorry to bother... but maybe someone can help me ez-ly.

I run only with SPANISH (code 3), I have made changes in the code for language_id = 3 wherever it was '4' (I think for next release should be variable anyway... or better: get it from the DB).

 

Still I get into trouble, it says:

 

File uploaded. 

Temporary filename: /var/www/upload/phpqQ3KQP

User filename: Form.csv

Size: 316

| xxxxx.gif | 121212 | 16 | | | Test produ | Este es un | 1000 | 0 | 24-09-2002 | 16 | 1 | 10 !New!1110 - Column 'products_status' specified twice



INSERT INTO products (products_status, products_image, products_model, products_price, products_status, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity) VALUES (1, "xxxxx.gif", "121212", 1000, "1", CURRENT_TIMESTAMP, "24-09-2002 13:57", "1", "0", "10")

 

For what it?s worth, here?s my categories output:

 

| ..16 | Audio 

| ..20 | Computaci?n 

| ..21 | El?ctricos 

| ..17 | Herramientas 

| ..19 | Luces

 

Help appreciated!!!

 

And... thanks, Oleh!

 

(NOT TO BE TAKEN AS A CRITIC I AM AWARE IT WAS RELEASED IN A HURRY AND UNDER PRESSURE!! :P )

"Seek only to be your own master, everything else will come from it"

Link to comment
Share on other sites

I have tried it and it works fine. The only trouble is (was) that the INSERT statement when supplying DateAvailable is incorrect. It is

 

    $query = 'INSERT INTO products ( products_status,products_image, products_model, products_price, products_status, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity)

                           VALUES (1, "' . $v_products_image . '", "' . $v_products_model . '", ' . $v_products_price . ', "1", CURRENT_TIMESTAMP, "'.$v_date_avail.'", "'.$v_tax_class_id.'", "'.$v_products_weight.'", "'.$v_products_quantity.'")';

but should be

 

   $query = 'INSERT INTO products ( products_image, products_model, products_price, products_status, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity)

                           VALUES ( "' . $v_products_image . '", "' . $v_products_model . '", ' . $v_products_price . ', "1", CURRENT_TIMESTAMP, "'.$v_date_avail.'", "'.$v_tax_class_id.'", "'.$v_products_weight.'", "'.$v_products_quantity.'")';

 

That is; you have to remove one of the entries for products_status from field list and value list.

 

After changing this it works fine. (I haven't checked the database contents yet though :wink: )

Link to comment
Share on other sites

| ..23 | Workstations  

 

 

I'm putting into workstations, exactly what txt goes into root!?

 

I tried | ..23 | or  Workstations or 1 or 2  etc etc.

 

John

 

The problem is that you only have a root but not a category code. I'm not sure how to do this with out using category code, we'll see if we can figure this out.

How would I have for example:

| ...3 | DVD Movies

----| ..10 | Action

Where 3 is the root and 10 is the category code.

Will play with it over the weeked John see what happens.

Link to comment
Share on other sites

I have tried it and it works fine. The only trouble is (was) that the INSERT statement when supplying DateAvailable is incorrect. It is  

That is; you have to remove one of the entries for products_status from field list and value list.

 

After changing this it works fine. (I haven't checked the database contents yet though :wink: )

 

Missed that one, thank you rhedlund.

Link to comment
Share on other sites

Well, I have tried many times with uploading and yet it comes up with the same error "No products_model field in record or incorrect root for category. I cannot import this record! "

 

I tried it on my on site and I also tried it on the TEST site. Same errors. I changed the root to 1 and catalog code to 17 to try on the test site.

 

The file name I used is anime.csv

 

Can anyone please help?

Link to comment
Share on other sites

dynamok-

 

Am I right to assume that in order to eliminate the Ukraine part of the excel.php file and just use English I should delete the following?

 

Lines 220-222:

if ($v_products_name_u != '') {

$result = tep_db_query("INSERT INTO products_description (products_id, language_id, products_name, products_description)

VALUES ('" . $prod_id . "', '4', '" . $v_products_name_u . "', '". $v_products_description_u . "')");}

 

 

Lines 248-251:

if ($v_products_name_u != '') {

$result = tep_db_query('UPDATE products_description '.

'SET products_name="'.$v_products_name_u.'"'.

', products_description="'.$v_products_description_u.'" WHERE ((products_id='.$prod_id.') AND (language_id = 4))');}

 

 

Lines 285-290:

if ($v_products_name_u != '') {

$result = tep_db_query('UPDATE products_description '.

'SET products_name="'.$v_products_name_u.'"'.

', products_description="'.$v_products_description_u.'" WHERE ((products_id='.$prod_id.') AND (language_id = 4))');}

 

 

Line 302:

update_tree(0,4);

 

 

Thanks for all of your help,

 

-Chris

Chris Sullivan

Link to comment
Share on other sites

I am having the same troubles as Guyver.

 

File uploaded.

Temporary filename: /tmp/php8Dgq3R

User filename: Form.csv

Size: 346

| unavailabl | | | | | | | | | | | |

 

No products_model field in record or incorrect root for category. I cannot import this record!

 

Thats all!

 

This is the Root and category list

 

| ..21 | Stuff

----| ..23 | Category of Stuff

 

This is the CSV file after excel saves it:

 

Image,Product Code (model),Category Code,Ukrainian Product Name,Ukrainian Product Description,English Product Name,English Product Description,Product Price,Product Weight,DateAvailable,Root!,TaxClass,Number of Units

unavailable.gif,20022,23,"???????? 2,5??","???????? 2,5??",Potatoes 2.5 kg,Potatoes 2.5 kg,"1,11",0,2002-09-24 13:57:02,21,2,1

 

 

Also I tried copying and pasting the code from dynamok - On Sat - Nov 16 - 2002 9:17am:

 

Image;Product Code (model);Category Code;Ukrainian Product Name;Ukrainian Product Description;English Product Name;English Product Description;Product Price;Product Weight;DateAvailable;Root!;TaxClass;Number of Units

sun/workstations/u2-med.jpg;Ultra 2;10;;;Sun Ultra 2 : 2x300mhz CPU , 256mb Memory, 9.1gb Hard Drive ++;TEST;550;18;;3;;

 

Saving this as a CSV file then uploading it to the test site

 

http://www.designspecialists.net/catalog/a...l.php?param=eng

 

It gave me this error -

File uploaded.

Temporary filename: /tmp/phpjg8O4O

User filename: Form.csv

Size: 344

| sun/workst | Ultra 2 | 10 | | | Sun Ultra | TEST | 550 | 18 | | 3 | | Updated

| | | | | | | | | | | | |

 

So I think this may be a permissions problem or something along those lines......

 

 

Also - I would like to add the HTML Header Tag Contoller Functionality to this excel sheet - what are the points to edit?

Link to comment
Share on other sites

Hey all!

 

Just like everyone else, I was eagerly awaiting this contribution (Who wants to key in 2600 items?) And like most of us, it didn't work for me either.

 

However, I have found the problem. After several hours of digging throuh the php manuals and some trouble shooting, it turns out that the problem is in parsing the csv file. In lines 68 thru 74 of the excel.php file, an attempt is made to get rid of any extra punctuation. Actually, to be more precise, it tries to configure the entire file to be broken down into fields separated by a semicolon. Several things go wrong here though. If there happens to be any semi colons in your product description, guess what...it tries to break down your description into the remaining fields causing this error. Also, commas, quotes and single quotes wreak havoc on the parser. If you have commas in your descriptions it has the same effect as the semi colon problem. When saving the csv in excel 97, quotes are placed arround text fields (ie descriptions etc) which make formatting a bit of a problem as double - double quotes appear everywhere you may have had quotes (ie "This product" becomes ""This Product"".)

 

I worked arround the problem by converting to tab seperated values as apposed to comma. Does anyone know how to do that in excel? I used Access to export the data with no quotes, and not csv but rather tsv. After exporting the data to a text file, I used a word processor's find/replace to rid myself of semi colons. Commas in descriptions weren't a problem anymore because of the switch to tsv. Single quotes (ie ' ) were also a problem as the sql querries use them to separate fields so those had to go.

 

Bottom line, get rid of semicolons, commas, quotes, and apostrophe's (and be sure to put the order of the fields as they are in form.xls too *grin*) and it should work fine.

 

One final note, the import file size is limited to 100k. At the bottom of the excel.php, there is a variable MAX_FILE_SIZE that controls this. I was able to set it to 900000 and it seemed to finally accept my large text file. Unfortunately, it only imported about 500 records and stopped with no errors or any clues as to why.

 

Hope this sheds some light on the situation. MUCH thanks goes to Oleh for getting us this far!

 

/Regards

Scott

There are 10 kinds of people in the world, those who understand binary - and those who do not.

Link to comment
Share on other sites

Regarding Ashaman?s problem:

 

I think it is the decimal format you are using on Excel. It should be because you are using comma instead of point for decimals, or the other way around. Give it a try!

"Seek only to be your own master, everything else will come from it"

Link to comment
Share on other sites

I think (pretty sure) that if you just copy-paste the excel spreadsheet completely into a text editor it does what you want pretty easily, and he we can work around the problem with some code modification.

 

It would be -even- easier to do this if, instead of uploading a file, we would paste it in a textbox direct from excel!... What do you think Oleh?

 

Keep up th good work.

"Seek only to be your own master, everything else will come from it"

Link to comment
Share on other sites

:lol:

in excel I did an extended search and replace of all extra punctuation (commas, quotes, etc) I saved the 925 product entries as a CSV file, then reopened it in my html editor, once there I replaced all the commas with semi-colons and imported all 925 with no errors.

 

I ran out of coffee and hit the floor.

Link to comment
Share on other sites

I'm kind of busy right now to answer all the questions, but I will get to them.

But the basic idea is that you values have to be separeted by

;

you can leave all the commas and everything else in description but what seperates everything is

;

Link to comment
Share on other sites

hi,

i have tested and everything worked ok, when replacing produts.

after that i tryed to add 4 new producs, here's the files:

excel format - http://www.bgtgsm.com/temp/formulario.xls

converted to cvs - http://www.bgtgsm.com/temp/formulario.cvs

 

and i get this erro:

MS Excel import

 

File uploaded.

Temporary filename: /storage/webhosting/w1000352/tmp/phpT5JzkQ

User filename: formulario.csv

Size: 528

| unavailabl | WBBASIC1 | 22 | a | xxx | a | xxx | 540 | 1 | 2002-09-24 | 21 | 2 | 100 | !New!1110 - Column 'products_status' specified twice

 

INSERT INTO products (products_status, products_image, products_model, products_price, products_status, products_date_added, products_date_available, products_tax_class_id, products_weight, products_quantity) VALUES (1, "unavailable.gif", "WBBASIC1", 540, "1", CURRENT_TIMESTAMP, "2002-09-24 13:57:02", "2", "1", "100")

 

[TEP STOP]

 

 

product status specified twice ?? anyone see's the problem ? maybe it's something simple i'm missing...

 

catalog http://www.bgtgsm.com/catalog

excel.php http://www.bgtgsm.com/admin/excel.php

 

thx

Link to comment
Share on other sites

Read the post by rhedlund on the first page of this topic he tells you where to remove the 'products_status' so it doesn't get specified twice.

Also I would try no to enter the date in, it seems to cause some problems, let it automatically assign todays date.

Link to comment
Share on other sites

I downloaded this contribution on sunday. With the help of the previous posts I have been able to upload some of my products. The problem Im having is a 30 second time out limit. I get the fatal error message of this 30 second called from the database.php file on line 45. Im running this on localhost at this time

 

Now I know didly squat about php so can someone suggest how i can change the timeout error.

 

This may be of use to someone. This is how i got the excell import to work for me.

1. Set up all your colums as in from.xls

2. Import all your data into the appropriate colums.

3. Do a search for , and replace with a (space)or (no sapce)

3. Do a search for (double space) and replace with (space)

4. Make sure all your catagorgories and root codes are correct

5. Save in CSV format

6. Open the saved file in Word pad

7. In word pad do a replace , with ;

8. Should be ready to up load

 

This is how I got it to work for me there are proabably better ways out there.

 

Cheers

Night and day the only 2 shore things in life

Link to comment
Share on other sites

             1.    Set up all your colums as in from.xls

             2.    Import all your data into the appropriate colums.

             3.    Do a search for , and replace with a (space)or (no sapce)

             3.    Do a search for (double space) and replace with (space)

             4.    Make sure all your catagorgories and root codes are correct

             5.    Save in CSV format

             6.    Open the saved file in Word pad

             7.    In word pad do a replace , with ;

             8.    Should be ready to up load

Cheers

1 & 2. That's right.

3. Don't know why you are doing this you can have comas in there.

4 & 5. Absolutely!

6. That's how I usually open it to.

7. This is weird, maybe someone knows the answer, but when I save my excel file it automatically saves it with ; and not with , I'm using office XP.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...