I need to import txt file into a table where the data is structured by fixed column, that is, the TIPO field is identified by the first 2 digits of the record, the CBO field from 3 to 8 , and so on.
The TIPO field is responsible for identifying the form of production record, whether it is Consolidated or Individualized.
The difference is that the Consolidated only fills the fields TIPO , DT_ATENDIMENTO and QUANTIDADE ; The Individual completes all the fields. So this changes the configuration of LOAD DATA INFILE. I am currently separating into two types of files, productionI.txt and productionC.txt and with LOAD DATA INFILE representing each file type.
I would like to know if it's possible to import as INDIVIDUALIZED - TYPE 02
LOAD DATA INFILE 'D:/producaoI.txt'
INTO TABLE producao
(@row)
SET
TIPO = SUBSTR(@row,1,2),
CBO = SUBSTR(@row,3,8),
DT_ATENDIMENTO = SUBSTR(@row,9,14),
CNS = SUBSTR(@row,15,29),
CBO = SUBSTR(@row,30,35),
QUANTIDADE = SUBSTR(@row,36,40);
NOME_PACIENTE = SUBSTR(@row,41,70);
Import as CONSOLIDATED - TYPE 01
LOAD DATA INFILE 'D:/producaoC.txt'
INTO TABLE producao
(@row)
SET
TIPO = SUBSTR(@row,1,2),
DT_ATENDIMENTO = SUBSTR(@row,3,8),
QUANTIDADE = SUBSTR(@row,9,14);
Is it possible for a IF/ELSE clause so that the bank itself performs the field identification process and the load itself? Example layman on lines 1, 5 and 14:
1) DECLARE TIPO = SUBSTR(@row,1,2);
2) LOAD DATA INFILE 'D:/producaoI.txt'
3) INTO TABLE producao
4) (@row)
5) IF TIPO = "02"
6) SET
7) TIPO = SUBSTR(@row,1,2),
8) CBO = SUBSTR(@row,3,9),
9) DT_ATENDIMENTO = SUBSTR(@row,10,15),
10) CNS = SUBSTR(@row,16,30),
11) CBO = SUBSTR(@row,31,36),
12) QUANTIDADE = SUBSTR(@row,37,41);
13) NOME_PACIENTE = SUBSTR(@row,42,71);
14) ELSE
15) LOAD DATA INFILE 'D:/cadastro.txt'
16) INTO TABLE producao
17) (@row)
18) SET
19) TIPO = SUBSTR(@row,1,2),
20) DT_ATENDIMENTO = SUBSTR(@row,3,9),
21) QUANTIDADE = SUBSTR(@row,10,15);