EasyPopulate 1.0 - Populate your store from Excel / csv

[Guest]

I will continue to test with different data. Looks like if any operators like, " or @ or , seems to confuse the file.

 

Will keep you guys posted

[dreamscape 0]

wasson,

 

thanks for this kickin contribution man...

 

one question... I am new to PHP and know that something about " and ' mess it up... and in a few of my discriptions I have quotes " and apostrophes ', and it has trouble importing them, giving me a syntax error...

 

so in the product descriptions, exactly how do I allow for quotes and apostrophes?

[Guest]

Ok I am getting closer.

 

What the hell does this mean?

 

 

File uploaded.

Temporary filename: /tmp/phpFH1EKa

User filename: EP1038061349.csv

Size: 1135

| 020126.jpg | test123 | test | test | 14.95 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | test1234 | test1 | test | 9.95 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | test321 | test2 | test | 12.05 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | test4321 | test3 | test | 12.05 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | 123 | test4 | test | 29.95 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | 1234 | test5 | test | 29.95 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | 12345 | test6 | test | 29.95 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | 123456 | test7 | test | 39.5 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

| 020126.jpg | 1234567 | test8 | test | 39.5 | 0 | 10/17/2002 | 1 | InkJet | Epson | Epson | !New!

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

ERROR - I didn't find enough fields!

There should be 11 fields and I found 7 fields.

Are you using the right separation character in your file?

Does the number of fields in easypopulate.php match the number in your file?

 

 

 

Thanks in advance, Kirk

[emanresu 0]

Had a similar problem using excel and viewed the CSV file using a hex viewer. Found that I'd added blank rows/colums in Excel which were'nt visable.

 

Try going back into Excel and deleting adjacent "blank" rows/colums to see if that clears it.

[Guest]

never mind, I found that something is picky I guess,

 

I just changed the products model numbers to test1 test2 and so on I had some that were 123 and 1234 and that caused the problem I guess.

 

On a roll guys!

 

soon I might be smart enough to turn in my own contrib

[networkdad 0]

Well, im getting close. A few things:

 

-When trying to create the csv file in the /catalog/temp dir - dir didnt exist. I created one, set permissions temporarily to 777, and bang! It worked.

 

-Now when uploading the csv file, rather than the 'root' numbers of each category, it appears to want the actual name.

 

-Semicolons work best (at least in my case): WindowsXP users, Start-settings-control panel-Regional and Lanugage Options-Customize-list separator ;

 

-When adding a new product, it went to the correct category

 

-When updating an existing product, it changed it correctly, but also added a duplicate to the root of the catalog!

 

-Still having trouble with descriptions...going to try soon.

 

Thanks!

Jason

[dreamscape 0]

hey also, any commas in any of the fields are being changed to periods on import. This really isn' that big of a deal for most fields, but in the product despcription, it sure can make for one heck of a mess when all the commas are now periods. anyway around this? I know when you add products one at a time in the admin, it keeps the commas and allows for quotes and apostrophes (it atomatically parses them to the correct code to avoid errors), so surely there would be a way to add this to the import function... though I don't know how since I am not really that big on PHP.

[dreamscape 0]

hey also, any commas in any of the fields are being changed to periods on import. This really isn' that big of a deal for most fields, but in the product despcription, it sure can make for one heck of a mess when all the commas are now periods. anyway around this? I know when you add products one at a time in the admin, it keeps the commas and allows for quotes and apostrophes (it atomatically parses them to the correct code to avoid errors), so surely there would be a way to add this to the import function... though I don't know how since I am not really that big on PHP.

 

I figured it out... I opened up easypopulate.php and some nut job (j/k) put code in there to change all commas to periods (commented that out), and to replace apostrophes with quotes (fixed that)...

[wasson65 0]

Wow. I feel like I've created a monster!

 

The descriptions seems to be the biggest issue involving special characters.

 

There are a couple ways around this, one of which is to switch to some other type of delimiter, lke a tab. Excel can do Tab delimited files. This is one possibility.

 

As far as special chars like comma, quote, doublequote, @, this should be addressable by having the code "escape" the special chars before it tries to stuff them into the db. There is still the difficulty of excel wanting to put double quotes around "text" fields, and knowing which are excel's quotes, and which are quotes that you want in a description.

 

As far as carriage returns in the descriptions, this is a more difficult problem. One solution is to have the code keep reading until it finds enough fields, assuming that any carriage returns found before the correct number of fields has been found should not be considered "real" carriage returns, but rather part of the field data.

 

