Convert Semi-Colon Text to Excel

Question

Hi All,

I need to convert a semi-colon delimited file to an Excel. However, there are some fields that must be removed from the Excel file, and some extra fields to be added. These extra fields are drop-down fields.

Is there any way to do this? Programming language that is preferably to be used is Java, but also welcome the possibility to use excel macro.

Thanks

Answer 1

You could look at opencsv and HSSF.

Answer 2

I'm pretty sure you can do this with vanilla Excel. You can either do a global search and replace on semicolon to comma and just open as CSV or use the "Text to Columns" feature.

EDIT: I've not done this programmatically in Java, but in Perl it should be pretty straightforward with Text::xSV and Spreadsheet::WriteExcel

Answer 3

I'm not sure how to do this with an Excel macro, but for Java:

1. Read the file with FileReader
2. Use a StringTokenizer with a ";" delimeter to separate the fields
3. Make an array for each row holding a custom object representing each row. The object can store arrays for the data needed to populate the drop down box
4. Use Apache POI to create an Excel spreadsheet (There are lots of POI examples on Stackoverflow)

Answer 4

You have two options:

1. Use Apache POI to write and customise the XLS
2. Create a sample spreadsheet in Excel, but save it as an HTML page. Take the saved HTML and use it as a template for your data. You can save the output (template+data) as a file with .xls suffix. Even though its content is really HTML it will open correctly.

If you use CSV you won't be able to get additional features such as drop downs or styling.