Hi everyone,
I have a large text file that is output of mysql select, like:
pet:
+--------------+-------------+---------+------+------------+
| name | owner | species | sex | birth |
+--------------+-------------+---------+------+------------+
| Mickey Mouse | Walt Disney | mouse | m | NULL |
| Tom | Walt Disney | cat | m | 1960-03-03 |
| Jery | Walt Disney | mouse | m | NULL |
| Spoky | Trey Parker | fish | NULL | 2001-06-13 |
| Fluffy | Harold | Hamster | f | 1993-02-04 |
.....skip......
Now I wanna to insert the same data into another MySQL table, but I have only this text file output.
I believe it is possible to make this file suitable for using MySQL's 'load data' statement by means either awk or sed, it means suitable format like
Mickey Mouse Walt Disney mouse m \N
Tom Walt Disney cat m 1960-03-03
.......
with tab as a delimiter,ending each line with EOL character and replace each 'NULL' value with '\N'
My knowledge of awk is weak, please any help?
Let's suppose that the file is as you told with a header of 4 lines 'pet:\n+--...' and has been put in the file "pet_1.txt"
then a simple small script will do it (not clever, nor optimised, but it works) :
but beware, there are items here that contain spaces ! don't know the MySQL load format, but you should do somthing for it because it won't load as you want (maybe by enclosing each element with '"' cars thus preceeding the code
with that small condition
I think my code produced what you want but not what MySQL is expecting when there blanks within the fields. If MySQL accept compound-strings surrounded by "double quote" cars. In that case, you should use
sudhamacs, clever solution but :
if a field has "NULL" in it (as in "this is a NULL case"), it won't work (I agree, it's rather a stupid case )
the 4 first lines of the header are still there
your method adds a \t at the begining of the record ... don't know if it is accepted
Hi,
Is there possible to do vlookup in Mysql one table from another table based on one column values and placed the data in same table?
if it is possible in mysql itself pls share links for reference.
Here is the ex: i need to vlookup the cus.id in table to and place the cus.name in 4th... (3 Replies)
#cat file1.log
10.51.61.38;Thu Nov 1 08:06:12 2012;Logout
10.51.62.21;Thu Nov 1 08:06:16 2012;Login
output result:
10.51.61.38;2012-11-01 08:06:12;Logout
10.51.62.21;2012-11-01 08:06:16;Login
how to write script using awk, need help (1 Reply)
After doing a yum install mysql mysql-server on Fedora 14 I wasn't able to fully install the packages correctly. It installed MySQL 5.1. I was getting the following error when running the:
mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)... (3 Replies)
Helloo,
I have worked with some files and so far I got to to point there I have a
fileA:
Person1_Operation20060611090814
Person4_Operation20060512090811
Person6_Operation20060613090214
Person2_Operation20060115090815
Person9_Operation20060617100814
..
...
...
so I was thinking... (3 Replies)
I recently installed mysql-standard-5.0.21-solaris9-sparc-64bit.pkg on a Solaris 9 machine (SunOS 5.9 Generic_118558-19 sun4u sparc SUNW,Ultra-250). The package installation went very smooth, however, starting mysql is a different story. I get the message below everytime I try to start mysql:
#... (2 Replies)
MySQL on my server is down....
I figured out that the mysqld process isn't running. When I try to run it, it says it can't find mysql.sock
Any suggestions?
Here's what I can't do:
can't be root
don't have physical access (do stuff via SSH)
reinstall MySQL (need to keep the current MySQL... (8 Replies)