This brings up the problem of really screwing up data if there really aren't enough fields of data. So I am thinking that perhaps an explicit "end of row" field that contains a defined "end of row" bit of data, something like "--EOR--", something unlikely to ever be used as data in a field.

 

Finally, there is a desire from more than one person to do more or less than simply "category" and "subcategory". Some want no categories, some want 3 and more layers.

 

Somebody mentioned a bug where updating products resulted in the product being listed in the root of the catalog - this is a real bug and I'm going to take care of that one in 1.4

 

Also in 1.4 will be a fix for those who have hosts running in safe_mode, I have code right now that Gerry has tested for the safe mode problems and it seems to work.

 

So I'll try my best to have a 1.4 out that addresses these two problems and announce a strategy to deal with the other issues that you all have brought up.

 

Please hang with me, I'm trying to get shopping carts set up for 2 clients right now and do this as well. It's almost overwhelming to see this many people using it and finding all these issues, which are real, valid points that need to be addressed. I do want to address them, and hope you'll give me some slack over the next several days in getting this straightened out.

 

Tim

[Guest]

Ok I found something new.

 

My list contains 15,883 products, When I try to upload it sends the file and then nothing happens, Now,,, When I take the first few hundred products and copy them into a new excel file it will upload those perfectly fine and everything works.

 

Is there some sort of file size limitation?

 

Is there a way for me to split the file up and send in numerous smalled files?

 

I think that if we can figure out what the probs are that this contrib would be well worth the time put into it by both the writer and us.

[wasson65 0]

hey also, any commas in any of the fields are being changed to periods on import. This really isn' that big of a deal for most fields, but in the product despcription, it sure can make for one heck of a mess when all the commas are now periods. anyway around this? I know when you add products one at a time in the admin, it keeps the commas and allows for quotes and apostrophes (it atomatically parses them to the correct code to avoid errors), so surely there would be a way to add this to the import function... though I don't know how since I am not really that big on PHP.

 

I figured it out... I opened up easypopulate.php and some nut job (j/k) put code in there to change all commas to periods (commented that out), and to replace apostrophes with quotes (fixed that)...

 

As the nut-job in question ;-) I'll take this fix and check it to be sure that the database isn't going to get confused. Unescaped chars like single/double quotes can trip up the db.

 

You guys don't miss nothin'!

 

Hehehe,

 

Tim

[wasson65 0]

Ok I found something new.

 

My list contains 15,883 products, When I try to upload it sends the file and then nothing happens, Now,,, When I take the first few hundred products and copy them into a new excel file it will upload those perfectly fine and everything works.

 

Is there some sort of file size limitation?

 

Is there a way for me to split the file up and send in numerous smalled files?

 

I think that if we can figure out what the probs are that this contrib would be well worth the time put into it by both the writer and us.

 

 

AAACK! Help! I'm going under!!! glub glub glub....

 

Yeah, I'd expect the 15k+ products is going to cause a problem. First, most php installs have a 30 second time limit on script run length. This is there to prevent runaway loops from consuming the machine forever.

 

So the behavior you're seeing makes sense considering the scope of what you are trying to do.

 

So breaking the file up into smaller parts would make sense.

 

Another idea I had was to make the download files by top level category, so you could get several downloaded csv files and edit them independently.

[Guest]

Wasson65,,

 

Man I think this thing really rocks, keep it comming at your convinience and I have to give My thanks to you and people like you.

 

Do you think that changing this thing to tab delimited would help?

I know that the default for mysqlimport uses tab delimited txt files.

 

also curios if you know if there is any kind of files size limitation.

 

 

Kirk

[Guest]

oh ok, cool on the 30 second deal,

what would happen if I imported a csv file containing 300 products and then turned around and impoted another csv file with another 300 files, would the second overwrite the first? I suppose it would.

[dreamscape 0]

hey also, any commas in any of the fields are being changed to periods on import. This really isn' that big of a deal for most fields, but in the product despcription, it sure can make for one heck of a mess when all the commas are now periods. anyway around this? I know when you add products one at a time in the admin, it keeps the commas and allows for quotes and apostrophes (it atomatically parses them to the correct code to avoid errors), so surely there would be a way to add this to the import function... though I don't know how since I am not really that big on PHP.

 

I figured it out... I opened up easypopulate.php and some nut job (j/k) put code in there to change all commas to periods (commented that out), and to replace apostrophes with quotes (fixed that)...

 

