CODEWITHSUNDEEP
mysql
Ben P. Dorsi-Todaro
Ben P. Dorsi-Todaro

Reputation: 321

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?

Upvotes: 1

Views: 5804

Answers (3)

hd1
hd1

Reputation: 34657

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.

Upvotes: 1

AbsoluteƵERØ
AbsoluteƵERØ

Reputation: 7870

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).

Upvotes: 1

Giulio Franco
Giulio Franco

Reputation: 3230

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.

Upvotes: 1

Related Questions