'Convert Text File To MySQL Database

I have .txt file that is about 20MB in size. Each Line looks like the following. 

*,00000000,Binary Zero,Description
*,00000001,Binary One,Description
*,00000010,Binary Two,Description
*,00000011,Binary Three,Description
*,00000100,Binary Four,Description
*,00000101,Binary Five,Description
*,00000110,Binary Six,Description
*,00000111,Binary Seven,Description
*,00001000,Binary Eight,Description
*,00001001,Binary Nine,Description
*,00001010,Binary Ten,Description

What I want to do is convert it to mysql database where the astericks will be replaced with an auto increment number called id. And the commas would be where a new field starts. How do I go about doing this?

mysql


Solution 1:[1]

If it would be me, I'd make a ruby script to convert that thing in a .sql with a list of INSERT statements. The only actual issue seems to be the asterisk, that should become an auto key...

So, first of all, you have to create a table in your database. Let's say you do

CREATE TABLE MyTable(
  ID INTEGER AUTO_INCREMENT PRIMARY KEY,
  Binary CHAR(whatever)
  Name VARCHAR(whatever),
  Description VARCHAR(whatever)
)

Then, you can make a .sql with some scripting language.

Maybe you can even do it with a .sh bash (sed -i -E 's/^\*,([^,]+),([^,]+),(.+)$/INSERT INTO MyTable (Binary, Name, Description) VALUES ("\1", "\2", "\3");/')

Otherwise, i feel more comfortable with Ruby:

require 'csv'
CSV.foreach("your_file.txt") do |row|
  puts <<EOS
INSERT INTO MyTable (Binary, Name, Description) VALUES ("#{row[1]}", "#{row[2]}", "#{row[3]}");
EOS
end

this will output a list of insert statements, that you can redirect to a file and feed to the SQL server (you can also write to file directly from the ruby script, or send commands to the SQL server directly from the Ruby script.

Solution 2:[2]

Create your table then use LOAD DATA INFILE in MySQL.

CREATE TABLE MyTable(
  ID INTEGER AUTO_INCREMENT PRIMARY KEY,
  binary VARCHAR(8),
  name VARCHAR(45),
  description TINYTXT
)

Open the file in excel, open office, or something like that, then replace all of the * characters with an empty field.

Then use:

LOAD DATA INFILE 'data.csv' INTO TABLE db2.my_table;

You can download MySQL Workbench and it's a lot *safer* than using PHPMyAdmin. *Provided you're doing something like using SSH to connect and you have no external accounts for MySQL open (so you have to be serverside).

Solution 3:[3]

mysql itself can import csv files:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Do let me know if you have further problems.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Giulio Franco
Solution 2 Absolute?ERØ
Solution 3 hd1

Related Questions