Importing text file data into rows in MySQL

Question

I have a text file which contain rows in this format:

com1,System Access,MinimumPasswordAge,1
com1,System Access,MaximumPasswordAge,42
com1,System Access,MinimumPasswordLength,7

I wish to import this data in 3 seperate rows in the following format:

"com1" in ID column,
"System Access" in Audit_Type column,
"MinimumPasswordAge" in System_Setting column, and
"1" in Actual_Value column.

Im using MySQL Query Browser in Windows 7 SP1.

Answer 1

1. Create table to hold your data.

2. Create CSV file. Do not include field headers.

3. Open MySQL Query Browser and run SQL:

---

load data local infile 'fullpath.csv' into table tableName
fields terminated by ','
lines terminated by '\n'
(ID, Audit_Type, System_Setting, Actual_Value);

Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator.

---

UPDATE 1

As you can see, 3 out of 81 records are only inserted

com1,System Access,MinimumPasswordAge,1
com2,System Access,MinimumPasswordAge,1
com3,System Access,MinimumPasswordAge,1

others fail

com1,Event Audit,AuditSystemEvents,0
com2,Kerberos Policy,MaxTicketAge,10
com3,System Access,NewAdministratorNameAdministrator, 1

It's because you set the column ID as the primary key. com1, com2, com3 already existed on the table. Primary Key is, by definition, unique. The best way to do to be able to insert all rows is by creating another column which whill be auto-incremented and set this as primary key.

Create Table system_settings
(
    RecordID int NOT NULL AUTO_INCREMENT,
    ID VARCHAR(30),
    Audit_Type VARCHAR(30), 
    System_Setting VARCHAR(30), 
    Actual_Value int,
    CONSTRAINT table_PK PRIMARY KEY (RecordID) 
)