Thursday, December 6, 2012

Exporting HTML table into CSV file

I work as a team member in a web application developer at my company. Often, I receive requests from the business users or (sometimes) auditors which asks me to generate some data (usually in CSV format) from the production database. Do not ask about the ACL or Picalo, they are not familiar with it :-(

In fact, actually,  those data which they needed already have provided in a report on the (web) application, but hey,...  they need raw data for further processing, for example in spreadsheet, and that's fine for them as business owners.

To reduce their dependence on programmers just to generate the data, as well as programmers reduced access to the production database (you know, it's a bad bad idea), we can use a Mozilla Firefox add-ons named ExportToCSV created by Souvik Chatterjee. This is a simple extension for exporting HTML table data as a CSV file

INSTALLATION :
  • Just go to this page, click the 'Add to Firefox' button, follow the instruction, you'll need to restart your Firefox.
  • Next, select the menu Tools> Export to CSV> Settings
  • Give a tick on Include Texts from all nodes, and check on Ignore Line Breaks






HOW TO EXPORT :
  • Simple, just right click on any HTML table, on the context menu choose Export Data as CSV.
  • On the next dialog, select the location where you will save the CSV file. Anyway don't forget to give the extension. 'CSV' end of the file name (I think Mr.Souvik miss about this).
  • Click Save button




PROBLEMS WITH CSV (?) :
CSV stands for Comma Separated Values. In short, CSV is actually plain text file that contains values ​​separated by commas.

See the following picture, when i opened the CSV file in text editor :



Well, if the CSV file is opened with spreadsheet applications (eg Microsoft Excel), then they may look a little messed up as follows:




SO HOW TO DEAL WITH IT? :
  • Simple. We can use the Text to Columns feature from Excel itself.
  • Highlight the cells  that are in the first column. 

  • Select the Data tab, then press the Text to Columns button.
  • The dialog wizard will appear;
  • In Step 1 gave tick on Delimited option, then press Next.

  • At Step 2, Set Delimiter to Comma, and Text Qualifier as ", then click Finish button

  • Hoopla! Your data will be neatly separated between cells. Furthermore, the data that data can be  processed through spreadsheet or Save as into Excel format.


Have a try. Not all reports must be requested from the programmer right? Anyway, programmers are human too,   :-)

Note, in this test I use :
  • Mozilla Firefox 14.0.1
  • ExportToCSV 1.2.b1 
  • Microsoft Excel 2007