Help to get Json data to a comma delimited file

[techboy992]

Hi there I have a bunch of json data from a old database backup that I would like to get in to a comma delimited file for mySQL insert.

 

Try to see attached file:

 

What I wan to archive is to get a script that can convert the data in to a sql file that I can be executed by PHP to insert it to a mysql Database

 

Hop some one can help me

 

Thanks in advance

result.txt

Edited August 1, 2016 by techboy992

[Barand]

• json_decode to put it in an array.
• loop through the array
• trim tabs and spaces from field values
• write to csv file

$arr = json_decode(file_get_contents('result.txt'),1);  // create array feom json data
$arr = array_filter($arr);  // remove empty records
$fp = fopen('result.csv','w');  // open output file
foreach ($arr as $record) {
    $rec = array_map('trim', $record);
    fputcsv($fp,$rec);
}
fclose($fp);

Your currency values should not be stored in a DB with currency symbols and comma formatting so you have some extra work to do there.

[techboy992]

Hi Barand

 

I now have the solution to remove the '$' in the records:

foreach ($arr as $record) {
	$record = str_replace('$','',$record);
	$record = str_replace(' ','',$record);	
	$rec = array_map('trim', $record);
    fputcsv($fp,$rec);
}

so how can I now insert the records into the database using mySQLI ?

 

Thanks in advance

Brian

Edited August 2, 2016 by techboy992

[Barand]

You might want to test that solution before you go any further

[techboy992]

Hi Barand I did check it and find it working.

 

https://www.kingsonlinebingo.com/test/csvcreator

 

 

Regards

Brian

[Barand]

Not throwing an error does not mean it works.

1. It removes the characters from the text fields also
2. It still leaves commas in the currency field

[Psycho]

Updated solution from Barand

// create array from json data
$arr = json_decode(file_get_contents('result.txt'),1);
// remove empty records
$arr = array_filter($arr);
// open output file
$fp = fopen('result.csv','w');
foreach ($arr as $record) {
    // trim the values
    $rec = array_map('trim', $record);
    // skip records that only contain " "
    if($rec[0]==' ') { continue; }
    // remove non-numeric characters from amount
    $rec[2] = preg_replace("#[^\d]#", "", $rec[2]);
    // replace character code with apostrophe
    $rec[3] = str_replace(''', "'", $rec[3]);
    // save in output file in csv format
    fputcsv($fp, $rec);
}
// close output file
fclose($fp);

Edited August 3, 2016 by Psycho

[Barand]

Having got the data into a CSV file you can load it into a MySql table using  LOAD DATA INFILE statement

[techboy992]

Hi Barand and Psycho

 

I have problem with that load file to get it right

 

I have try this but it is not inserting anything in the database:

$sql = "LOAD DATA INFILE 'result.csv' INTO TABLE playernames
  FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
  LINES TERMINATED BY '\r\n' 
(playername,location,winnings,mostcash)";


// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result = mysqli_query($con, $sql);

if (mysqli_affected_rows($con) == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con); 
};

[benanamen]

You may need to use LOAD DATA LOCAL INFILE

 

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

Edited August 4, 2016 by benanamen

[techboy992]

Thanks benanamen And barand and phycho

 

I now have it working

 

Regards

Brian