MySQL LOAD DATA - Loading a text file with ColumnName=Value format

Question

I've started learning SQL over the past few days, but am stuck while attempting to get my data into the table.

The data's stored in a text file with the format:

ColumnName1=SomeInteger
ColumnName2=SomeInteger
ColumnName3=SomeString
... etc

So far I've managed to create a table (which has about 150 Columns, that I'm hoping to split up and group seperately once I know more) by stripping the =SomeValue in Python. Then wrapping the column names with CREATE TABLE in a spreadsheet. A bit messy, but it works for now.

Now I'm stuck at the following point:

LOAD DATA INFILE 'path/to/file.txt'
INTO TABLE tableName
COLUMNS TERMINATED BY '\n'
LINES STARTING BY '=';

I'm trying to get SQL to insert the data into the column names specified (incase they're not always in the same order), ignore the equals sign, and use the unique filename as my index. I've also tried escaping the equals character with '\=', because the MySQL documentation mentions that everything before the LINES STARTING BY parameter should be ignored. Typing LINES STARTING BY 'ColumnName1=' manages to ignore the first instance, but it's not exactly what I want, and doesn't work for the remaining lines.

I'm not averse to reading more documentation or tutorials, if someone could point me in the right direction.

edit: Rows are delimited like so: I've been given about 100,000 ini files. Each of which is named FirstName_LastName.ini (uniqueness is guaranteed), and each row of data is contained within the ini files. I need to bring the archaic method of account storage into the 21st century. MySQL's LOAD DATA is rumored to be especially fast for this type of task, which is why I began looking into it as an option. I was just wondering if it's possible to manipulate it to work with data in my format, or if I'm better off putting all 100k files through a parser. I'm still open to suggestions that use SQL if there's any magicians reading this.

p.s: If anyone has better ideas for how to get my data (from this text format) into individual tables, I'd love to hear them too.

Answer 1

Personally, I would probably do the whole thing in python, using the MySQLdb module (probably available in a package named something like python-mysqldb or MySQL-python in your favorite distribution). Format your data into a list of tuples and then insert it. Example from http://mysql-python.sourceforge.net/MySQLdb.html:

import MySQLdb

datalist = [("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
            ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
            ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )]

db = MySQLdb.connect(user='dude', passwd='foo', db='mydatabase')
c = db.cursor()

c.executemany(
    """INSERT INTO breakfast (name, spam, eggs, sausage, price)
    VALUES (%s, %s, %s, %s, %s)""",
    datalist)