Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

#1 02-17-2009 11:40:56

Jersey_Man
Member
Registered: 04-24-2008
Posts: 88

odd CSV issue

Over the weekend I decided to work with the CSV files since setting up categories ground to halt last week.

I managed to enter 1600+ products no problem, but yesterday when I was refining some of the products I ran into a problem when I imported the CSV files, it said that it was an invalid CSV file.

Strange since I had been working with them without issue all weekend.

after a bit of experimenting I found WHY this happened, but it doesn't make a lot of sense.  I exported the products file just fine, then immediately imported it with no changes. Failure.

Eventually I figured out the reason for the failure. When I went to export, I unchecked several of the options on the export page like longdesc so that the CSV would be visually manageable since the long description can get pretty nasty once html tags are used. Apparently CCP wasn't liking the fact I unchecked any of the export options.

I did this numerous times with numerous databases and options. uncheck a field = FAIL, 100% of the time, ALL checked it works fine.

The way this install is going, It's probably going to be an issue of the whole install is corrupted and I have to do it all from scratch. sad

Offline

 

#2 02-17-2009 11:45:16

Dave
Member
Registered: 07-05-2003
Posts: 11233

Re: odd CSV issue

Not sure what leads you to believe that anything may be corrupted but imports ALWAYS require all of the columns defined for the table you are importing into.  The option to not export columns is provided in case you want only specific information for some purpose.

Offline

 

#3 02-17-2009 12:05:41

Jersey_Man
Member
Registered: 04-24-2008
Posts: 88

Re: odd CSV issue

Dave wrote:

Not sure what leads you to believe that anything may be corrupted but imports ALWAYS require all of the columns defined for the table you are importing into.  The option to not export columns is provided in case you want only specific information for some purpose.

That would be Murphy's Law. The worst possible thing that can happen at the worst time possible is usually the result to expect.  smile

Anyways, regarding the issue with CSV's.......

1. Why would I have options to export a CSV without a column (there is a long line of check boxes in export) if I can't import it again?

My issue is that to get my product to display nicely on screen, I've had to use HTML tags in the long description field. The problem with that is that exporting it makes the spreadsheet almost un-readable as it makes every row HUGE, allowing me to only be able to see like 4 rows on screen at a time, and I have like 1600 rows so far, and several thousand more to go. Seeing that I could uncheck items, led me to believe that I could export only what I needed, work on that and bring it back in, but I guess what you are saying is that is not the case at all. Is that correct?

The only thing I can think of then is to export the entire CSV, open it, cut the column with long descriptions, and save it in another spreadsheet so I can actually work on the one I need to work on.

Then when finished, go back and copy/paste the long descriptions back into the spreadsheet?

So I have to export my CSV with ALL items checked in the export screen, I cannot select only the fields I need to work with, it's all or nothing. Correct? No other options for easy editing?

The scary part here is I'm still just on NFL Jerseys. I haven't touched any other Jerseys yet, and still have to deal with Exercise Equip, Archery and Scuba gear, Surf products etc. Unless I'm missing something major here that isn't obvious?

Offline

 

#4 02-17-2009 12:16:53

Dave
Member
Registered: 07-05-2003
Posts: 11233

Re: odd CSV issue

Jersey_Man wrote:

1. Why would I have options to export a CSV without a column (there is a long line of check boxes in export) if I can't import it again?

As I said, you may want to export only certain columns to perform some sort of analysis or trending which doesn't require all of the data.

Jersey_Man wrote:

Seeing that I could uncheck items, led me to believe that I could export only what I needed, work on that and bring it back in, but I guess what you are saying is that is not the case at all. Is that correct?

Correct, that is not the case.

Jersey_Man wrote:

So I have to export my CSV with ALL items checked in the export screen, I cannot select only the fields I need to work with, it's all or nothing. Correct? No other options for easy editing?

Correct and no, not with CSV files.

Offline

 

#5 02-17-2009 13:14:52

goski
Member
Registered: 09-27-2005
Posts: 115

Re: odd CSV issue

I'm not sure what spreadsheet you use but I use Excel.  I sometimes have the same problem with the very large rows.  The height of the row is expanded.  I simple select the row(s) or column(s) or cell(s) that is causing the problem.  Then I go to Format>Cells>Alignment and uncheck the "Wrap Text" box.  Sometimes the checkmark in the "Wrap Text" box is greyed out.  In that case, I have to double check the box to remove the checkmark.

Offline

 

#6 02-17-2009 13:48:43

