CODEWITHSUNDEEP
javaexcelcoldfusionapache-poilarge-files
FangerZero
FangerZero

Reputation: 101

ColdFusion 8 Reading large Excel files

I have been able to create my application so it can take Excel files with multiple sheets and put them into the database. However I'm not sure how I do this with large files 30mb+ I tried looking into bufferedReader but I'm not sure this is what I want since it reads a portion at a time and I am using Apache POI to read in multiple Excel sheets.

Currently if I send in a file to big I'm getting Java heap space errors, which is why I'm feeling the file is to large. During testing it worked fine, and then I used the 32mb file, and it failed.

Below is the code, I appreciate any help in advance. also myfile is an argument

ci_model = createObject("component", "com.data_feeds.models.ci.model_item_setup");
FileIn = createObject("java","java.io.FileInputStream").init(javacast("string", myfile));
wb = createObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").init(FileIn);
workbook = createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(wb);
sheetCount = workbook.getNumberOfSheets();

for(sheetIndex = 0; sheetIndex LT sheetCount; sheetIndex = sheetIndex + 1)
{
    recordIndex = 1;
    sheet = workbook.getSheetAt(sheetIndex);

    rowHeader = sheet.getFirstRowNum();
    rowCount = sheet.getLastRowNum();

    for (rowIndex = 1; rowIndex LTE rowCount; rowIndex = rowIndex + 1)
    {
        row = sheet.getRow(rowIndex);
        columnCount = row.getLastCellNum();

        for(columnIndex = 0; columnIndex LT columnCount; columnIndex = columnIndex +1)
        {
            record[recordIndex][columnIndex+1] = row.getCell(columnIndex);
            if((columnIndex+1)==4)
            {
                record[recordIndex][columnIndex+1] = ci_model.boolean_number(row.getCell(columnIndex).toString());
            }
        }
        recordIndex = recordIndex + 1;
        if(recordIndex == 500)
        {
            this.insert_record(record);
            arrayClear(record);
            request.help.debug.collect_garbage();
            recordIndex = 1;
        }
    }
    this.insert_record(record);
    arrayClear(record);
}
FileIn.close();

I am using ColdFusion 8.

Upvotes: 0

Views: 1098

Answers (1)

Cory Fail
Cory Fail

Reputation: 1090

Your best bet is to import the data in a temporary table. Then import the data from the temporary table to the primary table using ColdFusion. This way you could use ColdFusion to simply import the data in chunks and not all at once.. To import into SQL Server all you need to do is use the import tool and for mySQL there are several ways you can. I personally just import using a csv file. I know this process is a bit less direct but it will put less strain on your ColdFusion process and more on your SQL server.

Another suggestion is to also make sure your queries are not timing out or your page. Adding ?requestTimeout=1000 after your URL could also stop your process from timing out. I understand that it's a java error but if your process is lasting too long it could be timing out.

Upvotes: 1

Related Questions