Answer the question
In order to leave comments, you need to log in
How to quickly add a large CSV file to MySQL?
Good afternoon. I can't find a clear example of using LOAD DATA INFILE anywhere.
I need to add csv file to mysql.
I am doing this now:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
for ($c=0; $c < count($data); $c++)
{
#разделение строки
list($c_classid, $c_instanceid, $c_price, $c_offers, $c_popularity, $c_rarity, $c_quality, $c_heroid, $c_slot, $c_stickers, $c_market_name, $c_market_name_en, $c_name_color, $c_price_updated, $c_pop) = explode(";", $data[$c]);
#Создаем запрос
$sqlGet = "INSERT INTO `csv` (
c_classid,
c_instanceid,
c_price,
c_offers,
c_popularity,
c_rarity,
c_quality,
c_heroid,
c_slot,
c_stickers,
c_market_name,
c_market_name_en,
c_name_color,
c_price_updated,
c_pop
) VALUES (
'".mysql_real_escape_string($c_classid)."',
'".mysql_real_escape_string($c_instanceid)."',
'".mysql_real_escape_string($c_price)."',
'".mysql_real_escape_string($c_offers)."',
'".mysql_real_escape_string($c_popularity)."',
'".mysql_real_escape_string($c_rarity)."',
'".mysql_real_escape_string(str_replace( "\"", "", $c_quality))."',
'".mysql_real_escape_string($c_heroid)."',
'".mysql_real_escape_string(str_replace( "\"", "", $c_slot))."',
'".mysql_real_escape_string($c_stickers)."',
'".mysql_real_escape_string(str_replace( "\"", "", $c_market_name))."',
'".mysql_real_escape_string(str_replace( "\"", "", $c_market_name_en))."',
'".mysql_real_escape_string($c_name_color)."',
'".mysql_real_escape_string($c_price_updated)."',
'".mysql_real_escape_string($c_pop)."'
)";
#заносим в базу
mysql_query($sqlGet) or die(mysql_error());
}
Answer the question
In order to leave comments, you need to log in
Третий параметр в fgetcsv это символ разделителя, непонятно зачем там запятая, а потом ты еще раз делаешь explode(';',,. Если у тебя разделитель ; то сразу его и укажи. Уменьши количество запросов, в одном инсерте может быть сколько угодно строк.
Ну и сложно представить что непонятного в этом запросе)
LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question