Transferring a flat file database to a MySQL database

I have a flat file database (yeah gross I know - the worst part is that it's 1.4GB), and I'm in the process of moving it to a MySQL database. The problem is that I'm not sure how to go about doing this - and I've checked through every related quest开发者_Python百科ion on here but none relate to what I want to do, nor how my database is currently setup.

My current flat file database is setup to where a normal MySQL row is its own file, and a MySQL table would be the directory. So for example if you have a user named Jon, there would be a file for the user in a directory named /members/. Within that file would be various information for the user including the users id, rank etc - all separated by tabs, all on separate lines (userid\t4).

So here's an example user file:

userid     4
notes      staff notes: bla bla    staff2 notes: bla bla bla
username   Example

So how can I convert the above into their own rows and fields in MySQL? And if possible, could I do thousands of these files at once?

Thanks.

This seems like a fairly trivial scripting problem.

See the example (pseudocode) below for how you might read in the user directory into a user table.

Clearly, you would want it to be a bit more robust, with error checking / data validation, but just for perspective, see below:

for file in list_dir('/path/to/users/'):
  line_data = dict()
  for line in open(file, 'r'):
    key, value = line.split("\t", 1)
    line_data[key] = value

  mysql_query('''
    INSERT INTO 
      users 
    SET 
      user_id = $1,
      foo = $2,
      bar = $3
    ''', 
    (
      line_data['user_id'],
      line_data['foo'],
      line_data['bar']
    )
  )

LOAD DATA INFILE is used for CSV files, and yours are not, so:

• merge all files in a directory in a single CSV file, removing the name of the columns (userid, username...) and separate the cols with a separator ([TAB], ";", ...) than import as CVS.
• Loop for every dirs you got.

or write a "stupid" program (php works well) that do all this job for you.