As the nut-job in question ;-) I'll take this fix and check it to be sure that the database isn't going to get confused. Unescaped chars like single/double quotes can trip up the db.

 

You guys don't miss nothin'!

 

Hehehe,

 

Tim

 

I am not having troubles with keeping commas and apostrophes... but the quotes are whats getting me...

 

say the description is something like "The book "A Hobbit's Tale" is a wonderful masterpiece."

 

excel will double quote the quotes making it "The book ""A Hobbit's Tale""...

 

and the remove double quotes code:

$item1 = str_replace('""','"',$item1);

 

does seem to be removing them because on import it comes in ""The book ""A Hobbit's Tale""...

 

it handles everthing fine (apostrophes and commas) except quotes where there are still double quotes and it puts double before each quote instead of just one... (i.e "A Hobbit's Tale")

 

for now leaving quotes out of descriptions isn't that big of a deal... I've got my commas and apostrophes back, so all is well for now. (though you can't have apostrophes in the title, they show up on the website with the ' there... but it does the same if you use the admin panel to add them in by hand, so I suspect it is a problem with OSC)

[dreamscape 0]

* that should be doesn't seem to be removing them

[wasson65 0]

oh ok, cool on the 30 second deal,

what would happen if I imported a csv file containing 300 products and then turned around and impoted another csv file with another 300 files, would the second overwrite the first? I suppose it would.

 

No, it doesn't wipe the database and refill it. What it does is check to see if it's an existing item, if it is, it updates the quantity, price, etc.

 

If it is not an existing item, it creates it as a new item.

 

So, if you upload 300 products, then upload another 300 products with different product_model codes, then you will have 600 products.

 

If you upload 300 products, then upload another 300 products which have the same product_model codes as the first batch, then you'll have 300 products.

 

If you have 300 products in the system, and you upload an empty file, you'll still have 300 products.

 

Make sense?

[dreamscape 0]

I see whats going on with the quotes now... if there is a quote in a text field, excell will quote the entire text field and the double quote the quote within the field (does that make any sense??? lol)

 

I opened my CSV file in notepad and removed the begining and ending quotes on my description field and leaving the double quotes around the actual quote... went to easypopulate and dam it went through...

 

maybe there is a way to check for a quote as the 1st and last character of a field (well 2nd to last I guess cause of semi-colons) and if it is there, chop them off...

[Guest]

Well I guess you solved the problem that I have been debating for the last 3 weeks, I do not have a problem cuting up a excel file and making about 50 new excel files.

May take a full day to do but I figure that it would take about 8 months to ad 15,883 manually.

 

This is great!

 

Thanks for the help, Kirk

[wasson65 0]

I see whats going on with the quotes now... if there is a quote in a text field, excell will quote the entire text field and the double quote the quote within the field (does that make any sense??? lol)

 

I opened my CSV file in notepad and removed the begining and ending quotes on my description field and leaving the double quotes around the actual quote... went to easypopulate and dam it went through...

 

maybe there is a way to check for a quote as the 1st and last character of a field (well 2nd to last I guess cause of semi-colons) and if it is there, chop them off...

 

I've got a rewrite in the works right now, that will switch to tabs for delimiters and use an explicit end-of-row field instead of carrriage returns, it will handle the apostrophes and double quotes correctly.

 

Give me a couple hours and it should be out.

 

This response is amazing, keep the comments coming, it's very exciting.

 

Tim

[wasson65 0]

Well I guess you solved the problem that I have been debating for the last 3 weeks, I do not have a problem cuting up a excel file and making about 50 new excel files.

May take a full day to do but I figure that it would take about 8 months to ad 15,883 manually.

 

This is great!

 

Thanks for the help, Kirk

 

I'm also going to add a little routine that will let you upload a single large file, and it will break it into smaller files and store those in the catalog/temp dir. Then add an option that would take a file name and get it from the temp dir instead of uploading it.

 

This will make the big sites alot easier. It will still take some time, but won't require any manual file splitting.

 

So hang in there a bit, help is on the way!

 

Tim

[Guest]

do not know if this is of importance,

 

but when I get this error

 

| 020126.jpg | KRT4889-0 | KRT4889-0 | FAX THERMA | 45.99 | 0 | 10/17/2002 | 1 | products | Thermal Tr | KO-REC-TYP | !New!

