Export / Edit and Import an Excel CSV via phpmyadmin

[avagyan]

hello,

 

regarding initial instructions-

 

i'm not able to follow "IMPORT" directions because "Insert data from a textfile into table" under the SQL tab under "Or Location of the textfile" is not present, version 2.6.2 w/ host provider. its not present anywhere else either. perhaps it was removed by host? i do not know but it's not there and i can not get a direct answer from host... so forget about version upgrades, etc.

 

using the "Run SQL query/queries on database XXX:" i'm executing the following SQL w/o error BUT it does NOT populate my "table" (my table as browsed via PMA):

 

LOAD DATA LOCAL INFILE 'j:/productsJL.txt' REPLACE INTO TABLE products

FIELDS TERMINATED BY ','

ESCAPED BY '\\'

LINES TERMINATED BY '\r\n'

 

'j:/productsJL.txt' =

29,10000,EME-BPLATE,NoImageJL.gif,500.25,7/5/2007 17:59,7/23/2007 20:02,NULL,45,1,0,0,0

(this is a one line only product table test file)

 

i've tried removing the 2nd backslash and also \n. i've also tried this as a csv (preferable).

 

i receive no errors; the table remains unchanged. any help here is greatly appreciated and i think others may benefit here? i feel this is potentially a valuable contribution to this thread please assist.

 

thanks, j

 

I'm just running the latest ubuntu server and have phpMyAdmin version 2.11.8.1deb1.

I also don't have "Insert data from a textfile into table" there. Instead there is an import tab. But can't import using that. Gives me error every time "Invalid field count in CSV input on line 1."

[♥geoffreywalton]

If you don't have the same number of fields/columns in your csv a in the table you are going to get this error.

 

Leave you to count them up and sort it out.

 

G

[lamouronline]

WOW! Great instructions. Thank you so much. I just updated over 20,700 products with these instructions. Much faster than Easy Populate.

 

Thank you so much.

[lucasvm]

is there an example of how the excel must be configured?

[d1ma]

I've followed the guides and verified the id's throughout the 4 tables and yet I still do not see the products on the home page. This is really frustrating as I've probably re-loaded osCom a good 4 times and wasted 18 hours without success!

 

Is anyone willing to connect and help if I were to provide some access?

 

Thank you in advance,

D

[geoff1951]

Hi there,

 

I have tried to follow the diagram, but when I get to step two, there is none of the stuff shown in the diagram. What version are you using?

 

Regards

Geoff

[geoff1951]

I am now getting this Error message:

 

Invalid column (Unique ID Product Code Subproduct Code Product Name Description materials Size (imp) Size (met) Power Trade Price RRP Category Range ImageName ThumbImageURL ViewImageURL Hi-Res URL Stock StockLevel MPN Manufacturer IsMan IsWoman IsLingerie IsCouples IsFetish IsGayMale IsGayFemale) specified!

 

I am using phpAdmin export facility.

 

setting these options: -

 

Options

Replace table data with file

Ignore duplicate rows

Fields terminated by ;

Fields enclosed by "

Fields escaped by \

Lines terminated by Auto

Column names - the full list is at the top.

 

Any help is appreciated.

 

Regards

Geoff

[geoff1951]

WOW! Great instructions. Thank you so much. I just updated over 20,700 products with these instructions. Much faster than Easy Populate.

 

Thank you so much.

 

How did you do that and what file is it please? Coz I really don't want to manually enter 3000 products.

 

Geoff

[geoff1951]

If you don't have the same number of fields/columns in your csv a in the table you are going to get this error.

 

Leave you to count them up and sort it out.

 

G

 

 

Sorry, I don't understand that, can you enlighten a bit more - thanks

Geoff

[geoff1951]

Sorry, I don't understand that, can you enlighten a bit more - thanks

Geoff

 

 

Is there anyone out that can help me - I am still having the same problems.

 

Geoff

[♥geoffreywalton]

I am now getting this Error message:

 

Invalid column (Unique ID Product Code Subproduct Code Product Name Description materials Size (imp) Size (met) Power Trade Price RRP Category Range ImageName ThumbImageURL ViewImageURL Hi-Res URL Stock StockLevel MPN Manufacturer IsMan IsWoman IsLingerie IsCouples IsFetish IsGayMale IsGayFemale) specified!

 

 

Are you trying to import a file that has a header row? Because you should not have one.

 

Options

Replace table data with file

Ignore duplicate rows

Fields terminated by ;

Fields enclosed by "

Fields escaped by \

Lines terminated by Auto

Column names - the full list is at the top.

 

Most files have the field separator as a comma.

 

HTH

 

G