[SOLVED] csv loading structure into LOAD DATA INFILE

[fenway]

Wasn't it in a CSV file?

 

Yes, but not in the same columnar format as the db table.

It doesn't have to be... that's why you specify the the columns for the DB in the order of the CSV file headings.  Which is why I said you need column names, not values.  Make sense?

[webent]

Ok, so just put the csv header names in the places that I want that relevant data to go? Wish I would of hired you to do this... The programmer I hired said that it can't be done this way... I'm just wondering how I am going to be able to manipulate some of the fields variables though, if I have to stick it straight into the db?

[fenway]

Ok, so just put the csv header names in the places that I want that relevant data to go? Wish I would of hired you to do this... The programmer I hired said that it can't be done this way... I'm just wondering how I am going to be able to manipulate some of the fields variables though, if I have to stick it straight into the db?

You still can... ;-)

 

No, you need to put the matching DB column name in the column list -- mysql never "sees" the CSV columns (in fact, you need to skip the header line if it is present).

 

You can also skip fields you don't want imported.

 

You can also manipulate them with the SET command (in some later versions).

[webent]

The programmer came up with this shortly after talking with you... it works on his local machine, showed me snapshots, but it doesn't work on my server...

 

$query = 
"
	LOAD DATA LOCAL INFILE '$file'
	INTO TABLE products
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
	LINES TERMINATED BY '\r\n'
	IGNORE 1 LINES
	(
	@var0,
	@var1,
	@var2,
	@var3,
	@var4,
	@var5,
	@var6,
	@var7,
	@var8,
	@var9,
	@var10,
	@var11,
	@var12,
	@var13,
	@var14,
	@var15,
	@var16,
	@var17,
	@var18,
	@var19,
	@var20,
	@var21,
	@var22
	)
	SET                
	product_vendor = 'doba',
	product_sku = @var1,
	product_quantity = @var17,
	product_manufacturer = @var5,
	product_name = @var2,
	product_line = SUBSTRING_INDEX(@var3, '|', 1),
	product_master_category = SUBSTRING_INDEX((SUBSTRING_INDEX(@var3, '|', 2)), '|', -1),
	product_category = SUBSTRING_INDEX(@var3, '|', -1),
	product_image = SUBSTRING_INDEX(@var4, '/', -1),
	product_image_path = SUBSTRING(@var4,1,(select LOCATE( (SELECT SUBSTRING_INDEX(@var4, '/', -1)) , @var4))-1),
	product_description = concat(@var6, '<br />' ,@var7),
	product_price = @var15,
	product_msrp = @var16,
	product_map = IF (@var14>0,'Yes','No'),				
	product_map_price = @var14,		
	product_weight  = @var8,
	product_set_ship_price = @var19,
	product_added_date = CURRENT_TIMESTAMP,
	product_upc =  @var21,
	product_metatags_title = concat(@var21, '-' ,@var2),
	product_metatags_keywords = concat(@var21, '-' ,@var2),
	product_metatags_description  = @var6;
";

[fenway]

I'm still confused... yes, some of the SET fields are expressions, so they're required... but why just the assignments? And where are those user variables set?

[webent]

He said, @var is user variable

 

It works perfectly on his PC via command line... so frustrating

[webent]

The thing is, if you remove this line, IGNORE 1 LINES, it'll at least insert the headers

[fenway]

He said, @var is user variable

 

It works perfectly on his PC via command line... so frustrating

Sorry, I was thinking about something else.

 

What version of mysql? SET clause is only supported as of v5.0.3

[webent]

I got it, you know, all this time, and it was LINES TERMINATED BY '\r\n' causing all the problem... Needed to be LINES TERMINATED BY '\n'

[fenway]

I got it, you know, all this time, and it was LINES TERMINATED BY '\r\n' causing all the problem... Needed to be LINES TERMINATED BY '\n'

Good ol' DOS->UNIX issues.