Inserting custom tab-delim text files

[Anon]

I have to regularly insert bulk data in tab-delimited format for realty-related site.
I tried using phpMyAdmin and MySQLinput to perform this task. Well, what can I say - cool scripts, but what I really need is to format text before import.
I have to delete several columns, merge 2 columns in 1 and delete trailing spaces in each column.
What is the easiest way to perform that task? PHP or maybe Perl script? Perhaps there is something handy that I can download without writing code myself?

[brandonschnell]

you could roll your own parser.

something like:

<?
$data_lines = file("import.txt");
while(list($line_num, $line) = each($data_lines))
{
$col_array = explode("\t",$line);

echo "INSERT INTO table (col1, col2) VALUES ('". trim($col_array[0]) ."', '". trim($col_array[1] . $col_array[2]) ."')";
}
?>

this loads import.txt, strips off whitespaces before and after the strings. loads the first field right in. concats the second and third fields and loads them into the second column in the db. this is from memory, so its just an example.

[[deleted]]

Just a thought (no criticisms intended):

This method uses INSERT queries, which will slow you down quite a bit.
It may be 'better' to have PHP parse the data to generate a new textfile that you can load into mysql directly.

[Anon]

Thnx a lot to both of you!

2vincent: I don't care about speed to much, I have to add 20-50 records once per day. Anyway, could you please explain your method? Maybe scribble a short example of what you mean? Thnx in advance

[[deleted]]

It's basically the same thing that Brandon posted, but instead of the INSERT query, I'd write the data back into the array and save that as a file.
Then you could load that file into the database directly. MySQL (and most databases) are much faster at loading from a flatfile than doing tons of inserts.

It may not matter now as you only do 50 or so inserts a day, but it usually starts out with "let's not make things too complex because we're only processing a few records" and then the few becomes 'a lot' and 'a lot' becomes 'a few million rows' and then doody hits the fan :-)

[Anon]

🙂))
Thanks for your tips, guyz, that was a great help for me.