OK... It is now 7:59am after an all nighter trying to figure this one out! (maybe I'm just slow) When all else fails, peers shall set you free.
I have 5 txt files separated as TAB delimited files that need to end up in ONE MySQL table. The average file size of the txt files is approx 70 cols and 6,000 rows making them quite large(2.5MB ea)
I already have a cron set up that downloads the newest txt files via ftp so I would like to make the import automated as well. Here goes:
First, I tried fopen. Not good because too long of string.
Then I tried file(). Progress. Contents now in array based on newline char from txt file like so:
$file1 = "BigTextFile1.txt";
$file1_content = file($file1);
I'm thinking I had it all figured out so just slam it into MySQL right??? WELL...there is a TON of data in these files and it's all "compressed" using codes in each column. Here's an example of what I mean.
ColA_______ColB_etc...
A,C,G,H,I,L,PB,C,D,T_etc...
D,G,I,M,N_B,C,E,R__etc...
I could not get explode() to work with the TAB character (I tried \t, 4 spaces, 8 spaces - nothing) Also, due to the commas in the column values, I figured I would have to replace the TAB chars into something other than a comma so I can differentiate between cols. I chose to preg_replace the TAB with "|" like so:
foreach($file1_content as $key => $value){
$file1_new_content = preg_replace("/[\t]+/", "| ", $value);
}
This will give something like:
2100| I| 0| B,C,R,V,AI| C,D,E| B| Z| N| B| 0| N| N| 575000| 2228| D,G,H,J| G,O| 13x12| 620670| G,AA,AG| 046055985| 64046
We're a little bit closer now with the values separated with "|" but still not done. In order for this to be entered into MySQL, it will take quite a loop to get it done: This is what I have so far but it takes WAY too long and there are four more files to enter still:
foreach($file1_content as $key => $value){
$file1_row_content = preg_replace("/[\t]+/", "|", $value);
$file1_row_content = explode("|", $file1_row_content);
$insert_query = "INSERT INTO $table1 VALUES (";
foreach($file1_row_content as $key => $value){
$insert_query .= "'".$value."', ";
}
$insert_query .= " )";
$insert_result = $db_build_connection->query($insert_query);
}
If anyone out there can tackle this one, I will be eternally greatful.
Thanks Gadnium
