Dear Guys, I have been stucked in loading file using "load data local infile" command. At first it is loading a null value on my first record followed with one record from my input file. The rest are ignored and i've been receiving lots of warnings.
I tried converting the text file into a csv and was successful in loading all the files but with a lot of null values and data going another field. I used the "fields terminated by ',' lines terminated by ','" instead of '\r\n' because the loading problem. I tried reformatting my txt and csv file, check its spacing(tabs) one by one but still the data seem to go haywire. I'm actually building an application that will accept continuous data (syslog) using c++ that I need to store in Mysql. But just want to handle one thing at a time so I'm just testing how it will accept loading file from the mysql prompt. I'm running on windows XP, mysql server 4.1.11. Kindly refer to the table structure and the attached file. I know you guys are experience and must have encounter this matter before. Will appreciate all your help to a new developer like me, Thanks, Elizabeth mysql> show create table log8; | log8 |CREATE TABLE `log8` ( `log_date` date default '0000-00-00', `log_time` time default '00:00:00', `device_id` varchar(17) default NULL, `log_id` varchar(11) NOT NULL default '', `type` varchar(7) default NULL, `subtype` varchar(7) default NULL, `pri` varchar(6) default NULL, `vd` varchar(4) default NULL, `serial_number` int(11) default NULL, `duration` int(5) default NULL, `policyid` tinyint(3) default NULL, `proto` tinyint(3) default NULL, `service` varchar(10) default NULL, `status` varchar(6) default NULL, `src` varchar(15) default NULL, `srcname` varchar(15) default NULL, `dst` varchar(15) default NULL, `dstname` varchar(15) default NULL, `src_int` varchar(8) default NULL, `dst_int` varchar(8) default NULL, `sent` int(11) default NULL, `rcvd` int(11) default NULL, `sent_pkt` tinyint(4) default NULL, `rcvd_pkt` tinyint(4) default NULL, `src_port` int(11) default NULL, `dst_port` int(11) default NULL, `vpn` varchar(5) default NULL, `tran_ip` varchar(15) default NULL, `tran_port` int(5) default NULL, `dir_disp` varchar(5) default NULL, `tran_disp` varchar(5) default NULL, PRIMARY KEY (`log_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 1 row in set (0.08 sec) mysql> On 6/27/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > Hello. > > > > Send an example of your file and your table's definition > > (use SHOW CREATE TABLE for this purpose). Please, next time > > send your replies to the list. > > > > > > > > > > > >Hope you're doing great today. Back to my question last week, I've > > >been encountering this problem of loading my file using the "load data > > >local infile" command for a particular text file. > > > > > >I cannot determine the error in my sql codes since i find no problem > > >loading other text file into the database. > > > > > > mysql> load data local infile 'syslog5.txt' into table log4 fields > > > > > >>> > > >> > > >>>> > terminated by ' \t' lines terminated by '\n'; > > >> > > >>> > > >> > > >>>> > Query OK, 2 rows affected, 513 warnings (0.03 sec) > > >> > > >>> > > >> > > >>>> > Records: 20 Deleted: 0 Skipped: 18 Warnings: 505 > > > > > > > > >I'm not familiar with this "warnings" myself. I can only load the > > >first line in the "syslog5.txt" and the rest are ignored. From the two > > >rows only the first line is stored and the rest are ignored. > > > > > >The result of my select statement from the log4 table includes all > > >null value on the first row and the value of the second line for the > > >second row. > > > > > >Would appreciate if you can teach me how to debug the warning or maybe > > >enlighten me on the cause of the problem. > > > > > >Thanks for all your help. > > > > > >Elizabeth > > > > > >On 6/24/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > > > > > >>> Hello. > > >>> > > >>> > > >>> > > >>> What 'SHOW WARNINGS' reports? > > > > Elizabeth Bonifacio <[EMAIL PROTECTED]> wrote: > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3656393 180 1 17 53020/udp accept 192.168.2.6 192.168.2.6 83.217.132.7 83.217.132.7 internal external 183 69 1 1 32193 53020 n/a 202.189.48.98 61114 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3655880 584 1 17 36480/udp accept 192.168.2.6 192.168.2.6 70.67.164.40 70.67.164.40 internal external 1601 595 8 8 32193 36480 n/a 202.189.48.98 60652 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3656371 202 1 17 3417/udp accept 192.168.2.222 192.168.2.222 68.67.220.64 68.67.220.64 internal external 153 920 2 2 12000 3417 n/a 202.189.48.98 61092 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3656394 180 1 17 38818/udp accept 192.168.2.6 192.168.2.6 193.49.4.200 193.49.4.200 internal external 172 70 1 1 32193 38818 n/a 202.189.48.98 61115 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3655172 1012 1 17 16249/udp accept 192.168.2.222 192.168.2.222 203.136.241.66 203.136.241.66 internal external 1804 427 11 7 12000 16249 n/a 202.189.48.98 60059 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3656397 180 1 17 56167/udp accept 192.168.2.222 192.168.2.222 203.64.121.219 203.64.121.219 internal external 164 70 1 1 12000 56167 n/a 202.189.48.98 61118 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3656366 209 1 17 3246/udp accept 192.168.2.50 192.168.2.50 219.5.220.9 219.5.220.9 internal external 296 98 2 2 53538 3246 n/a 202.189.48.98 61087 org noop 6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic allowed notice root 3656367 209 1 17 57154/udp accept 192.168.2.50 192.168.2.50 212.179.96.181 212.179.96.181 internal external 296 98 2 2 53538 57154 n/a 202.189.48.98 61088 org noop 6/23/2005 15:45:51 FGT2002A04200757 22010001 traffic allowed notice root 3656395 181 1 17 1398/udp accept 192.168.2.6 192.168.2.6 84.252.47.195 84.252.47.195 internal external 179 91 1 1 32193 1398 n/a 202.189.48.98 61116 org noop 6/23/2005 15:45:51 FGT2002A04200757 22010001 traffic allowed notice root 3656398 180 1 17 36480/udp accept 192.168.2.222 192.168.2.222 70.67.164.40 70.67.164.40 internal external 178 79 1 1 12000 36480 n/a 202.189.48.98 61119 org noop
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
