# Load library ----
library(fwftools)
strFileInput = "C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.little.rpt"
header <- fileHeader(strFileInput)
header
## $found
## [1] TRUE
##
## $skip
## [1] 40
##
## $headers
## [1] "ENTIDAD" "FECHAEJE" "FECHAAMB" "FECHAGEN" "ARBOLID"
## [6] "TIPO" "ARBOLVER" "ELEMENTO" "NODOID" "INTENSIDAD"
##
## $strHeader
## [1] "ENTIDAD FECHAEJE FECHAAMB FECHAGEN ARBOLID TIPO ARBOLVER ELEMENTO NODOID INTENSIDAD"
##
## $strSubHeader
## [1] "------------------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- ---------- ---------- ---------- ---------- ---------- ----------------------------------------"
##
## $widths
## [1] 132 9 9 9 11 11 11 11 11 41
##
## $begin
## [1] 1 134 143 152 161 172 183 194 205 216
##
## $end
## [1] 132 141 150 159 170 181 192 203 214 255
totallines = fileCountLines(strFileInput)
## File input : C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.little.rpt
##
## Total lines: 485350
totallines
## [1] 485350
fileShowLines(strFileInput, header$skip+5)
## File input : C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.little.rpt
## Max. lines : 45
##
## [Line 1]:SQL> @../../sql/./Consulta_Puntual/sm_ra_intensidadperfil.sql;
## [Line 2]:SQL> select (select MAX('M_' || pais || '_' || entidad || '_' || 'E_') from i_paisentidad where matriz = 'S') || x.pais || '_' || x.entidad as entidad,
## [Line 3]: 2 to_char(current_date, 'YYYYMMDD') as fechaejecucion,
## [Line 4]: 3 to_char(x.fechaambito, 'YYYYMMDD') as fechaambito,
## [Line 5]: 4 to_char(x.fechageneracion, 'YYYYMMDD') as fechageneracion,
## [Line 6]: 5 x.arbolid,
## [Line 7]: 6 x.tipo,
## [Line 8]: 7 x.arbolver,
## [Line 9]: 8 x.elemento,
## [Line 10]: 9 y.nodoid,
## [Line 11]: 10 to_char(round((y.intensidad * 10000000000),0)) as intensidad
## [Line 12]: 11 from i_alarma_historial x
## [Line 13]: 12 inner join i_arbolintensidad y on x.alarmaid = y.alarmaid
## [Line 14]: 13 inner join
## [Line 15]: 14 (
## [Line 16]: 15 select x.pais, x.entidad, x.arbolid, x.fechageneracion
## [Line 17]: 16 from i_alarma x
## [Line 18]: 17 inner join
## [Line 19]: 18 (
## [Line 20]: 19 select pais, entidad, arbolid, max(fechageneracion) as maxfg
## [Line 21]: 20 from i_alarma
## [Line 22]: 21 group by pais, entidad, arbolid
## [Line 23]: 22 ) y on x.arbolid = y.arbolid and x.pais = y.pais and x.entidad = y.entidad
## [Line 24]: 23 where x.fechageneracion between y.maxfg - 3 * 7 and y.maxfg
## [Line 25]: 24 group by x.pais, x.entidad, x.arbolid, x.fechageneracion
## [Line 26]: 25 ) z on x.arbolid = z.arbolid and x.fechageneracion = z.fechageneracion and x.pais = z.pais and x.entidad = z.entidad
## [Line 27]: 26 where x.pais = '011' and x.entidad = '0133'
## [Line 28]: 27 order by (select MAX('M_' || pais || '_' || entidad || '_' || 'E_') from i_paisentidad where matriz = 'S') || x.pais || '_' || x.entidad,
## [Line 29]: 28 to_char(current_date, 'YYYYMMDD'),
## [Line 30]: 29 to_char(x.fechaambito, 'YYYYMMDD'),
## [Line 31]: 30 to_char(x.fechageneracion, 'YYYYMMDD'),
## [Line 32]: 31 x.arbolid,
## [Line 33]: 32 x.tipo,
## [Line 34]: 33 x.arbolver,
## [Line 35]: 34 x.elemento,
## [Line 36]: 35 y.nodoid
## [Line 37]: 36 ;
## [Line 38]:
## [Line 39]:ENTIDAD FECHAEJE FECHAAMB FECHAGEN ARBOLID TIPO ARBOLVER ELEMENTO NODOID INTENSIDAD
## [Line 40]:------------------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- ---------- ---------- ---------- ---------- ---------- ----------------------------------------
## [Line 41]:M_011_2100_E_011_0133 20141229 20141016 20141219 2 2 2 1168 1 3380321600
## [Line 42]:M_011_2100_E_011_0133 20141229 20141016 20141219 2 2 2 1168 4 4700459200
## [Line 43]:M_011_2100_E_011_0133 20141229 20141016 20141219 2 2 2 1168 5 6225918500
## [Line 44]:M_011_2100_E_011_0133 20141229 20141016 20141219 2 2 2 1168 6 3175000000
## [Line 45]:M_011_2100_E_011_0133 20141229 20141016 20141219 2 2 2 1168 7 300000010
fileShowMidLines(strFileInput, totallines-10, totallines)
## File input : C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.little.rpt
##
## [Line 485340]:M_011_2100_E_011_0133 20141229 20141113 20141228 3 3 2 195535237 173 0
## [Line 485341]:M_011_2100_E_011_0133 20141229 20141113 20141228 3 3 2 195535237 175 0
## [Line 485342]:M_011_2100_E_011_0133 20141229 20141113 20141228 3 3 2 195535237 176 0
## [Line 485343]:M_011_2100_E_011_0133 20141229 20141113 20141228 3 3 2 195535237 177 0
## [Line 485344]:M_011_2100_E_011_0133 20141229 20141113 20141228 3 3 2 195535237 178 0
## [Line 485345]:M_011_2100_E_011_0133 20141229 20141113 20141228 3 3 2 195535237 179 0
## [Line 485346]:
## [Line 485347]:485278 rows selected.
## [Line 485348]:
## [Line 485349]:SQL> SQL> spool off;
## [Line 485350]:
fileFWFtoCSVtime(strFileInput,
"C:/Alabern/Material/R/Siopeia/data/temporal.csv",
header$begin, header$end,
header$strHeader, header$strSubHeader,
header$skip, 10000)
## File input : C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.little.rpt
## File output: C:/Alabern/Material/R/Siopeia/data/temporal.csv
## Skip lines : 40
##
##
## Total lines file: 485350
## Time estimated : 3.898072 seconds
## [1] 3.898072
fileFWFtoCSV(strFileInput,
"C:/Alabern/Material/R/Siopeia/data/procesado.csv",
header$begin, header$end,
header$strHeader, header$strSubHeader,
header$skip, 0)
## File input : C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.little.rpt
## File output: C:/Alabern/Material/R/Siopeia/data/procesado.csv
## Skip lines : 40
## Max. lines : 0
## Append : no (n)
##
## [Line 1]:SQL> @../../sql/./Consulta_Puntual/sm_ra_intensidadperfil.sql;
## [Line 2]:SQL> select (select MAX('M_' || pais || '_' || entidad || '_' || 'E_') from i_paisentidad where matriz = 'S') || x.pais || '_' || x.entidad as entidad,
## [Line 3]: 2 to_char(current_date, 'YYYYMMDD') as fechaejecucion,
## [Line 4]: 3 to_char(x.fechaambito, 'YYYYMMDD') as fechaambito,
## [Line 5]: 4 to_char(x.fechageneracion, 'YYYYMMDD') as fechageneracion,
## [Line 6]: 5 x.arbolid,
## [Line 7]: 6 x.tipo,
## [Line 8]: 7 x.arbolver,
## [Line 9]: 8 x.elemento,
## [Line 10]: 9 y.nodoid,
## [Line 11]: 10 to_char(round((y.intensidad * 10000000000),0)) as intensidad
## [Line 12]: 11 from i_alarma_historial x
## [Line 13]: 12 inner join i_arbolintensidad y on x.alarmaid = y.alarmaid
## [Line 14]: 13 inner join
## [Line 15]: 14 (
## [Line 16]: 15 select x.pais, x.entidad, x.arbolid, x.fechageneracion
## [Line 17]: 16 from i_alarma x
## [Line 18]: 17 inner join
## [Line 19]: 18 (
## [Line 20]: 19 select pais, entidad, arbolid, max(fechageneracion) as maxfg
## [Line 21]: 20 from i_alarma
## [Line 22]: 21 group by pais, entidad, arbolid
## [Line 23]: 22 ) y on x.arbolid = y.arbolid and x.pais = y.pais and x.entidad = y.entidad
## [Line 24]: 23 where x.fechageneracion between y.maxfg - 3 * 7 and y.maxfg
## [Line 25]: 24 group by x.pais, x.entidad, x.arbolid, x.fechageneracion
## [Line 26]: 25 ) z on x.arbolid = z.arbolid and x.fechageneracion = z.fechageneracion and x.pais = z.pais and x.entidad = z.entidad
## [Line 27]: 26 where x.pais = '011' and x.entidad = '0133'
## [Line 28]: 27 order by (select MAX('M_' || pais || '_' || entidad || '_' || 'E_') from i_paisentidad where matriz = 'S') || x.pais || '_' || x.entidad,
## [Line 29]: 28 to_char(current_date, 'YYYYMMDD'),
## [Line 30]: 29 to_char(x.fechaambito, 'YYYYMMDD'),
## [Line 31]: 30 to_char(x.fechageneracion, 'YYYYMMDD'),
## [Line 32]: 31 x.arbolid,
## [Line 33]: 32 x.tipo,
## [Line 34]: 33 x.arbolver,
## [Line 35]: 34 x.elemento,
## [Line 36]: 35 y.nodoid
## [Line 37]: 36 ;
## [Line 38]:
## [Line 39]:ENTIDAD FECHAEJE FECHAAMB FECHAGEN ARBOLID TIPO ARBOLVER ELEMENTO NODOID INTENSIDAD
## [Line 40]:------------------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- ---------- ---------- ---------- ---------- ---------- ----------------------------------------
## Lines processed: 50000
## Lines processed: 100000
## Lines processed: 150000
## Lines processed: 200000
## Lines processed: 250000
## Lines processed: 300000
## Lines processed: 350000
## Lines processed: 400000
## Lines processed: 450000
##
## Total lines: 485350
## Time used : 3.057000 seconds
fileFWFtoCSVtime("C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.big.rpt",
"C:/Alabern/Material/R/Siopeia/data/temporal.csv",
header$begin, header$end,
header$strHeader, header$strSubHeader,
header$skip, 10000)
## File input : C:/Alabern/Material/R/Siopeia/data/sm_ra_intensidadperfil.sql.big.rpt
## File output: C:/Alabern/Material/R/Siopeia/data/temporal.csv
## Skip lines : 40
##
##
## Total lines file: 38419836
## Time estimated : 663.474365 seconds
## [1] 663.4744