Workbook.SaveAs Method (Excel) Excel by default will use a comma as the delimiter, but if you open up Control Panel – Region and Language, and then click the Additional settings button on the bottom Now look very closely at the “List separator” item, which normally has a comma in the field, but for the purposes of illustration I’ve switched it to a Pipe character. Obtaining Pipe Delimited Results from SQL Server using SSMS. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe.
1. Click the Windows 'Start' button in the computer's taskbar, and then click 'Control Panel' in the resulting menu. This opens a window.2. Click 'Region and Language' in the window that appears. The Region and Language window opens.
3. Click the 'Additional Settings' button in the Region and Language window. The Customize Format window appears.
4. Highlight the comma in the 'List separator' field. Press the 'Shift' key and the backslash key simultaneously on your keyboard. This will create a pipe (|).
![Save csv pipe delimited excel Save csv pipe delimited excel](/uploads/1/2/5/8/125840377/116201449.png)
5. Press the 'Apply' button, then 'OK' to close the Customize Format window. Press 'OK' to close the Region and Language window.
6. Double-click the Excel spreadsheet file you want to change to pipe delimited. This will open the file in Excel.
7. Click the 'File' tab and then click 'Save As' if you're using Word 2010. Click the 'Office' button and then click 'Save As' if you're using Word 2007. Click 'File' and then 'Save As' in the resulting window if you're using Word 2003. This opens the Save As window.
8. Type a name for your file in the 'File name' field, and then select 'CSV (comma delimited)' in the 'Save as type' drop-down menu. Click the 'Save' button when done. Since you set the machine's default to a pipe, it will save the delimited file with pipes instead of commas.
Problem
I made a PHP script to export a SQL query (from MySql) to a CSV file for opening in Excel. I cannot control how different users have their Excel setup, ie what separator delimiter they have set (this differs with language settings or user may have changed setting)
So the field separator delimiter gave me some problems, ie Excel would not automatically open file columns correctly but put each row in a single cell, unless you use Import data to make it work.
Some forums suggested setting the separator delimiter in the csv file (delimiter is third argument for fputcsv) to tab (t) but that did not help.
Solution (use csv header sep=)
Then I stumbled upon the possibility to add a parameter as a header to the actual csv file, simply add sep=<delimiter> to the beginning of your csv file and Excel will parse it. So for separator delimiter use comma (,), pipe (|), tab (t) or whatever you set in your php code.
However I found a few caveats:
- “sep=t” needs to be in quotes (otherwise it will use header but also for some reason also show it as data in the spreadsheet and put it in its own cell and move all other cells on that row one position to the right)
- You need to add newline n after the sep= header (otherwise it will use header but also show it as data concatenated with first cells data)
- Use fwrite as fputcsv will not work
Example to use tab as separator:
[code]fwrite($fp, 'sep=t'n');[/code]
I could not find much info on the subject of csv headers but this is the full thread that lead me on the to it (thanks Burhan Ali)
![Pipe Delimited Csv Excel Pipe Delimited Csv Excel](https://helpdesk.iconstituent.com/hc/en-us/article_attachments/201382394/Columns.png)
Tested with Excel 2016 on Windows 10 and Excel for Mac (15.25) on OSX El Capitan (10.11.15).
I have not tested on other spreadsheets software like OpenOffice, LibreOffice etc, so please share in the comments if you have any success with this.