POI: Importing CSV data.

Question

How can I efficiently import CSV data with Apache POI? If I have a very large CSV file that I would like to store in my Excel spreadsheet, then I don't imagine that going cell-by-cell is the best way to import...?

Answer 1

How big is your CSV file? Maybe its senseless to do this with java! Both OpenOffice and M$ Excel can open CSV files and transform them into a table "on the fly"!

So: Open the CSV file with Excel, a dialog is shown, you fill out what seperators are used, here is your ready sheet, save it as xls ...

Answer 2

The most efficient way to add information to your Excel spreadsheet is by using an array. This code:

Sheets(1).Range("A1").Resize(uBound(myArray),uBound(myArray,2)).Value = myArray

populates the sheet range from a two dimensional array, where the lower bound is 1.

In your situation, I would read the CSV file into an array first, and THEN use syntax like the above to place it on the worksheet.

Stan Scott

Answer 3

You can use VB Script to open Excel and open your CSV in it, abnd then VBA to to any post import processing and save it as Excel Workkbook or as a new CSV.

something on the lines of

dim oExcel, oWorkbook
Set oExcel=GetObject("", "Excel.application")  
if not oExcel is nothing then
     oExcel.DisplayAlerts=False
     Set oWorkbook=oExcel.workbooks.open("c:\dummy.csv")
     oWorkbook.saveas "c:\dummy.xls"
     oWorkbook.close, true
     oExcel.DisplayAlerts=true
     oExcel.quit
else
     msgbox "Excel cannot be started!!"
end if

save this as a .vbs file, you can call this from out side env.

Answer 4

Apache POI was never designed to call on CSV files. While a CSV File may be opened in Excel, Excel has its own reader that does an auto import. This is assuming that your CSV has the .csv instead of the .txt suffix. If it has the .txt suffix, save it as a .csv. All then you have to do is right click on the CSV and Open With Excel. Presto, the CSV has been imported into Excel.

I am assuming that you are wanting to parse the data from a txt file into the Excel File. If that is the case I would suggest you use a Library liKe SuperCSV instead of trying to get POI to do something it was never designed to do. It will load it all into a Bean, Map or List of your choice as it parses the data and then you can either write it back in the format you chose into a .csv file or use a JDBC-ODBC Bridge or Apache POI to write it directly into and .XLS format. Adds an extra step, but then you have complete control of the data.

SuperCSV carries the Apache2 License, so it should be good for anything you choose to do with it.

Or just use the .split() function in java and parse up the CSV into arrays and load the arrays into .xls with POI.