Page 1 of 1

Importing .txt file into phpMyAdmin

Posted: Mon Jan 22, 2007 3:09 pm
by Superman859
I wish to import some .txt files into phpMyAdmin but am unsure of the best way to do it....

I don't even know if you can do it directly, as I haven't seen anything about that. (I also can't even find import in phpMyAdmin, although I know it's there unless GoDaddy removed it from their version of phpMyAdmin).

But I have a table created, and wish to import a bunch of data into the rows to fill up a dictionary database.

Here is a sample of the data...

A.E.R.E. : n, Automatic Energy Research Establishment, 원자력 연구소
A.N. Other : n, 익명씨, 선수 미정
A.V.C. : n, American Veterans' Committee미국 재향 군인회
aardvark : n, 땅돼지, 남아프리카산 땅돼지
Aaron : n, 아론(모세의 형, 유태교 최초의 제사장)

I wish to separate it into three columns...English, type, and Korean.

The English word comes before the colon. The type is always the first 1-2 letters before the first comma. After that, the different meanings/words are divided by commas. (except in that last example)...

But what would be the best way to get this into the database so that it is fully searchable? The English part is easy for searching, but what about Korean? There may be several meanings, which are separated by commas. Could I put all the Korean into the same column, and still manage to make it searchable if someone types in any one of those words (retreive the English).

Furthermore, I am not sure HOW to get it all into the database. I certainly won't do it manually - there are thousands and thousands of rows.

I was hoping there would be an easy way to do it using an import and a delimiter, but I never saw that. Other than that, I was thinking I could simply use a loop and regex expression in PHP to fill it up...

Any ideas on this?

Posted: Mon Jan 22, 2007 3:15 pm
by feyd
Considering you have fairly irregular separation, you'll have to engineer a script to perform the insertion.

As for the structure and searching, I'm not too sure.

Posted: Mon Jan 22, 2007 3:17 pm
by Christopher
I don't know if you can easily do that, but with MySQL you would use LOAD DATA INFILE first with some delimiter (":" perhaps) to get into the database (an import table). Then you could run queries to split the data on commas and put it in other fields. You could also use something like PHP to parse the file, split the lines and INSERT them into a database.

Posted: Mon Jan 22, 2007 6:07 pm
by Superman859
I was thinking I would use PHP.

Does this sound like it might work - I was going to use PHP, read the file using file(). Use count() to see how many items are in the array formed.

Use a for loop to iterate count($array) number of times, processing that line of the array with a regular expression and inserting into database...

It seems like it would work logically, I just don't have a lot of experience with PHP and SQL languages, so I'm not sure of the specifics yet. Nor do I know how long it would take to finish the loop, as there are well over 100,000 lines to process.