Jersey_Man
Member
Registered: 04-24-2008
Posts: 88

Re: odd CSV issue

ok, will this work.......

I export CSV complete.

I go to the column I want to remove, in this case the long description, and delete JUST CONTENTS of said column. Leaving column intact.

work on my stuff, save and import again.

But the question is, will the new CSV just completely overwrite cells that have data if the imported cell is blank? or would it import just the data entered and ignore spaces?

Alternatively, goski's idea works as well, it had slipped my mind I could do that. Then I could just hide all the columns I don't want to see, work on my stuff then show all again before I import it again.

Offline

 

#7 02-17-2009 14:40:03

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

If you import the a csv file with empty fields they will be set in the database as empty, there no way for the script to know or guess what you want in the database. If you don't want to see the other fields when working on a file in Excel then you best shot is to hide those fields you don't want to look at.

John

Offline

 

#8 02-25-2009 08:39:57

sushisushi
Member
From: barnsley, UK
Registered: 06-26-2008
Posts: 88
Website

Re: odd CSV issue

when I export the file then re import it doesn't work either AND I have all the boxes ticked.

Why would that be?


Buy sushi making ingredients, sushi party kits and Japanese food at wwwsushisushi.co.uk

Offline

 

#9 02-25-2009 08:44:30

sushisushi
Member
From: barnsley, UK
Registered: 06-26-2008
Posts: 88
Website

Re: odd CSV issue

correction:

only after I have opened it in Excel and re saved it does the problem occur


Buy sushi making ingredients, sushi party kits and Japanese food at wwwsushisushi.co.uk

Offline

 

#10 02-25-2009 10:03:55

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

What form of Excel are you using (PC, Mac)? Check the file type it is getting saved in through Excel.

John

Offline

 

#11 02-25-2009 11:01:56

sushisushi
Member
From: barnsley, UK
Registered: 06-26-2008
Posts: 88
Website

Re: odd CSV issue

mac version, its def being saved as a CSV.


Buy sushi making ingredients, sushi party kits and Japanese food at wwwsushisushi.co.uk

Offline

 

#12 02-25-2009 11:12:53

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

Then you need to save it as a CSV(Windows) format.

John

Offline

 

#13 02-26-2009 04:24:19

sushisushi
Member
From: barnsley, UK
Registered: 06-26-2008
Posts: 88
Website

Re: odd CSV issue

I think it was the mac version of microsoft office that was the problem. I installed Mac OSX Intel version of Open Office Aqua 3. It was free and I like it better than office!

Anyway it fixed one problem and uncovered another. Now when I export the csv, do the mods to it, re import it to the databse all the products come up fine. The problem arises when I try to edit the products within click cart, say to just change the price of the product. When I click submit it says I've not filled in all the required forms, even though I have. I have checked through the form I don't know how many times but I can't get it working.

Does anyone know what causes this issue.

Thanks

Stuart


Buy sushi making ingredients, sushi party kits and Japanese food at wwwsushisushi.co.uk

Offline

 

#14 02-26-2009 07:53:05

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

I would check and or make sure that at least one realtime shipping choice is selected in both local and international shipping choices, they are required and generally don't show up as hi-lited when empty if I remember correctly.

John

Offline

 

#15 02-26-2009 08:08:57

sushisushi
Member
From: barnsley, UK
Registered: 06-26-2008
Posts: 88
Website

Re: odd CSV issue

I'll check that and get back


Buy sushi making ingredients, sushi party kits and Japanese food at wwwsushisushi.co.uk

Offline

 

#16 02-26-2009 11:13:39

Jersey_Man
Member
Registered: 04-24-2008
Posts: 88

Re: odd CSV issue

Ok, so back to my original CSV question........

I'm pretty much just hiding the cells I don't want to work with, and then un hide when I'm done and ready to import again.

I was importing a CSV the other night, and since it was taking awhile to upload I was reading the import page and it said something to the effect of keeping my uploads limited to like 1000 products at a time.

I'm a touch confused by that since since the impression I got from dh783's last reply is that it's an all or nothing deal when you upload, and I see nothing that implies you can append a CSV.

That would lead one to believe that if I have 40,000 total products, I have to download and work with a massive spreadsheet every time I need to update or change just a few items or additions.  Unless of course I do it the slow and clunky way of manually adding products in through the admin pages. 

That sound about right?

Offline

 

#17 02-26-2009 11:42:40

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

