Regularizing raw data and adding to database using Load Data

[Sravan]

Hi Friends, I am having some situation here. I am having data like this...

"JOB_FINISH" "7.06" 1375105341 787452 22967 503578631 1 1374645973 0 0 1374645973 "mallikas" "batch" "select[ ((type==X64LIN && osrel==50 && clearcase)||(type==X32LIN && osrel==50 && clearcase))]" "" "" "blrlc275" "/home/padbgl/c11t_m6p60_2v5_Iw6/nodm/default/units/top/simulation/titan/FE/TB_IN_CSEA" "" "" "" "1374645973.787452" [i][u][b]2 "blrlc245+" "others" 1 "blrlc245"[/b][/u][/i] 32 225.0 "INWAY_NANOSIM" "/home/padbgl/bin/prjgate -e -- /home/mallikas/.lsbatch/blrlc275.28492.0.1374645972.NANOSIM" 3.542461 2.000000 39952 0 -1 0 0 21904 13 0 0 0 -1 0 0 0 15028 170 -1 "" "padbgl_c11t_m6p60_2v5_Iw6" 256 1 "" "" 0 52288 272924 "" "" "" "" 0 "" 0 "" -1 "/mallikas" "" "" "" -1 "" "" 5136 "" 1374645973 "" "" 0"JOB_FINISH" "7.06" 1375105342 793353 22967 503578626 1 1374826659 0 0 1374826659 "mallikas" "batch" "select[ ((type==X64LIN && osrel==50 && clearcase)||(type==X64LIN && osrel==40 && clearcase))]" "" "" "blrlc275" "/home/padbgl/c11t_m6p60_2v5_Iw6/nodm/default/units/top/milkyway_2010.03/4m_MQLB/source" "" "" "" "1374826659.793353" [u][i][b]0 1 "blrlc266"[/b][/i][/u] 32 225.0 "" "/home/padbgl/bin/prjgate -e -- /home/mallikas/.lsbatch/blrlc275.24538.0.1374826658.DEFAULT" 0.012998 0.014997 0 0 -1 0 0 414 0 0 0 0 -1 0 0 0 21 1 -1 "" "padbgl_c11t_m6p60_2v5_Iw6" 9 1 "" "" 0 70372 200640 "" "" "" "" 0 "" 17 "" -1 "/mallikas" "" "" "" -1 "" "" 5136 "" 1374826659 "" "" 0

The real problem is with those four fields where i have underlined.1. when the first field is 0, the logger is skipping to enter an empty string '' there which makes other fields popping into their previous fields.2. When the first filed is 2 or more the logger is giving them in separate string like

2 'blrlc245+' 'others'

which is supposedly to be like

2 'blrlc245+others'

which makes the rest of the fields move right as many new columns created by logger for one field. Is there any way to regularize this and add files into the database using LOAD DATA INFILE command ? [ as of now it is giving random values for the columns after this fields because of this irregularity ] Hoping for help

[justsomeguy]

The data is messed up, you need to correct the data. In the first example you have these 5 fields, which are fields 23 through 27:

 

2 "blrlc245+" "others" 1 "blrlc245"

 

The second example has these 3 fields, which are fields 23 through 25:

 

0 1 "blrlc266"

 

In the first example field 24 is a string, and in the second one it's a number. The first example has 2 more fields than the second example, that's what the problem is. You need to fix the data.