Related to my last post I discovered that Excel does not always open a CSV file and correctly determine the column breaks when you used the pipe delimiter. I have no idea why that’s the case but you can work around this.
If you find that when you open a csv file that has used the pipe delimiter all of the data is in column A, which is not what you wanted you can work around this using one of these two techniques:
- Open Excel and then use file open to load the file – this will force the text import wizard to popup and then you can specify the delimiter to use.
- Convert the file once it’s opened – see below
I suspect you are like me and forget the formatting used in the file and just double click the CSV file and get presented with a single column of data ARRRGHHHH! No problem, Excel can do the conversion for you without having to close the file and start again.
- Select the column with all the data by clicking in the column header
- select the data tab on the tool bar ribbon
- Click Text to Columns
This will fire up the Convert to Text Columns Wizard and you can then specify the delimiter – job done 🙂
- PowerShell export-csv (clan8blog.wordpress.com)
- Importing and Exporting CSV and XML Files in PowerShell (windowsitpro.com)