Parsing several csv files using Spring Batch

Question

I need to parse several csv files from a given folder. As each csv has different columns, there are separate tables in DB for each csv. I need to know

• Does spring batch provide any mechanism which scans through the given folder and then I can pass those files one by one to the reader.
• As I am trying to make the reader/writer generic, is it possible to just get the column header for each csv, based upon that I am trying to build tokenizer and also the insert query.

Code sample

public ItemReader<Gdp> reader1() {
    FlatFileItemReader<Gdp> reader1 = new FlatFileItemReader<Gdp>();
    reader1.setResource(new ClassPathResource("datagdp.csv"));
    reader1.setLinesToSkip(1);
    reader1.setLineMapper(new DefaultLineMapper<Gdp>() {
        {
            setLineTokenizer(new DelimitedLineTokenizer() {
                {
                    setNames(new String[] { "region", "gdpExpend", "value" });
                }
            });
            setFieldSetMapper(new BeanWrapperFieldSetMapper<Gdp>() {
                {
                    setTargetType(Gdp.class);
                }
            });
        }
    });
    return reader1;
}

Answer 1

I suppose you are expecting this kind of implementation.

1. During the Partition Step Builder, read all the files names, file header, insert query for the writer and save the same in the Execution Context.

2. In the slave step, for every reader and writer, pass on the Execution context, get the file to read, file header to the tokenizer, insert query that needs to be inserted for that writer.

This resolves your question.

Answer 2

Answers for your questions:

1. I don't know about a specific mechanism on spring batch to scan files.
2. You can use opencsv as generic CSV reader, there are a lot of mechanisms reading files.

About OpenCSV: If you are using maven project, try to import this dependency:

 <dependency> 
   <groupId>net.sf.opencsv</groupId> 
   <artifactId>opencsv</artifactId> 
    <version>2.0</version> 
 </dependency> 

You can read your files making an object for specific formats, or generic headers like this below:

private static List<DadosPeople> extrairDadosPeople() throws IOException {
    CSVReader readerPeople = new CSVReader(new FileReader(people));
    List<PeopleData> listPeople = new ArrayList<PeopleData>();
    String[] nextLine;
    while ((nextLine = readerPeople.readNext()) != null) {
        PeopleData people = new PeopleData();
        people.setIncludeData(nextLine[0]);
        people.setPartnerCode(Long.valueOf(nextLine[1]));

        listPeople.add(people);
    }
    readerPeople.close();
    return listPeople;
}

There are a lot of other ways to read CSV files using opencsv:

If you want to use an Iterator style pattern, you might do something like this:

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"));
 String [] nextLine;
 while ((nextLine = reader.readNext()) != null) {
    // nextLine[] is an array of values from the line
    System.out.println(nextLine[0] + nextLine[1] + "etc...");
 }

Or, if you might just want to slurp the whole lot into a List, just call readAll()...

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"));
 List myEntries = reader.readAll();

which will give you a List of String[] that you can iterate over. If all else fails, check out the Javadocs here. If you want to customize quote characters and separators, you'll find constructors that cater for supplying your own separator and quote characters. Say you're using a tab for your separator, you can do something like this:

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"), '\t');

And if you single quoted your escaped characters rather than double quote them, you can use the three arg constructor:

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"), '\t', '\'');

You may also skip the first few lines of the file if you know that the content doesn't start till later in the file. So, for example, you can skip the first two lines by doing:

 CSVReader reader = new CSVReader(new FileReader("yourfile.csv"), '\t', '\'', 2);

Can I write csv files with opencsv?

Yes. There is a CSVWriter in the same package that follows the same semantics as the CSVReader. For example, to write a tab separated file:

 CSVWriter writer = new CSVWriter(new FileWriter("yourfile.csv"), '\t');
 // feed in your array (or convert your data to an array)
 String[] entries = "first#second#third".split("#");
 writer.writeNext(entries);
 writer.close();

If you'd prefer to use your own quote characters, you may use the three arg version of the constructor, which takes a quote character (or feel free to pass in CSVWriter.NO_QUOTE_CHARACTER).

You can also customise the line terminators used in the generated file (which is handy when you're exporting from your Linux web application to Windows clients). There is a constructor argument for this purpose. Can I dump out SQL tables to CSV?

Yes you can. There is a feature on CSVWriter so you can pass writeAll() a ResultSet.

 java.sql.ResultSet myResultSet = ....
 writer.writeAll(myResultSet, includeHeaders);

Is there a way to bind my CSV file to a list of Javabeans?

Yes there is. There is a set of classes to allow you to bind a CSV file to a list of JavaBeans based on column name, column position, or a custom mapping strategy. You can find the new classes in the com.opencsv.bean package. Here's how you can map to a java bean based on the field positions in your CSV file:

ColumnPositionMappingStrategy strat = new ColumnPositionMappingStrategy();
strat.setType(YourOrderBean.class);
String[] columns = new String[] {"name", "orderNumber", "id"}; // the fields to bind do in your JavaBean
strat.setColumnMapping(columns);

CsvToBean csv = new CsvToBean();
List list = csv.parse(strat, yourReader);

Answer 3

Use a MultiResourceItemReader to scan all files.
I think you need a sort of classified ItemReader as MultiResourceItemReader.delegate but SB doesn't offer that so you have to write your own.
For ItemProcessor and ItemWriter SB offers a classifier-aware implementation (ClassifierCompositeItemProcessor and ClassifierCompositeItemWriter).
Obviously more different input file you have more XML config must be write,but it should be straightforward to do.