Insert data file into mysql5 on mac osx

November 14, 2009 § Leave a comment

# Start mysql
mysql -u root -p
# Show all databases within mysql
SHOW DATABASES;
# Create a database named “sample”
CREATE DATABASE sample;
# Select database sample
USE sample;
# Create a database table named “company”
CREATE TABLE company (
user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(45) NOT NULL,
title VARCHAR (45) NOT NULL,
company VARCHAR (45) NOT NULL,
PRIMARY KEY (user_id)
);

# Show existing tables
SHOW TABLES;

mysql> show tables;
+—————-+
| Tables_in_sample |
+—————-+
| company        |
+—————-+
1 row in set (0.00 sec)

# Show table definitions
DESCRIBE company;
mysql> describe company;
+———+——————+——+—–+———+—————-+
| Field   | Type             | Null | Key | Default | Extra          |
+———+——————+——+—–+———+—————-+
| user_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(45)      | NO   |     | NULL    |                |
| title   | varchar(45)      | NO   |     | NULL    |                |
| company | varchar(45)      | NO   |     | NULL    |                |
+———+——————+——+—–+———+—————-+
4 rows in set (0.00 sec)

To import data using Default MySQL located in the path of /usr/local/mysql/bin:

sudo /usr/local/mysql/bin/mysqlimport -u root -p -c name,title,company –verbose –fields-terminated-by=”\t” –lines-terminated-by=”\n” sample ‘/Users/<Username>/database/company.txt’
# skip password as blank for mysql

To import data using macports installed MySQL located in the path of /opt/local/bin:

/opt/local/bin/mysqlimport5 -u root -p -c name,title,company –verbose –fields-terminated-by=”\t” –lines-terminated-by=”\n” sample ‘/Users/<Username>/database/company.txt’

Note: The data file must be a Tab Delimited .txt without any line ending characters from Mac OSX or Window OS. .txt must be located in a folder where permission is not restricted.

\t refers to Tab Delimited. \n refers to new line break. <Username> is home directory ~.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading Insert data file into mysql5 on mac osx at Web Builders.

meta

%d bloggers like this: