Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

EasyPopulate 1.0 - Populate your store from Excel / csv


wasson65

Recommended Posts

I have my own server, and my php.ini is set at 120sec

and 8m However, my file is less than 4Mb. and the process time that it uses when I try to upload the file is actually less than if I were to upload a 300 product file. its like the server says ok upload the file,,, then sees what it is and says never mind thats crazy. --snip--

 

 

For those having trouble with big file uploads and splits not working, it's very possible that PHP is running out of memory. The 8 meg memory limit on php can be easily overrun with 4 meg of data, because of data structure organization and overhead.

 

So the silent dying with no error messages are likely memory problems.

 

I've got an idea for a fix, will try it and let everyone know...

 

 

Tim

Link to comment
Share on other sites

  • Replies 362
  • Created
  • Last Reply

Top Posters In This Topic

OK, folks, we're getting close, I mean really, really close now....

 

I fixed the splitting problem, and also tried to make the code smarter about how much memory it ties up trying to read the large file to be split. So some of you with the "no messages, no errors, no confirmations problems" when splitting may be helped by this release.

 

Keep in mind that this cannot overcome the max upload size that your hoster has set for their servers.

 

See how this works for you...

 

Tim

Link to comment
Share on other sites

Can everyone who's using EP with excel please help write a step by step for the way they are using Excel?

 

Simon has put in some time on this, but it seems like way more work than I think most of you are doing.

 

I don't think this for v2.1.

 

If you can all post your step-by-steps, I'll compile them and hopefully we'll get a definitive document about what the process ought to be.

 

Then I'll include that in the docs.

 

Simon's Process:

Instructions for the round the world method of importing an excel file into oscommerce Easy Populate by Tim Wasson. Its a little time consuming but once you know how to do it it takes about 5 mins max.

 

Written by Simon Young [email protected]

 

1. Download existing csv file or use base.csv attached

2. Open Excel

3. Create new workbook.

4. Use "Data" menu, choose "get external data" then "import text file"

5. Choose the base.csv or existing csv file you downloaded

6. Click next - make sure to set the text qualifier to "none" and the delimiters to "tab"

7. Data will appear all data will have quotes around it.

8. Amend and append data not forgetting to keep the " quotes around it.

9. When finished adding products or amending choose file "save as"

10. Save file as *.txt (text tab delimited)

11. Close Excel

12. Open the file *.txt using Mircrosoft Word

13. There will be """ (three quotes) around all the data

14. Use the "edit" "replace" command in Word - replace """ with "

15. You should now have all data surrounded by single " and separated by tabs

16. Choose file save as and name the file *.txt, replace existing file

17. Open this text file in Notepad and rename it *.csv or just edit the file name adding the csv instead of the txt extension.

 

The file you now have is in a format MySQL can accept through Easypopulate.

 

Thanks, the docs are the final frontier. If we get them up to snuff, it will save alot of posts and discussions.

 

Thanks for everyone's help and support.

 

Tim

Link to comment
Share on other sites

Uh, simple

 

1.download the txt tab delimited file,

2. right click the file and choose open with, excel

3. edit products

4.save as text delimited file

upload file.

 

windows 2000 has open with on right click

in windows 98 I think its alt+right click

Link to comment
Share on other sites

When I donwload the .csv file to edit in Excel, it is working, but I do have a problem.

 

Does anyone know why, once the file is opened in Excel, it shows up in straightline code instead of being seperated in individual cells? Is there a way to fix this?

 

 

Thanks for the help, and the great module.

 

-Chris

Chris Sullivan

Link to comment
Share on other sites

Warning: Unable to create '/home/blueline/bluelinehifi-www/catalog/temp/test.csv': No such file or directory in /home/blueline/bluelinehifi-www/catalog/admin/includes/functions/general.php on line 783

 

Warning: Unable to move '/tmp/phpmqV0dz' to '/home/blueline/bluelinehifi-www/catalog/temp/test.csv' in /home/blueline/bluelinehifi-www/catalog/admin/includes/functions/general.php on line 783

 

 

File uploaded.  

Temporary filename: /tmp/phpmqV0dz

User filename: test.csv

Size: 2047

 

Warning: file("/home/blueline/bluelinehifi-www/catalog/temp/test.csv") - No such file or directory in /home/blueline/bluelinehifi-www/catalog/admin/easypopulate.php on line 727

 