| 020126.jpg | KRT3382-0 | KRT3382-0 | RIBBON. CO | 4 | 0 | 10/17/2002 | 1 | products | TYPEWRITER | KO-REC-TYP | !New!

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

 

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

 

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

 

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

 

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

 

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

 

ERROR - I didn't find enough fields!

There should be 11 fields and I found 7 fields.

Are you using the right separation character in your file?

Does the number of fields in easypopulate.php match the number in your file?

 

 

It seems to import all of the products anyways.

 

I am wondering if anything will show up funny on the site .

 

I have divided into 53 csv files, it seems that when I import 500 files I get a error about exceeding the 30 seconds as wasson65 had stated.

it stoped at 365 files.

 

so to be safe I have my files at 300 products.

 

this seems to be the most that a person can import at one time safely

 

Kirk

[dreamscape 0]

cool...

 

no we must thank you... this saves alot of us alot of time populating our databases.

 

on another note, the OSC code doesn't strip out slashes in titles for apostrophes, but using the character code ' does the job (have to get rid of the semi-colon or else it thinks a new field is beginning).

 

I've been using the character codes for quotes and semi-colons in the description, but I guess I can stop...

 

another idea... have the new easypopulate (tab delimented) change the special characters to their character codes... well I'll change mine to do that anyways since the development team reccomends using the character codes (found at http://www.w3.org/MarkUp/html-spec/html-spec_13.html) because parts of OSC still do not strip out the slashes (apostrophes in titles, I think emails, etc...)

[Hoza 0]

Firstly... HOLY COW!! This is definitely cool to see this project flying along like this! I agree Tim, it is exciting and fun... well, to watch from this side! :) I'm really appreciating all your hard work, as well as Dynamok for getting the ball rolling!

Well I guess you solved the problem that I have been debating for the last 3 weeks, I do not have a problem cuting up a excel file and making about 50 new excel files.  

May take a full day to do but I figure that it would take about 8 months to ad 15,883 manually.

Okay, I thought I'd fire off a potential suggestion here, but it's not complete. I only have what I believe to be the beginnings of a solution for you using OpenOffice.org. The reason I'm not totally complete with this is that it seems possible in theory, but my personal knowledge stops short of giving you a complete solution...

 

ANYWAY: If you try OpenOffice.org's Calc program (same as Sun's StarOffice Calc, I think), there is a feture in there to save selections of a spreadsheet as Database Ranges. These ranges can then be easily re-selected by a quick double-click. What I'm thinking here, but I don't really have the time to research fully on my own, is that you may just be able to set up a range for each 300-item block. Then, it seems like you might be able to just open your file and select an existing range and export it to it's own file.

 

Again, I'm not sure if this is possible, but I looked a bit into things and maybe there is a macro (OOo has a macro language in it) that would automatically dump the selection into a new file. It may even be possible to make a macro that would automatically run through your datafile and save out all the db ranges in one shot. Additionally, a macro might be able to make the selections/ranges for you in the first place, too.

 

I know, I know... this is more like a "wouldn't it be cool!" kinda thing, since I'm not doing it first. At any rate, it seems well within the realm of possibility if any of you know how to crank out OOo macroes and such. There are also a lot of sources for that type of stuff on the Web.

 

 

Now, having said all that, it may take longer to solve the problem this way than it will take Tim to implement his "large file" optimizations!!

 

Hehe... oh well, I'm getting into the spirit here I guess. Wish I could contribute more. :)

[Hoza 0]

excel will double quote the quotes making it "The book ""A Hobbit's Tale""...

I don't have Excel anymore, but I wanted to prod someone with it to look something. I remember having this problem with Excel placing quotes around a text field automatically. You wouldn't see it until you opened the exported file (friggin' stupid if you ask me... mostly since I don't know why that "feature" would be desireable.) Anyway, there is a setting that will change that to not place the quotes in there.

 

Ugh... I don't know what the setting is and I don't have Excel installed anymore to check, sorry. It might be something to do with the formatting of cells or something. I'm really sorry to not have better info, but I'm working from long-ago memory here. I do remember setting something that may have been file-specific, rather than a permanent Excel setting. It simply stripped the quotes from around the various text fields of the export. Look into settings regarding Text-type fields, I think, at least as a start.

 

Hope this helps... I certainly think it's nice of Tim to work quote-checking into Easy Populate, but I think it's kinder of us to just do away with the problem in the first place from the Excel side. Of course, I also hope I'm remembering things right and I'm not just wasting space here! :D