Monday, June 21, 2010

Exporting from Excel to a CSV with a delimiter other than a comma. It is possible!

I was surprised today to find that when I had a need to export an Excel file out to a CSV file that there wasn't anywhere within Excel (that I could find at least) to change the delimiter from a comma to something else.

The reason I needed to change the delimiter was that the text had commas in some of the fields, and although the text was put into double quotes by Excel this wasn't suitable for me importing easily via PHP into a MySQL database. I was initially wanting to import the xls file via PHP into MySQL but there was a lack of code available to support this, so I decided to stick with the CSV option.

After a bit of searching I came across a blog post from 2005 that had the answers, and I thought it worthy to replicate it here for when I or others need it:
To change the default on your PC to a pipe rather than a comma bring up your default options window by clicking Start -> Settings -> Control Panel -> Regional Settings.
Click the “Number” tab and in the “List Separator” field, replace the current default separator with the one you want to use (let’s say a pipe symbol | ).
Click “OK” to save the change and close the window. You can now save Excel files as pipe delimited files by simply choosing to 'Save As' CSV.
In Windows XP there is an extra step involved:
When you open the Regional settings window, you have to click customize next to the language choice drop down. That will bring up another window with “Numbers” as the first tab item. Change the “List separator” to whatever you want to use as a delimiter.
 Big thanks to the "Astrochimp: Export CSV with Any Delimiter" post.

Sunday, June 13, 2010

Issues, Risks, Assumptions, Questions and Decisions Register Template

I have been using a spreadsheet that I have been slowly extending over the years for recording and monitoring Issues, Risks, Assumptions, Questions and Decisions. I tend to store this in a central repository and encourage team members to add and update entries. I also encourage the recording of entries to be in terms that somebody in another team (business or technically-oriented) could read and understand. We then review this regularly, particularly with how we are tracking with closing out issues.  Below is a link to the template that you are welcome to use and customise as you need.

The template can be downloaded at http://www.scribd.com/doc/32966336/Issues-Risks-Assumptions-Questions-Decisions-template