Inserting .dat or .txt files into MYSQL

Not .csv files. Ordinary text seperated by [space]

[karma_75]

Is there any reccomendation for me as I have to import a lot of data (each of'em is nearly 5000 rows!) into several MYSQL tables? How I am gonna do that?

I know I have to write a function but before going into this dark side I wanted to ask you guys. Maybe someone did it and he can post the function here..?

The files that I am trying to import directly into MYSQL are not .csv files. Ordinary text separated by [space] and [ \n ] characters for each row.

Cheers.

[jatar_k]

you should be able to use LOAD DATA INFILE [dev.mysql.com] to map that format for import into mysql.

though you may have problems if the values are not deleimited and spaces occur within the values as well as the delimiter.

[charlier]

I don't have a function but what I do normally is to just convert them to csv format with a global replace. If the fields don't have spaces in them you can just do a replace (space) with "," and (linefeed) with "(linefeed)". where (linefeed) means the ^p character in word for example. If you have spaces in field text then it is more difficult. If you have rows where the fields are of equal length then you can use the colum mode in an editor like UltraEdit to paste in the ","s.

[karma_75]

Sorry guys, I think I mislead you with my rubbish post.

Here is the cleaner explanation:

I am trying to automate the whole process.

1- I am fetching some info (as *.txt files) from various 3 web sites..
2- I have to insert those data files into my MYSQL tables.

For this I have to create a PHP function to open these fetched .dat files and insert 'em into MYSQL.

This function should look like this:

function insert_me ($doc,$separator="¦",$row_separator="\n"){ 
  //read file
  $doc=fopen("eksen.txt","a"); 
  $content =fread ($doc,getfilesize($doc)); 
  fclose ($doc); 
  //take records
  $records = explode ($content,$row_separator); 
  //mysql connection already made
  //get each record and write them into MYSQL table
  foreach ($records as $record){ 
   //get fields 
   $fields=explode $record,$separator); 
   $karma="echo("; 
   //for every field
   foreach ($fields as $field) 
       $karma .="'$field',"; 
   //kill the comma from end
   $karma = substr($karma,-1); 
   $karma.=")"; 
     
  } 
} 

Unfortunately it does not work..

:)

[bluesman333]

try using file(). file() returns a an array where each element is a line in the file.

$file = file($filename);

foreach($file as $value) {
//mysql_query
}

[bluesman333]

if you want to break that up even more:

$field = split("\t", $value);

[ergophobe]

$fields=explode $record,$separator);

I assume the missing parenthesis is a typo, so my main question is, is this a CSV file? Remember, you can use any delimiter, but basically to be a "csv" file, it means that any strings with the delimiter in them will be quoted and any quotes will be escaped. So

1$ 3$ "Some serious $$ on the table or \"moolah\""

should parse just fine with

$data = fgetscsv($handle, 1000, '$');

Once having parsed it out like that, getting it into the DB should be pretty easy if your data is uniform.