Insert data from text file into mysql

[ainoy31]

Hello-

 

I am requesting help with reading data from a text file and then inserting into a mysql db.

 

Here is table fields:

part_id

catcode

year

make

model

submodel   

engtype   

liter   

fuel   

fueldel   

asp

engvin   

engdesg

dciptdescr   

exppartno   

expldescr

vqdescr   

fndescr

 

Here is my php script to read and insert one line of data from the text file

CRW|2007|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; ||

$content = file_get_contents('misc files/part.txt');
$data = nl2br($content)
$lines = explode("|", $data);
$y = count($lines)
for($x=0; $x < $y; $x++)
{
$sql = "INSERT INTO part (part_id, catcode, year, make, model, submodel, engtype, liter, fuel, fueldel, asp, engvin, engdesg, dciptdescr, exppartno, expldescr, vqdescr, fndescr) VALUES ('', '$lines[0]', '$lines[1]', '$lines[2]', '$lines[3]', '$lines[4]', '$lines[5]', '$lines[6]', '$lines[7]', '$lines[8]', '$lines[9]', '$lines[10]', '$lines[11]', '$lines[12]', '$lines[13]', '$lines[14]', '$lines[15]', '$lines[16]')";  
}

 

This script works fine for reading one record but I have a text file with a 100 records. Here is a sample:

Here is a sample data from the text file:

CRW|2007|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; ||

 

CRW|2008|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; ||

 

CRW|2007|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37096|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Clear Coated; ||

 

CRW|2008|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37096|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Clear Coated; ||

 

Hope this is not confusing.  Much appreciation.  AM

 

[vbnullchar]

use foreach

 

$a = "a|b|c";

$b = explode('|', $a);

foreach($b as $value)

[bluejay002]

given in that format... then explode() first each record with the two line breaks to get each record....

then explode() again for '|' for each field.

 

i hope this doesn't sound confusing.

[vbnullchar]

something like this?

<?php
$str = "a|b|c||1|2|3";

$a = explode('||', $str);	

foreach($a as $value) {

	$b = explode('|', $value);

	foreach($b as $bv) {

		echo $bv;

	}

}
?>

[bluejay002]

given that the linebreak is represented by '\n'... then explode() it with '\n\n' before exploding with '|'.

 

if the linebreak is represented by other character, say '\n\r' then explode() it with '\n\r\n\r' before exploding with '|'.

 

those with '||'... i suppose they hold a special value, its just that its empty.

 

did it work?

[ainoy31]

I understand the exploding part.  I just need help inserting the data into the DB.  If I have only one line then I am able to insert.  My issue is when I have 100 lines to do.

[Stephen]

<?php
while (!feof($handle)) {
  $line=fgets($handle);
  //Insert your code for finding the variables... the string is $line
  mysql_query("INSERT INTO table VALUES('','','','etc.')");
}
?>

Didn't test it. $handle is the fopen variable, then I put a comment for where you should find the new variables from $line. Just edit the mysql_query with the variables in the right order.

[bluejay002]

you can do it through a single query using multiple insert or through multiple insert queries.

[ainoy31]

my test file has this data:

 

CRW|2007|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; ||

CRW|2008|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; ||

 

**the || means that it is an empty data with potential data later

 

Here is my code:

$data = file_get_contents('misc files/part.txt');

$data = explode("\n", $data);
print_r($data);
echo count($data);

 

why would the array count return 4 instead of 2?

 

the print_r has this:

 

Array ( [0] => CRW|2007|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; || [1] => CRW|2008|CADILLAC|ESCALADE||V8|6.2|GAS|FI|N|8|L92|Grille Insert|37012|Billet Aluminum Grille; CNC Machined; Marque-Mesh;No Cutting; Polished; || [2] => [3] => )

 

Thanks guys or girls.

[Stephen]

Check your file, you probably have two new lines under the last line with text on it. Try deleting them and test it again.

[ainoy31]

Awesome man.  Thank you very much.  I can actually loop through the array and then insert these data.  I have about 5000 lines to do.  Later.