Warning: Invalid argument supplied for foreach() in /home/blueline/bluelinehifi-www/catalog/admin/easypopulate.php on line 732

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

 

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

 

 

Thats all!

 

Please help, I have no idea what is wrong. I created a temp folder, 777 chmod, and this doesn't work.

 

Warning: OpenDir: Permission denied (errno 13) in /home/blueline/bluelinehifi-www/catalog/admin/file_manager.php on line 187

 

Fatal error: Call to a member function on a non-object in /home/blueline/bluelinehifi-www/catalog/admin/file_manager.php on line 188

 

File Manager

/home/blueline/bluelinehifi-www

 

For some reason I can't get into my File Manager either. Someone please help.....

Chris Sullivan

Link to comment
Share on other sites

Hello!

 

At first: my english is not perfect. so please excuse if there are some errors.....

 

I got the V2.1 to work. but if i import a test-file there comes this stupid error.....

 

File uploaded.

Temporary filename: C:PHPuploadtempphp29D.tmp

User filename: test.txt

Size: 17422

| matrox/mg2 | MG200MMS | Matrox G20 | Reinforcin | 29.900 | 23.00 | 0000-00-00 | 32 | Matrox | Graphics C | Hardware | | | | | | Updated

| matrox/mg4 | MG400-32MB | Matrox G40 | Dramati | 49.900 | 23.00 | 0000-00-00 | 32 | Matrox | Graphics C | Hardware | | | | | | Updated

| microsoft/ | MSIMPRO | Microsoft | Every elem | 4.999.000 | 7.00 | 0000-00-00 | 2 | Warner | Mice | Hardware | | | | | | Updated

| dvd/replac | DVD-RPMK | The Replac | Regional C | 4.200.000 | 23.00 | 0000-00-00 | 13 | Microsoft | Action | DVD Movies | | | | | | Updated

| dvd/blade_ | DVD-BLDRND | Blade Runn | Regional C | 3.599.000 | 7.00 | 0000-00-00 | 17 | Warner | Science Fi | DVD Movies | | | | | | Updated

| dvd/the_ma | DVD-MATR | The Matrix | Regional C | 3.999.000 | 7.00 | 0000-00-00 | 10 | Warner | Action | DVD Movies | | | | | | Updated

| dvd/youve_ | DVD-YGEM | You've | Regional C | 3.499.000 | 7.00 | 0000-00-00 | 10 | Warner | Comedy | DVD Movies | | | | | | Updated

| dvd/a_bugs | DVD-ABUG | A Bug's | Regional C | 3.599.000 | 7.00 | 0000-00-00 | 10 | Warner | Cartoons | DVD Movies | | | | | | Updated

| dvd/under_ | DVD-UNSG | Under Sieg | Regional C | 2.999.000 | 7.00 | 0000-00-00 | 10 | Warner | Action | DVD Movies | | | | | | Updated

| dvd/under_ | DVD-UNSG2 | Under Sieg | Regional C | 2.999.000 | 7.00 | 0000-00-00 | 10 | Warner | Action | DVD Movies | | | | | | Updated

| dvd/fire_d | DVD-FDBL | Fire Down | Regional C | 2.999.000 | 7.00 | 0000-00-00 | 10 | Warner | Action | DVD Movies | | | | | | Updated

| dvd/die_ha | DVD-DHWV | Die Hard W | Regional C | 3.999.000 | 7.00 | 0000-00-00 | 10 | Fox | Action | DVD Movies | | | | | | Updated

| dvd/lethal | DVD-LTWP | Lethal Wea | Regional C | 3.499.000 | 7.00 | 0000-00-00 | 10 | Warner | Action | DVD Movies | | | | | | Updated

| dvd/red_co | DVD-REDC | Red Corner | Regional C | 3.200.000 | 7.00 | 0000-00-00 | 10 | Warner | Drama | DVD Movies | | | | | | Updated

| dvd/franti | DVD-FRAN | Frantic | Regional C | 3.500.000 | 7.00 | 0000-00-00 | 10 | Warner | Thriller | DVD Movies | | | | | | Updated

| dvd/courag | DVD-CUFI | Courage Un | Regional C | 3.899.000 | 7.00 | 0000-00-00 | 10 | Fox | Drama | DVD Movies | | | | | | Updated

| dvd/speed. | DVD-SPEED | Speed | Regional C | 3.999.000 | 7.00 | 0000-00-00 | 10 | Fox | Action | DVD Movies | | | | | | Updated

