visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Load Data Question
By: phrench U.S.A.  Date: 06/06/2003 00:00:00  English  Points: 125 Status: Answered
Quality : Excellent
Hello,

Thanks for taking the time to read this.

I am having partial success importing a text file into the my DB with LOAD DATA. I say partial because I am importing the data but I haven't been able to do exactly what I need to do. The text file is delimited with commas. However, there are five columns in it that I need to be split and are delimited by colons. This is done in Oracle with a control file with the following statements.

LOAD DATA
INFILE tcp.infile
append
INTO TABLE cent_1g_tcp_log
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(prot filler,
id filler,
Host_a CHAR TERMINATED BY ':',
Port_a,
Host_b CHAR TERMINATED BY ':',
Port_b,
..
..
)

The example above contains two columns "Host_a" and "Host_b" that are
split further into "Host_a", "Port_a", "Host_b" and "Port_b".

Can something similar be done in MySQL. I have not found th tools that allow me to do this. I have thought about parsing the file with a PERL script and replacing the colons with commas but would rather not do that if I don't have to.

I am using the following version of MySQL on Redhat 9.

Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386)

I appreciate any thoughts.

Thanks,

Phil
By: VGR Date: 06/06/2003 03:51:00 English  Type : Comment
just do it in three simple steps :

first import (the MySql statement LOAD DATA INFILE is 99% like the one of Oracle)
- so you'll have columns Host_a and Host_b -

then add columns Port_a and Port_b :
ALTER TABLE yourtable ADD (Port_a...);

then split further your extra column with :
UPDATE yourtable SET Host_a=LEFT(Host_a,(@a:=LOCATE(':',Host_a))-1), Port_a=SUBSTRING(Host_a,@a+1);

UPDATE yourtable SET Host_b=LEFT(Host_b,(@b:=LOCATE(':',Host_b))-1), Port_b=SUBSTRING(Host_b,@b+1); # or combine both
By: phrench Date: 06/06/2003 09:08:00 English  Type : Comment
Thank you very much for the reply. This is getting me close. I tried this and found two things. This did strip the port_a data off of the host_a (and for the b equivilents as well) but did not enter the data into the port_a column. Additionally, if I ran the command again (which I will have to do whenever I update the table via a batch file) the data is column host_a was removed. I would appreciate any other ideas. If not you have definitely given me a path to follow to find a solution.

Thanks,

Phil

P.S. Below are some abbreviated examples of the output of a select statement before and after the update command.

Original values

host_a | port_a | host_b | port_b |
+-------------------+--------+--------------------+--------+
| 10.8.10.1:4348 | NULL | 10.8.10.23:22 | NULL |

After statement
update cent_tcp_log SET Host_a=LEFT(Host_a,(@a:=LOCATE(':',Host_a))-1), Port_a=SUBSTRING(Host_a,@a+1);

host_a | port_a | host_b | port_b |
+-------------------+--------+--------------------+--------+
| 10.8.10.1 | NULL | 10.8.10.23:22 | NULL |

After 2nd time statement entered
host_a | port_a | host_b | port_b |
+-------------------+--------+--------------------+--------+
| | NULL | 10.8.10.23:22 | NULL |
By: VGR Date: 06/06/2003 09:23:00 English  Type : Answer
true. I apparently needed a second user variable. Next time I'll test my queries first :D

Fixed :

mysql> update pipi set host_a='host:port';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update pipi set Host_a=LEFT((@b:=Host_a),(@a:=LOCATE(':',Host_a))-1),Port_a=SUBSTRING(@b,@a+1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from pipi;
+--------+--------+
| Host_a | port_a |
+--------+--------+
| host | port |
+--------+--------+
1 row in set (0.01 sec)
By: phrench Date: 07/06/2003 16:40:00 English  Type : Comment
Thanks for the help. This really enlightened me. Had to lookup the string functions to understand what was going on.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 831.319090 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page