How to format mytext file?

Question

I have to load set of questions to MYSQL database table.But the table format is like below QNO Questions Option a Option b Option c Option d Rightanswer... but my textfile which i need to load in database using LOADINFILE command is in different format. How can i match my textfile format to my table format?

Answer 1

You can re-map the columns to import in LOAD DATA INFILE.

Check the manual entry

Example:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, field2, field3); 

I like using a graphical front-end like HeidiSQL to point-and-click the desired fields, and copy the generated SQL from there.

Answer 2

This depends upon your table format, but if there is any complex preprocessing then it's probably a good idea to do it outside of mysql. It's probably a lot easier.