The all or nothing comment which you say came from me was concerning the columns used in a database, as you will get an error if you try to just import just a few columns for a database. The 1000 product reference in ccp is concerning the limits imposed by your server in either php maximum time allowed or maximum file size and or limits which the webserver application may have. You can import any number of products, that fall within the limits allowed by the server, which contain all of the columns for that database. That would mean that if your importing 40,000 products then you would need to at least break it up into 40 imports of 1000 products each, through ccp, or if you want to do it all at once then you would need to do a direct import into the database within mysql, which would not be under the limits of a webserver.

You can limit the number of products exported from ccp and work on them and reimport it back, I created a new column in my product database which holds the manufactures name so that I can just export items pertaining to that company but there are other ways to limit the total number of item exported at any given time.

John

Last edited by dh783 (02-26-2009 11:48:35)

Offline

 

#18 02-26-2009 12:23:50

Jersey_Man
Member
Registered: 04-24-2008
Posts: 88

Re: odd CSV issue

Well I'm going by this line in your reply:

"If you import the a csv file with empty fields they will be set in the database as empty, there no way for the script to know or guess what you want in the database."

Since it imports the CSV file as an all in prospect, then how would it be possible to import items broken up a 1000 at a time, as any blanks in the CSV file would be overwritten based on your previous remarks correct?

Best I can tell, there is one and one only products file.

There does not appear to be any append to CSV function available.

IF CCP ignored blank fields, columns, rows etc. I could see it working, but your previous reply indicates it doesn't.  Since I specifically asked if CCP would ignore blanks and just import fields WITH data.

Since it made sense that CCP can't tell what I want or don't want to upload, my other thought that maybe the spreadsheet needed to be something like this, where the second 1000 items went in rows 1001-2000 on the spreadsheet, and rows 1-1000 would be uploaded blank.

But that goes back to your other comment about it will not know what exactly I want to upload, and therefore all items from 1-1000 will be deleted.

However, your last reply indicates that I CAN work on only what I want and upload it, or I can work on specific vendors or groups and upload ONLY those as I work on them.  But I see no reference to this in the manual at all, and I didn't find a thread specific to this kind of thing unless I'm just searching wrong.

Offline

 

#19 02-26-2009 12:35:09

Dave
Member
Registered: 07-05-2003
Posts: 11233

Re: odd CSV issue

Import will only work with identifiers that are actually IN the file being imported.  If your import file only has 1 item that is the only item that will be affected.  John was referring to COLUMNS within the file not complete lines of data.  You can import 40,000 items one at a time or 1,000 items at a time without affect the other 39,000 items.

Offline

 

#20 02-26-2009 12:54:58

Jersey_Man
Member
Registered: 04-24-2008
Posts: 88

Re: odd CSV issue

So If I import 10 items on my first brand new CSV, on line 2-11 (since your row header with field descriptions is line 1)

And then later I want to upload 10 more items to my database (using small numbers for ease of conversation), those items should be on lines 12-21, with lines 2-11 blank right? and then each group of items uploaded would be bumped down the row numbers accordingly. Otherwise it would just over write the previous items correct?

Offline

 

#21 02-26-2009 12:56:42

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

As Dave said the import function only update existing products that match the entry in the id column or if you have entered a unique id for an item, along with the rest of it's data, then it will be inserted it into the database.
When you said empty fields I took it as saying that you had a row with and item id with blank fields and maybe price a chage (as an example) in this case all blank fields would be updated to NULL  for that product id and only those fields that had an entry would contain data.
Also importing will not remove any item in the database you would need to do that manually or thru a raw sql statement.

John

Offline

 

#22 02-26-2009 12:59:01

Dave
Member
Registered: 07-05-2003
Posts: 11233

Re: odd CSV issue

Jersey_Man wrote:

And then later I want to upload 10 more items to my database (using small numbers for ease of conversation), those items should be on lines 12-21, with lines 2-11 blank right?

If they are new items or items you want to update they'd be on lines 12-21.

Don't associate rows in the CSV with products in CCP.  The product identifier in column one is the key to whether something is updated/modified or added.

Offline

 

#23 02-26-2009 13:02:44

dh783
Member
From: Avondale, Arizona
Registered: 04-06-2005
Posts: 6233
Website

Re: odd CSV issue

It doesn't matter on what line the item are on in the import as long as the first row is the column data for that database. You can import any number of files which the items start on row 2 of the import as ccp is not concerned with the row count but the information in the id column, if it finds an item with that information in the dates base then it is updated if not then it is created.

John

Offline

 

Board footer