| dvd/speed_ | DVD-SPEED2 | Speed 2: C | Regional C | 4.200.000 | 7.00 | 0000-00-00 | 10 | Fox | Action | DVD Movies | | | | | | Updated

| dvd/theres | DVD-TSAB | There's | Regional C | 4.999.000 | 7.00 | 0000-00-00 | 10 | Fox | Comedy | DVD Movies | | | | | | Updated

| dvd/belove | DVD-BELOVE | Beloved | Regional C | 5.499.000 | 7.00 | 0000-00-00 | 10 | Warner | Drama | DVD Movies | | | | | | Updated

| sierra/swa | PC-SWAT3 | SWAT 3: Cl | Windows | 7.999.000 | 7.00 | 0000-00-00 | 16 | Sierra | Simulation | Software | | | | | | Updated

| gt_interac | PC-UNTM | Unreal Tou | From the c | 8.999.000 | 7.00 | 0000-00-00 | 13 | GT Interac | Action | Software | | | | | | Updated

| gt_interac | PC-TWOF | The Wheel | The world | 9.999.000 | 10.00 | 0000-00-00 | 16 | GT Interac | Strategy | Software | | | | | | Updated

| gt_interac | PC-DISC | Disciples: | A new age | 9.000.000 | 8.00 | 0000-00-00 | 17 | GT Interac | Strategy | Software | | | | | | Updated

| microsoft/ | MSINTKB | Microsoft | The Intern | 6.999.000 | 8.00 | 0000-00-00 | 16 | Microsoft | Keyboards | Hardware | | | | | | Updated

| microsoft/ | MSIMEXP | Microsoft | Microsoft | 6.495.000 | 8.00 | 0000-00-00 | 10 | Microsoft | Mice | Hardware | | | | | | Updated

| hewlett_pa | HPLJ1100XI | Hewlett Pa | HP has alw | 49.900 | 45.00 | 0000-00-00 | 8 | Hewlett Pa | Printers | Hardware | | | | | | Updated

| 2228.gif | EHEIM2228 | Aussenfilt | Dies ist d | 1.090.000 | 15.00 | 0000-00-00 | 10 | EHEIM | Pumpen | Filtertech | Technik | | | | | Updated

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

 

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

 

upload works fine, but it does no import...

i tried in the filemanager and upload/download worked fine. in the file, there is no emty last line. i checked in excel and rechecked in word/editor/textpad. a empty line ore tabs can not be found!

 

and i have another question: the default language of the shop should be german. may i get any problem using this enormous helpfully contribute?

 

please tell me, if i do something wrong.....

 

THANKS

ThePapabear

 

PS: the shop is V.2.2 CSV from 20021027. Running on WinXP pro with IIS5.1, php4.2.3 and mySQL 3.23.52. All working fine, no other adds on the shop

live is what happens while we are still planning....

Link to comment
Share on other sites

Blueline, save the csv file and open it agian, also check the delimiter in the global settings in windows control panel.

 

Also the other error, I had the same, I created a temp folder and chmod 777 and still got the same, I had to actually go into graphical, in my linux redhat box and right click on the folder and go to permitions, for some reason a chmod 777 did not turn everything on, I checked all of the boxes under the permisions and then it worked.

So I would have to say that it is still with your permisions.

 

Papa Bear,

I get the same problem with every file I upload but it just shows the error, and the products are still uploaded fine.

Link to comment
Share on other sites

Hi, Kirk6677!

 

Not so at my side! When i look in the admin, there is no new product/category. When i look in the shop, there is a new product in "new product", klicking on the product brings a site that say?s: "no product found".

 

When i look in the database, the product is there in products, but nothing else. Nothing in "product to category.." ore somewhere else....

 

Should i emty the whole database and try to do all completely new? CHMOD-Problem isn?t available because it?s running local on WinXP....

....so i got no more ideas, because i am totally new in MySQL and PHP.

 

Thanks a lot for helping!

ThePapabear

live is what happens while we are still planning....

Link to comment
Share on other sites

Hi, Kirk6677!

 

Not so at my side! When i look in the admin, there is no new product/category. When i look in the shop, there is a new product in "new product", klicking on the product brings a site that say?s: "no product found".

 

When i look in the database, the product is there in products, but nothing else. Nothing in "product to category.." ore somewhere else....

 

Should i emty the whole database and try to do all completely new? CHMOD-Problem isn?t available because it?s running local on WinXP....

