1
I have an id field in a csv file that is read as an array. This id field comes with two id in a field because id has an identical answer field called "answer". Instead of repeating this identical field as (example: ID:2334 ANSWER: I AM COOL ID 2187: ANSWER: I AM COOL.) the person who did csv joined the id that has the same answer.(EXAMPLE:ID:2334,2187 ANSWER: I AM COOL) How can I duplicate this identical field by id when reading the csv file AS THE EXAMPLE NOTED ABOVE (example: ID:2334 ANSWER: I AM COOL ID 2187: ANSWER: I AM COOL.)???
<?php
ini_set('memory_limit', '-1');
include 'utils.php';
// Filename contem o nome do CSV passado por parêmetro na linha de comando
$filename = (count($argv) > 3) ? $argv[3] : '';
// Lê o arquivo e o converte para um array com a lista de dados
$csv = parseCsv($filename);
// Extrai metadados do nome do
$path_parts = pathinfo($filename);
/**
* Dicionário de colunas.
* Cada item desse array deve conter a chave igual a coluna do cabeçalho do CSV
* e no valor o nome da coluna no banco de dados.
*/
$dicionario_colunas = [
'ID SKU' => 'ID',
'Categorias de TR relacionadas' => 'CTG_TR_RELACIONADAS',
'Nº da pergunta' => 'N_PERGUNTAS',
'Pergunta' => 'PERGUNTA',
'Tip (i)' => 'TIP',
'Opções' => 'OPÇÕES',
'Se SIM' => 'SIM',
'Se NÃO' => 'NÃO',
];
$csv = normalizeCsvArrayKeys($csv, $dicionario_colunas);
if (empty($csv)) {//verificando se o seu file.csv está vazio
die("Arquivo csv vazio\n");
}
$perguntas = [];
$duplicate = [];
foreach ($csv as $linha) {
$perguntaId = trim($linha['ID']);
if (!array_key_exists($perguntaId, $perguntas)) {
$perguntas[$perguntaId] = [
'ID' => $perguntaId,
'CTG_TR_RELACIONADAS' => trim($linha['CTG_TR_RELACIONADAS']),
'N_PERGUNTAS' => trim($linha['N_PERGUNTAS']),
'PERGUNTA' => trim($linha['PERGUNTA']),
'TIP' => trim($linha['TIP']),
'OPÇÕES' => trim($linha['OPÇÕES']),
'SIM' => trim($linha['SIM']),
'NÃO' => trim($linha['NÃO']),
];
}
}
$migrationID = date('YmdHis');
$migrationRef = 'exames';
$migrationName = $path_parts['filename'];
$outputName = $migrationID . '_' . $migrationRef . '_' . $migrationName . '.sql';
$output = fopen(ROOT . '/db_crx/migrations/' . $outputName, "w"); # not readedlines
try {
echo "\nProcessando os dados\n";
echo "Iniciando output.\n";
fwrite($output, "SET search_path = \"rx_ref\";\n");
fwrite($output, 'BEGIN;' . "\n\n");
fwrite($output, "UPDATE tr_equipamento_perguntas SET obsoleto = NOW();\n");
fwrite($output, "UPDATE tr_equipamentos SET obsoleto = NOW();\n");
if (!empty($perguntas)) {
fwrite($output, "\n" . "-- insert / update tr_fabricantes\n");
foreach ($perguntas as $perg) {
$stmt = "INSERT INTO tr_equipamento_perguntas (tr_equipamento_id, ordem_pergunta, pergunta, dica_1,resposta_sim,resposta_nao) VALUES (" .
"'{$perg['ID']}', '{$perg['N_PERGUNTAS']}', '{$perg['PERGUNTA']}', '{$perg['TIP']}','{$perg['SIM']}', '{$perg['NÃO']}')\n" .
"ON CONFLICT (id) DO UPDATE SET NúmeroPergunta = '{$perg['N_PERGUNTAS']}', Pergunta = '{$perg['PERGUNTA']}', Dica = '{$perg['TIP']}',
RespostaSim = '{$perg['SIM']}', RespostaNão = '{$perg['NÃO']}'," .
"obsoleto = null;\n";
fwrite($output, $stmt);
}
}
fwrite($output, "DELETE FROM tr_equipamento_perguntas WHERE obsoleto IS NOT NULL;\n");
fwrite($output, "\n" .
"INSERT INTO public.crx_migrations (id, reference, description, filename) VALUES ('$migrationID', '$migrationRef', '$migrationName', '$outputName');\n");
fwrite($output, "\nCOMMIT;\n");
echo "Output pronto.\nVerifique o arquivo $outputName na pasta de migrações\n";
}
catch (Exception $e) {
echo "\n" . "Ocorreu uma exceção durante a geração do script:\n";
echo $e->getMessage() . "\n";
echo $e->getTraceAsString() . "\n";
if (file_exists($output)) {
unlink($output);
}
}
Boy, got a little confused your question has how to clarify better ?
– Bulfaitelo
I have a csv file that I normally need to convert to sql file in php. I have already managed to do. but I have only one problem. I have two fields: one called ID and one called RESPONSE. when the csv file was created, the person did not want to take the trouble of repeating fields he had as an indentical response. example: ID = A , REPOSTA= 23 E ID = B, REPLY 23. As the two have the same answer, people together the id in a field and got ID= A,B REPLY= 23. already put an image to better understand
– Gladiador
in the case of selected frames, the first id is the question id, and the second id would be the answer id ? and the answer is the second frame marked in red ?
– Bulfaitelo
the "SKUL ID" field inside the image I posted up there, refers to the ID as in the example of ID= A and ID= B. That I selected in red frames,Now the field If Yes S refers to the ANSWER as in the example I left it for you RESPOTA =23. also selected in red. Now inside this "SKULL ID" Voce can realize that has a field that came with two id number. example: TD003,TD004. These two were placed there pq the answer of the column If yes is the same for both, I do not know if I became clearer?
– Gladiador
Is it clear, in case the case there are 2 SKU ID you need to enter 2 times in the bank the answer in question ? for example id 3 and 4, insert the answer "Test..." ?
– Bulfaitelo
That’s right @Bulfaitelo
– Gladiador
I will answer with the idea of what you can do, to make it easier to understand. but the part of reading the file is easy for you right and would have to put, a line with 2 id and a line with only a 1 ai in the question for me to have as a basis to assemble a logic?
– Bulfaitelo
i’ll put the whole code there in question pq here do not put everything because of the characters....
– Gladiador
Just for the sake of doubt,
$perguntaIdreceives :TD002orTD003;TD004?– Bulfaitelo
It has field where the Id comes alone and other fields the id comes two or three in a field just like in the example of TD003;TD004 because of what I explained there above. I don’t know you understood or you were still in doubt?
– Gladiador
I understood the question whether it comes this way separated by";"
– Bulfaitelo