README.md

Load CSV file and convert to SQL Query (JAVA)

Code snippets to load a .csv file using library de.siegmar:fastcsv, and convert to SQL string query.

Contains a JUnit test to verify if a query is created and formatted successfully

build.gradle

dependencies {

    // Lib for load/save a .CSV file
    implementation 'de.siegmar:fastcsv:1.0.3'

    // Required -- JUnit 4 framework
    testImplementation 'junit:junit:4.12'

    // Required -- Additional assertions to JUnit
    testImplementation 'org.hamcrest:hamcrest-junit:2.0.0.0'

    // Optional -- Verify if a SQL query is ok
    testImplementation 'com.github.jsqlparser:jsqlparser:2.0'
}

Perguntas.java

public class Perguntas {
  
  public static String csvToQuery(Context context, String fileName) throws IOException {

		InputStream stream = context.getAssets().open(fileName);

		return csvToQuery(stream);
	}

	public static String csvToQuery(InputStream stream) throws IOException {

		if (stream == null) {
			throw new IOException("Arquivo .csv não encontrado ou corrompido. Dados não podem ser nulos");
		}

		StringBuilder sql = new StringBuilder();

		CsvReader csvReader = new CsvReader();
		csvReader.setContainsHeader(true);
		csvReader.setSkipEmptyRows(false);
		csvReader.setFieldSeparator('|');

		CsvContainer csv = csvReader.read(new InputStreamReader(stream));
		Integer indexWithQuotes = csv.getHeader().indexOf("Perguntas Relacionadas");

		if (csv.getRowCount() > 0) {
			sql.append(INSERT_PERGUNTAS);
			sql.append(" VALUES ");

			// Campos: (ordemTopico, topico, ordemSubtopico, subTopico, pergunta, resposta, relacoes, enquadramento, idioma, favorito)
			for (int i = 0; i < csv.getRowCount(); i++) {
				CsvRow row = csv.getRow(i);

				if (row.getField("Pergunta") == null && row.getField("Resposta") == null) {
					throw new PerguntasCsvException("Um cabeçalho é requerido para o arquivo .csv. Campos \'Pergunta\' e \'Resposta\' são obrigatórios");
				}

				// Campo utilizado somente na importação no IOS
				if (row.getField("Pergunta ID") != null) {
					continue;
				}

				sql.append("(");

				for (int j = 0; j < row.getFieldCount(); j++) {

					String field = row.getField(j);

					if (field.isEmpty()) {
						sql.append("NULL");
					} else {
						if (field.matches("\\d+") && !indexWithQuotes.equals(j)) {
							sql.append(field);
						} else {

							field = PatternsCompat.WEB_URL.matcher(field).replaceAll("<a class=\"external\" href=\"$1\">$1</a>");
							field = field.replaceAll("(?i)<br>|<\\s+br\\s+>", "<br/>");

							sql.append(String.format("\'%s\'", field.trim()));
						}
					}

					if (j < row.getFieldCount() - 1) {
						sql.append(",");
					} else {

						/**
						 * Campos: (idioma, favorito)
						 * Não existem no arquivo CSV. São adicionados de forma fixa
						 * na query SQL para inserir no banco de dados
						 */
						if (row.getField("Idioma") == null) {
							sql.append(",\'pt\'");
						}

					    sql.append(",0");
                    }
				}

				if (i == csv.getRowCount() - 1) {
					sql.append(");");
				} else {
					sql.append("),");
				}
			}

		}

		return sql.toString();
	}
}

PerguntasCsvException.java

package br.gov.fazenda.receita.perguntas.exception;

public class PerguntasCsvException extends RuntimeException {

    public PerguntasCsvException(String message) {
        super(message);
    }

    public PerguntasCsvException(String message, Throwable cause) {
        super(message, cause);
    }
}

PerguntasTest.java

package br.gov.fazenda.receita.perguntas;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;

import org.junit.Assert;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

import br.gov.fazenda.receita.perguntas.exception.PerguntasCsvException;
import br.gov.fazenda.receita.perguntas.model.Perguntas;

import static org.hamcrest.CoreMatchers.not;
import static org.hamcrest.Matchers.emptyOrNullString;

public class PerguntasTest {

    @Test
    public void csvParaSql() {

        ClassLoader loader = Objects.requireNonNull(getClass().getClassLoader());
        InputStream stream = loader.getResourceAsStream("perguntas.csv");

        try {
            String sql = Perguntas.csvToQuery(stream);
            Assert.assertThat(sql, not(emptyOrNullString()));

            try {
                /**
                 * @see https://github.com/JSQLParser/JSqlParser/wiki/Examples-of-SQL-parsing
                 */
                Statement stmt = CCJSqlParserUtil.parse(sql);
                Assert.assertNotNull(stmt);
            } catch (JSQLParserException sqlErr) {
                sqlErr.printStackTrace();
                Assert.fail(sqlErr.getMessage());
            }
        } catch (IOException e) {
            e.printStackTrace();
            Assert.fail(e.getMessage());
        }
    }

    @Test(expected = PerguntasCsvException.class)
    public void csvSemCabecalho() {
        ClassLoader loader = Objects.requireNonNull(getClass().getClassLoader());
        InputStream stream = loader.getResourceAsStream("perguntas-origin-no-header.csv");

        try {
            Perguntas.csvToQuery(stream);
        } catch (IOException e) {
            e.printStackTrace();
            Assert.fail(e.getMessage());
        }
    }
}