....so i got no more ideas, because i am totally new in MySQL and PHP.

 

Thanks a lot for helping!

ThePapabear

 

I suspect the db insert is failing quietly. Please send me your file you're trying to import, I'll see if something is wrong with it.

 

 

Tim

Link to comment
Share on other sites

Hi, Tim!

 

Thanks for your offer to send you the file!

But it isn?t necessary any more! I found the problem:

 

you?ve got language_id 1 and 4 in the easypopulate.php.

german has 2 and i?ve disabled english and spanish because my users will not need....

so i changed the id for german to "1" in the database and it works fine! :o

changing the *.php i wouldn?t try, because i?m afraid of damaging the file. maybe you could add the language id?s 2 and 3(i think this is for spanish) and it will work for german and spanish users as well as for others....

 

thanks a lot!

Herbert

live is what happens while we are still planning....

Link to comment
Share on other sites

me, again...

 

i can?t sleep tonight. so i am sitting in front of my computer and try to work on the shop.....

 

i got some questions/suggestions

 

1) is it possible to get another table in , which is to be filled with the language_id? so it will be able to use any language a user is working with.

 

2) ist it possible to get in the tax_class and _id too? For me in Austria it is necessary to have the taxes on the bill. Without it i?ll have really troubles with the tax office....

 

if this works, it would be wonderful! No additional work on produkts after upload....

 

Thanks for your really great work! This is the first tool to upload products, which works for me!

 

regards

Herbert

live is what happens while we are still planning....

Link to comment
Share on other sites

me, again...

 

i can?t sleep tonight. so i am sitting in front of my computer and try to work on the shop.....

 

i got some questions/suggestions

 

1) is it possible to get another table in , which is to be filled with the language_id? so it will be able to use any language a user is working with.

 

2) ist it possible to get in the tax_class and _id too? For me in Austria it is necessary to have the taxes on the bill. Without it i?ll have really troubles with the tax office....

 

if this works, it would be wonderful! No additional work on produkts after upload....

 

Thanks for your really great work! This is the first tool to upload products, which works for me!

 

regards

Herbert

 

I have a beta version of EP with multi-language/non-english support in. I need someone to test it and see if it makes sense.

 

If you need multi-lang support and can test this, please d/l it at

http://www.wassons.org/EasyPopulate_v2.2beta.zip

 

and test it out.

 

It will let you set the default language (all categories are assumed to be in the default lang), and handle up to 3 different languages for the product name and descriptions.

 

To set these things, read easypopulate.php. It has comments that explain what to do.

 

Please email me at [email protected] to let me know how it goes

 

Thanks,

Tim

Link to comment
Share on other sites

me, again...

-- snip --

2) ist it possible to get in the tax_class and _id too? For me in Austria it is necessary to have the taxes on the bill. Without it i?ll have really troubles with the tax office....

-- snip --

regards

Herbert

 

The tax_class_id is in there, but normally it's not exposed. Inside easypopulate you'll see tax_class_id in the $filelayout array, just remove the hash mark (uncomment it) and it will appear in your data.

 

Should probably test to be sure that it makes it back in properly, just to be safe.

 

Tim

Link to comment
Share on other sites

Simon's Process:

 

Instructions for the round the world method of importing an excel file into oscommerce Easy Populate by Tim Wasson. Its a little time consuming but once you know how to do it it takes about 5 mins max.

 

Written by Simon Young [email protected]

 

1. Download existing csv file or use base.csv attached

2. Open Excel

3. Create new workbook.

4. Use "Data" menu, choose "get external data" then "import text file"

5. Choose the base.csv or existing csv file you downloaded

6. Click next - make sure to set the text qualifier to "none" and the delimiters to "tab"

7. Data will appear all data will have quotes around it.

8. Amend and append data not forgetting to keep the " quotes around it.

9. When finished adding products or amending choose file "save as"

10. Save file as *.txt (text tab delimited)

11. Close Excel

12. Open the file *.txt using Mircrosoft Word

13. There will be """ (three quotes) around all the data

14. Use the "edit" "replace" command in Word - replace """ with "

15. You should now have all data surrounded by single " and separated by tabs

16. Choose file save as and name the file *.txt, replace existing file

17. Open this text file in Notepad and rename it *.csv or just edit the file name adding the csv instead of the txt extension.

 

The file you now have is in a format MySQL can accept through Easypopulate.

 

