Mysql – How to import a specific line from a text file into MySQL

loadMySQL

I'm building an app to import data from a text file to a database.

I want to import only specific lines from the text file. With Navicat I found that we could limit number of line when importing data.

I also found an article saying that we could limit it, so I tried this:

load data infile 'c:/coop/wkwk.txt' into table jo fields 
    terminated by '|' lines terminated by '\n' limit 10,100;

But I get an error in SQL syntax, what is the QUERY to make this work?

Best Answer

If you are using Unix/Linux you could try using a fifo buffer:

mkfifo /tmp/mysql.pipe 
# Select rows 101 to 110
sed -n 101,110p filename > /tmp/mysql.pipe & 
mysql –uusername -p -Ddatabasename -e "LOAD DATA LOCAL INFILE ' /tmp/mysql.pipe ' INTO TABLE tablename"

rm -f /tmp/mysql.pipe

Related Solutions

Mysql – How to load a data file into a specific field

Since you are looking to INSERT an entire file into a single column. You need the LOAD_FILE function:

UPDATE jobs SET Log = LOAD_FILE('/root/copy.log')
WHERE TaskName = 'LouPrMgt004.Backup';

So that you can see just how LOAD_FILE function works, try this:

SET @mycnf = LOAD_FILE('/etc/my.cnf');
SELECT @mycnf;

You should be able to see your my.cnf loaded into a variable

Here is a sample run

mysql> SET @mycnf = LOAD_FILE('/etc/my.cnf');
Query OK, 0 rows affected (0.00 sec)

mysql> select @mycnf\G
*************************** 1. row ***************************
@mycnf: [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_password=1

key_buffer_size=1G
max_allowed_packet=128M

# These two values must be the same
tmp_table_size=16M
max_heap_table_size=16M

# Sort_buffer is too large, so try default to see if it helps
sort_buffer_size = 2M

read_buffer_size=1M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=16M
max_length_for_sort_data=2048
max_sort_length=2048
long-query-time=5
skip-name-resolve
table_cache = 6144
open_files_limit = 32768
interactive_timeout=3600
wait_timeout=3600
thread_cache = 100
max_connections=1000
query_cache_size = 0
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len=2
group_concat_max_len=5000000
query_cache_size=8M
query_cache_limit=32M

#log-output=TABLE
#slow-query-log
#slow-query-log_file=slow-query.log
#expire-logs-days=7

server-id=180
#log-bin=mysql-bin
#log-slave-updates
#relay-log=relay-bin

innodb_file_per_table
innodb_log_file_size=128M
innodb_buffer_pool_size=512M
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT

innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=5000
innodb_thread_concurrency=0

#
#       XtraDB Cluster Options
#

user=mysql
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://
wsrep_slave_threads=2
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

wsrep_sst_method=xtrabackup
wsrep_cluster_name=oxygen_xtradb_cluster_test
wsrep_node_name=oxygen_xtradb_cluster_test_node180

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Be careful with this because some have complained that it does not work

Mysql – How to use variables in file paths in MySQL statements

You cannot keep the path in a variable because variables are interpreted by the server, and path names are interpreted by the mysql client. Related question:

https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error

However, there are a couple of different work-arounds that will allow you to accomplish what you are trying to do, and they are explained in the article.

I'd like to add one more idea:

If you call the mysql script from a Bash script, copy your file into a temp file, so the script always calls the same file name, like "work_file.csv".

After the mysql script runs, you can delete "work_file.csv".

Your original file is kept intact, and the data from that file is now uploaded.

Best Answer

Related Solutions

Mysql – How to load a data file into a specific field

Mysql – How to use variables in file paths in MySQL statements

Related Question