*PANICK* help importing into mySQL

[hiberphoptik]

OK OK OK before you send me to http://www.mysql.com/doc/en/mysqlimport.html I have already been there and it does not address my concern. I can import files fine but here is where I run into a snag...

example:

I have a table with 4 columns:

one | two | three | four

here is my text file:

one, three, two, four

how do i rearrange the items in the text file as they go into the mysql table??? i cannot manually do it since I will be getting files with hundreds of thousands of records in them, and I cannot change the columns in the database table because we have entire php applications written around that structure and we dont want to have to rewrite everything.

We used to use MS SQL7 and you could tell it what cols in the source go in what cols in the dest but i dont see a way to do that with MySQL

HELP! im doomed if i cant get this to work and my "this is why we should move to linux" speech will come back and slap me in the face 🙁

[stolzyboy]

do you have phpMyAdmin installed, this will do the importing for you, you can simply browse to the text file, then let 'er buck

[hiberphoptik]

yes but how can i tell it where each col in the text file goes in the table? My problem is reordering the columns at runtime

if all my cols in the textfile matched up with the ones in the table there would be no prb at all, but since i get the files from other sources i have to do some shuffling

[stolzyboy]

ahh, you already have a table setup in mysql and want to like merge them togther on import?

[hiberphoptik]

yes, the table is there and flat files already exist

the order of the columns in the flat file do not align with the order of the columns in the table

|---------------|

| DB table |

| firstname |
| lastname |

| middle |

|---------------|

| TXT File |

| lastname |
| middle |

| firstname |

so how do i tell it that column1 in the textfile goes into column2 int he table and column2 in text file goes into column3 in table, and column3 in textfile goes in goes in column1 in the table?

[stolzyboy]

hmmm, not sure that is gonna work on just an import, you may have to import that as a separate table, then run some queries to insert the specific columns from the new table into the old table

[hiberphoptik]

UGH.. that stinks... this is the only thing i can say MS SQL has over MySQL then 🙂

im surprised that nobody else has run into this issue or MySQL does not support somthing like this!

[paulg]

MyPhpAdmin allows you to set a comma delimited list of column names on import.
So if you already have your table defined, using :-
lastname, middle, firstname
will take the first entry in the flatfile and insert it into the 'lastname' table-field and so on.

Just to be sure tho, backup your table before you try this - its easier to restore if you screw up 😃

[Thora_Fan]

I would write a script that would rearrange the rows in your text file, one by one. I had a similar problem a while back. Basically what I did is this:

$delim = "\t"; // change this to whatever your field delimiter is

$fileArray = file("something.txt");
for ($i=0; $i<=count($fileArray)-1; $i++)
{
    list($lastname, $middlename, $firstname) = split($delim, $fileArray[$i]);
    $output .= "$firstname\t$middlename\t$lastname\n";
}

$fp  = fopen("newsomething.txt", "w+");
fwrite($fp, $output);
fclose($fp);

This should work for you, unless I completely misunderstood the problem (and it wouldn't be the first time that's happened). Obivously, if you want the fields to be delimited by something other than \t and your lines to be terminated by \n, make the needed changes in the $output var.

Hope this helps.