Have Geek Brain

Convert semi-column text in Excel

I need to convert a semicolon delimited file to Excel. However, there are some fields that need to be removed from the Excel file and some additional fields added. These additional fields are drop-down fields.

Is there a way to do this? The preferred programming language is Java, but the excel macro is also welcome.

thanks

0


a source to share


4 answers


I'm sure you can do it with vanilla Excel. You can either do a global search or replace the comma with a comma and just open the CSV or use the Text to Columns function.



EDIT: I haven't done it programmatically in Java, but in Perl it should be pretty easy with Text :: xSV and Spreadsheet :: WriteExcel

+2


a source


You can take a look at opencsv and HSSF .



0


a source


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

  • Read the file using FileReader
  • Use StringTokenizer with parameter ";" separator to separate fields
  • Make an array for each row, containing a custom object that represents each row. The object can store arrays for the data needed to populate the dropdown.
  • Use Apache POI to create an Excel spreadsheet (there are many POI examples on Stackoverflow)
0


a source


You have two options:

  • Use Apache POI to record and configure XLS
  • 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 (pattern + data) as a file with .xls suffix. Even though its content is really HTML, it will open correctly.

If you are using CSV, you will not be able to get additional features like dropdowns or styles.

0


a source






More articles:


All Articles