importing data to mysql

thousand of textfiles with data

[whizzy]

I got this web site with several thousand members and all information about users are stored in textfiles, one per user.
I now have a database in mysql and I need to import all the data from the text files to the database. Is this possible?
The textfiles are like this:

username
password
desc

and so on down like this.

Can't say I feel like writing all this manually....

[Birdman]

Yes, you can do it, but I don't know of any utility that would do it automatically. Maybe someone else does, though.

If your handy with PHP, you could write a script that would do it for you. You could use scandir() [us4.php.net] to get the list of files into an array then loop through them and manipulate the lines into mysql_queries and insert them. Hmm, easier said than done, huh?

[jatar_k]

Welcome to WebmasterWorld whizzy,

As Birdman says you can do this fairly easily. A couple of dry runs might be a good idea.

read each file in the dir
open a pointer
grab the data
dump it into mysql
close the pointer

continue as needed until there are no more files.

[beckyh]

PHPMyAdmin has an import feature. You go into the table under the Structure tab and at the bottom is a feature called:
"Insert data from a textfile into table"

[whizzy]

Well since I got like 2000 text files the PHPmyadmin can't work because I'm not gonna sit there selecting the file for xx days. But thanks for the idea.

Guess I have to do it with PHP. That was my original idea, but I kind of lack the knowledge how to do it.

So i use scandir() to scan all the files in the directory, then I read one file at the time, read each line of the file and insert them into the database. sound easy enough. Gotta try this...guess i'll be back here asking more! :)

Thanks for your quick responses!

[beckyh]

I found the function that PHPMyAdmin uses to import. It's a MySQL function documentation found at:

[mysql.com...]

LOAD DATA [LOW_PRIORITY ¦ CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE ¦ IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]

Or for an example it would look like this (the LOCAL part doesn't work for me, so just put your file(s) on the server)

LOAD DATA INFILE 'C:\\WINNT\\TEMP\\MyFile.txt' INTO TABLE `MyTable` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

You can probably write a script to write this line of code 2000 times with the different filenames and tables.

I hope this helps!
BH