CSV and insert in mysql

cubax

Hello,
I'm creating a script that reads one CVS file made by a lot of rows.
example of the structure of the file:

And so on for more or less 500 rows.

I use this script for reading the CVS file:


$row = 1;

$fp = fopen ("db_portale_TOTALuser.csv","r");
while ($data = fgetcsv ($fp, 5000, ";")) {
$num = count ($data);

$row++;


for ($c=0; $c < $num; $c++) {
print $data[$c];

Well, it works but for every $data[$c]
I get one row with all all the 84 values together...
Instead of that way, I would need to have the 84 values "in a separate way" because every of them have to be inserted in the relevant field into mysql.
Maybe the solution would be an associative array that keeps in memory (row by row) all the "separated" values of the 84 fields.

So, to be clear, I have to read all those informations and duplicate them with an insert into a "one-to-one" mysql DB.

Ideas?

Bye!

volleytotal_ch

Why don't you use the LOAD DATA function?

cubax

Thanks for your suggestion!
Following it I'm trying to do it in this way:


<?
	//Prepare DBs connections
	$host="localhost";
	$db="userstat";
	$user="xxxxx";
	$password="xxxxxxxxxxxxx";
	$connect = mysql_connect ($host, $user, $password) or die ("Impossibile connettersi al DB");
	mysql_select_db ($db, $connect) or die ("Impossibile selezionare il db $db");
	?>

  $inserting = mysql_query(" LOAD DATA [LOW_PRIORITY | CONCURRENT] INFILE '/var/www/userstat/db_portale_TOTALuser.csv'
    [REPLACE | IGNORE]
    INTO TABLE users
    [FIELDS
        [TERMINATED BY ';']
        [ESCAPED BY '' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(`id` , `civilte` , `first_name` , `last_name` , `updating` , `request_date` , `date_of_birth` , `email` , `email_part_1` , `email_part_2` , `work_phone` , `home_phone` , `mobile_phone` , `pager` , `fax` , `home_addr1` , `home_addr2` , `home_addr3` , `home_city` , `home_state` , `home_zip` , `home_country` , `password` , `nome_utente` , `livello` , `voltimum_answer` , `payment_status` , `company_id` , `function_in_compagny` , `federation` , `use_product` , `newsletter` , `loyalty_point` , `accept_news` , `building_type_1` , `building_type_2` , `building_type_3` , `building_type_4` , `building_type_5` , `building_type_6` , `building_type_7` , `building_type_8` , `projet_hors_bat` , `specialities_1` , `specialities_2` , `specialities_3` , `specialities_4` , `specialities_5` , `adm_federation` , `adm_company` , `creation_step` , `contratto` , `data_fine_contratto` , `billing_address` , `autorizzato_da` , `processo_di_rinnovo_abbonamento` , `relance-0-status` , `relance-1-status` , `froze_status` , `codice_di_sconto` , `data_inizio_contratto` , `prefered_language` , `site_id` , `adsl` , `prefered_website_1` , `prefered_website_2` , `prefered_website_3` , `prefered_website_4` , `vlt_id_2` , `name` , `profilo` , `correction` , `employee` , `area_covered` , `code_naf` , `city` , `state` , `postal_code` , `phone` , `address_1` , `address_2` , `address_3` , `country_id` , `fax_2`)]")

?>

It doesen't do anything? 🙁

What is wrong?

rinjani

You may actually have to go to the mysql documentation and read how to use the LOAD DATA function.

Here's a sql example:

$query = "LOAD DATA IN FILE '$file' INTO TABLE $table FIELDS TERMINATED BY '$char' ENCLOSED BY '\"' LINES TERMINATED BY '$line_terminator'";

where you have to give values for:
$file - file path
$table - table to insert into
$char - field terminator
$line_terminator - carriage return, line feed etc.

Using the above query you should be able to insert a file from somewhere on the same server as mysql.

You can also do it with the additional LOCAL option if uploading from your PC.

The terminology you used includes all the optional and extended commands which will not work as used.

rinjani