MYSQL import: port a big list of names into mysql table column. An easy way?

Question

I have a txt file with tons of complex names I want to insert into my sql table named cred_insurances. The table is currently blank. Each line should be one record and the name should be in the column called ProviderName. My text file is like this:

Alabama Medicaid
Alaska Medicaid
Arizona Medicaid (AHCCCS)
Arkansas Medicaid
California Medicaid
Colorado Medicaid
Colorado Medicare
Connecticut Medicaid
Delaware Medicaid
Florida Medicaid
ETC...BLAH BLAH BLAH

I was thinking of doing some sort of replace function on the text file that might be able to put the proper sql syntax before and after each name so that I could paste it all at the prompt, but that seems quite much. There must be an easier way to do this.

I think that a single command for each line would be thus:

INSERT INTO cred_insurances (ProviderName) values ("Alabama Medicaid");

Answer 1

LOAD DATA INFILE 'data.txt' INTO TABLE cred_insurances (ProviderName)

(you may need, to tinker with parameters if it ignores stuff after the space, but this should work)

Answer 2

There sure is an easier way to batch import data from a text file, much faster than lots of inserts as well.

Something along these lines LOAD DATA INFILE 'data.txt' INTO TABLE cred_insurances;

Have a look at the documentation to get the correct syntax if you use tab or space delimited etc.

LOAD DATA LOCAL INFILE

Answer 3

The following should work, assuming your lines are separated by a CRLF (\r\n):

LOAD DATA INFILE 'mydata.txt' INTO TABLE cred_insurances
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\r\n' (ProviderName);