Is sed a solution to this problem for loading data back into mysql db?

newbie14 · 12-21-2011, 12:52 PM

Dear All,
We have back up a table into text file first via the into file method. Then on another server we try to run this command LOAD DATA LOCAL INFILE '/usr/local/backUp.txt' INTO TABLE data1 FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n'. It works but only able to fill the first column and the rest are all empty. Any solution to this problem? Sample of the data is below.

Code:

308589422 3.10882 101.54966 23 0 215 0.9 2011-08-01 00:00:00 1 00402020F028 00 000 0 00 109.6 13033 4D \N \N \N \N \N \N 
308590571 5.00517 102.22741 20 0 340 0 2011-08-01 00:00:00 1 00000000 27.3 012 0 04.0 154856 BB1012 \N \N \N 01 0100 0 
308596673 2.94121 101.76445 29 0 62 0 2011-08-01 00:00:00 1 00000000 25.4 000 0 04.1 56870.5 AB0569 \N \N \N 02 0000 F 
308596708 3.99147 101.05772 15 0 105 0 2011-08-01 00:00:00 1 00000000 12.6 01B 0 04.1 42562.9 AB0829 \N \N \N 02 0000 F 
308589424 5.4142 100.55539 26 0 126 1 2011-08-01 00:00:01 1 01000028F820 57 3FF 0 F5 1082.31 13276 75 \N \N \N \N \N \N 
308589482 4.95754 102.07095 3 96 235 0 2011-08-01 00:00:01 1 00000000 27.0 000 0 04.1 10278.9 BB1788 \N \N \N 01 0100 F 
308589649 1.8189 102.95381 15 0 0 0 2011-08-01 00:00:01 1 00000000 25.3 2D6 0 04.1 15223 BB1972 \N \N \N 02 0000 F 
308590471 2.63557 103.65955 14 0 66 50 2011-08-01 00:00:01 4 014000203028 44 FF 0 56 102958 15370 13 \N \N \N \N \N \N 
308590497 2.63557 103.65955 14 0 66 50 2011-08-01 00:00:01 4 014000203028 44 FF 0 56 102958 15370 13 \N \N \N \N \N \N 
308590498 2.63557 103.65955 14 0 66 50 2011-08-01 00:00:01 4 014000203028 44 FF 0 56 102958 15370 13 \N \N \N \N \N \N 
308589430 1.66043 103.85198 13 0 101 0.8 2011-08-01 00:00:02 1 01400020FC28 57 FF 0 00 0 14110 2D \N \N \N \N \N \N 
308589460 2.20039 102.23996 8 46 304 0 2011-08-01 00:00:02 1 00000000 14.5 000 0 04.1 12777.8 AB0550 \N \N \N 01 0100 F 
308589490 3.43023 101.58937 7 0 8 0 2011-08-01 00:00:02 1 00000000 25.7 01B 0 04.0 86577.1 AB0731 \N \N \N 02 0000 F 
308589498 2.54766 101.80599 27 0 283 0 2011-08-01 00:00:02 1 00000000 12.3 01B 0 04.1 66774.7 AB0728 \N \N \N 02 0000 F 
309099219 1.72879 103.42198 6 63 125 0 2011-08-01 00:00:02 1 00000000 27.1 000 0 04.1 23969.4 BB1891 \N \N \N 01 0100 F 
308589432 2.79064 102.29934 16 68 235 0.8 2011-08-01 00:00:03 1 01400028F028 58 FF 0 58 264552 00601 5C \N \N \N \N \N \N 
308589725 4.81002 100.71297 14 24 146 0.8 2011-08-01 00:00:03 1 01400020F028 BF FF 0 AD 81911.8 12154 6D \N \N \N \N \N \N 
308589964 3.30702 101.55428 11 0 312 0 2011-08-01 00:00:03 1 00000000 27.7 012 0 04.0 34818.2 BB1465 \N \N \N 01 0100 F

Tinkster · 12-21-2011, 06:14 PM

That's an odd dump. If \n is meant to be a line terminator, why are you
popping lots of \n into the individual rows? And is the original dump from
a linux, a windows or a Mac box?

Cheers,
Tink

newbie14 · 12-21-2011, 08:21 PM