What the heck??... steps 3 through 8 are comletely unneccessary. by elimating steps 3 to 8, there is no need to do steps 12 to 17...

 

you just simply need to download the tab delimited file from easypopulate... it will save as a *.txt (the new versions do or should anyways... if it doesn't then just rename it to .txt)... either right click and choose "open with -> Excel" or open excell and then open the file. Then input the data and save as Text (tab delimited) and import with easy populate... really that simple... absolutely no need to complicate the process by doing all the extra garbage in the above 17 step guide...

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

Hi Tim

I have change Ep to run my own modded field and it run really very very nicelly... thanks for that :)

 

except language file so i will give a look at the multilanguage

 

but i think it become to make many field to change and will not be easy to update so large files

 

maybe you should split easy pop in two part :

1/EasyPop Financial that would work on price, qty,tax,weight... and default language description (should be enough for most mono language shop)

2/EasyPop Language that woul only carry the description for language dependant field for at least 4 language (the 3 standards one and the fourth laguage localized added)

Link to comment
Share on other sites

the way language is managed may cause some problem

 

it seems you Need that language_id 1 is standard language

but if somebody removed english he will have no language at all

you should better use value DEFAULT_LANGUAGE that is defined in language.php, stored in configuration tables and avalaible for that purpose.

 

& you should use instead function function tep_get_languages()

to gat all avalaible language and export them

function tep_get_languages() {

$languages_query = tep_db_query("select languages_id, name, code, image, directory from " . TABLE_LANGUAGES . " order by sort_order");

while ($languages = tep_db_fetch_array($languages_query)) {

$languages_array[] = array('id' => $languages['languages_id'],

'name' => $languages['name'],

'code' => $languages['code'],

'image' => $languages['image'],

'directory' => $languages['directory']

);

}

 

return $languages_array;

}

 

 

Link to comment
Share on other sites

Well, it looks like im partially successfull, with the exception of this error:

 

| G1-25 | Amaranth | | 1.99 | 10 | 8/23/2002 | 0 | | Bulk | Grains | Long Term | | | | | | Updated

| G2-25 | Alfalfa | | 2 | 20 | 8/23/2002 | 0 | | Bulk | Grains | Long Term | | | | | | Updated

ERROR! - Too many characters in the model number.

12 is the maximum on a standard OSC install.

Your maximum product_model length is set to 12

You can either shorten your model numbers or increase the size of the field in the database.

 

As you can see, my max prod_length is limited to 12 - however i'm only using 5 characters! Any suggestions ??

 

~Jason

Link to comment
Share on other sites

Well, it looks like im partially successfull, with the exception of this error:

 

| G1-25 | Amaranth | | 1.99 | 10 | 8/23/2002 | 0 | | Bulk | Grains | Long Term | | | | | | Updated

| G2-25 | Alfalfa | | 2 | 20 | 8/23/2002 | 0 | | Bulk | Grains | Long Term | | | | | | Updated

ERROR! - Too many characters in the model number.

12 is the maximum on a standard OSC install.

Your maximum product_model length is set to 12

You can either shorten your model numbers or increase the size of the field in the database.

 

As you can see, my max prod_length is limited to 12 - however i'm only using 5 characters! Any suggestions ??

 

~Jason

 

Hmm, can you send me the file and I'll try importing it on my test store.

 

Thanks,

Tim

Link to comment
Share on other sites

Hi Tim

I have change Ep to run my own modded field and it run really very very nicelly... thanks for that :)

 

except language file so i will give a look at the multilanguage

 

but i think it become to make many field to change and will not be easy to update so large files

 

maybe you should split easy pop in two part :

1/EasyPop Financial that would work on price, qty,tax,weight... and default language description (should be enough for most mono language shop)

2/EasyPop Language that woul only carry the description for language dependant field for at least 4 language (the 3 standards one and the fourth laguage localized added)

 

I've got a doctored up version of EP that can support multiple languages. If you'd like to download and test it can be had at

http://www.wassons.org/EasyPopulate_v2.2beta.zip

 

It lets you decide which languages you want to have names and descriptions for, and also will let you determine which language the category names are in.

 

The idea about splitting EP files into "finanicial" and "language" ones is interesting. I've been trying to think about how to handle attributes, and this might all get solved in the same manner - a name/description file, a price/weight file, an attributes file, etc.

 

Let me know what you think...

Tim

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • Create New...