how to import the excel data to the mysql database or using PHP?

Question

the data base data is stored as the image shows. the table is products.

enter image description here

the data in excel is as the following shows. enter image description here

but when i open it by notepad++. it shows like this:

enter image description here

1, the data's format which i prepared is right? if not? how to correct it?

supposed the csv filed named product.csv. when i using LOAD DATA INFILE .when should i put the csv file into? could i load it from my local computer to my site's server?

i want to update the price, if the price is null, then using 0 to fill. this is my command. but it doesn't work

 LOAD DATA  LOCAL INFILE 'D:\\product.csv' REPLACE INTO TABLE products
    FIELDS TERMINATED BY '  '
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (products_model, @var1)
    SET products_price = IF(@var1 = '', 0, @var1); -- Replace empty string with 0

i put the above command in phpmyadmin, it can run, but the data doesn't be updated. what's wrong with it?

ps: is there a way to use PHP to get this? if have, how to do it?

the data's format which i prepared is right? if not? how to correct it? — dreamchaser
– dreamchaser, Commented Nov 4, 2011 at 1:58
CSV format is the right format, Also, PHPmyadmin has a way to import Excel as Excel (Although this might be a tool inside Mysql and not phpmyadmin...) — Itay Moav -Malimovka
– Itay Moav -Malimovka, Commented Nov 4, 2011 at 2:01
when i using the command, but it doesn't work, what's wrong with it? — dreamchaser
– dreamchaser, Commented Nov 4, 2011 at 2:32

John Fisher · Accepted Answer · 2011-11-04 04:41:58Z

1

I haven't used the setup you're working with, but it looks like the code is trying to read a tab-delimited file. The file is comma-delimited.

So, maybe all you need to do is change:

FIELDS TERMINATED BY '  '

to

FIELDS TERMINATED BY ','

answered Nov 4, 2011 at 4:41

John Fisher

22.8k2 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dreamchaser Over a year ago

when i change it, then run the command, the products_price still no updated.

dreamchaser Over a year ago

REPLACE INTO TABLE products...is REPLACE right? i want to update the price.

John Fisher Over a year ago

Look at the documentation for the functions you're working with. Understand them, then change your parameters to give them the information they need.

dreamchaser Over a year ago

i have read it lots of times,but still don't know how to use thm

John Fisher Over a year ago

Maybe you should ask multiple specific questions about the parts you don't understand. Figuring it out on your own because you understand the API will be much more valuable that someone solving the problem for you.

Suus · Accepted Answer · 2012-05-31 08:45:40Z

1

Probably this issue is solved already, but just to give some extra input (also for others who visit this page):

Did you check your excel sheet? It seems that you have your data (price) in a different column than your sql database. In excel the price is located in sixth column, in your sql it is located in the THIRD column... you can see that in your notepad csv file. Each comma represent a column.

answered May 31, 2012 at 8:45

Suus

111 bronze badge

Collectives™ on Stack Overflow

how to import the excel data to the mysql database or using PHP?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related