Dear Tinkster,
The original itself is in linux and now I am backing it back also into linux. Any resolution to this?

Quote:

Originally Posted by Tinkster

That's an odd dump. If \n is meant to be a line terminator, why are you
popping lots of \n into the individual rows? And is the original dump from
a linux, a windows or a Mac box?

Cheers,
Tink

Tinkster · 12-21-2011, 08:34 PM

May I ask why you're not using the mysqldump utility instead?
Chances are the results will be more predictable.

Cheers,
Tink

newbie14 · 12-21-2011, 08:35 PM

Dear Tinkster,
Mysqldump was too slow and the original data have been deleted ready. So we are just left with this text files. How best to recover it?

Quote:

Originally Posted by Tinkster

May I ask why you're not using the mysqldump utility instead?
Chances are the results will be more predictable.

Cheers,
Tink

Tinkster · 12-21-2011, 08:46 PM

Hard to say ... what's the actual table structure you're trying to
push this into? Looking at the file w/ awk, counting columns, the
result isn't promising for any import, no matter what tool you'd
use ....

Code:

awk '{print NF}' mysql.dump
24
23
23
23
24
23
23
24
24
24
24
23
23
23
23
24
24
23

Any respectable SQL database server will barf on that (even MySQL
does, apparently).

Cheers,
Tink

newbie14 · 12-21-2011, 08:48 PM

Dear Tinkster,
I do not cause we generate it via the select into file method which we had no control over the fields either why is like difficult for me to answer as that is the results of mysql?

Quote:

Originally Posted by Tinkster

Hard to say ... what's the actual table structure you're trying to
push this into? Looking at the file w/ awk, counting columns, the
result isn't promising for any import, no matter what tool you'd
use ....

Code:

awk '{print NF}' mysql.dump
24
23
23
23
24
23
23
24
24
24
24
23
23
23
23
24
24
23

Any respectable SQL database server will barf on that (even MySQL
does, apparently).

Cheers,
Tink

Tinkster · 12-21-2011, 08:54 PM

Quote:

Originally Posted by newbie14

I do not cause we generate it via the select into file method which we had no control over the fields either why is like difficult for me to answer as that is the results of mysql?

Che? Can you please rephrase that?

And can you please stop top-posting? If all you do is quote
my entire post might as well save the precious space, and not
quote at all.

Cheers,
Tink

newbie14 · 12-21-2011, 08:57 PM

Dear Tinkster,
What I was trying to tell is that the file is output based on select into file so why there are 23/24 fields I also could not answer as we did not build the file manually by ourselves? So what best solution can you suggest?

chrism01 · 12-22-2011, 12:10 AM

Unless you've got the orig cmd (and even then) its going to be tricky to say why it is like it is....
So, focussing on the soln, as asked above, tell us what table structure you intend to insert this stuff into and we'll be able to give you some ptrs.
The main thing is to match the data fields you've got with the num of cols in the target table.

newbie14 · 12-22-2011, 02:11 AM

Dear All,
I have managed it with some removal of fields and insertion of dummy fields. So looks like ok for now. So future backup for million of records like this what is the best advice when I select into file should I do some delimiters?

Tinkster · 12-22-2011, 02:43 PM

I recommend using the methods provided by the DB for such purposes.
"select into" doesn't seem to be good enough. If you can modify the
select to insert delimiters, how come you can't use the appropriate
methods for the database, mysqldump?

Cheers,
Tink

chrism01 · 12-22-2011, 06:00 PM

I also recommend using mysqldump if moving between MySQL DBs; it'll handle the fiddly bits for you.
If you want to use the other method, you really need to read the relevant docs pages carefully and know your data very well.
A 3rd option for moving data to other formats generally is to write a program (personally I've done a lot with Perl) and create the output exactly as you want it.

newbie14 · 12-23-2011, 06:20 AM

Dear All,
Actually I forgot the mention the main reason I used the select into because I did not wanted to dump the whole table but just a data between a date range e.g a particular month. So for that case what is best is it dump or any other mechanism?

Cedrik · 12-23-2011, 07:09 AM

Try --where option for mysqldump

Code:

mysqldump -u<user> -p<passwd> \
--where="EXTRACT(MONTH FROM date_field_name) = <month num>" \
<database name> <table name> > dump.sql.txt