If you have data saved in an Excel spreadsheet, you can export it to a structured format for use in other programs. The export options in Excel include exporting to CSV (Comma Separated Values) files. By default, in a CSV file, the data values are separated, or delimited, by the comma character. However, you can alter the settings in your Windows system, either temporarily or permanently, to use the pipe character instead. Delimited data files can be useful if you are planning to import your spreadsheet data into another application such as a database.
Open the regional and language settings for for your system. Before you attempt the export operation, first alter the default character that Windows uses to separate list items. Open the Control Panel by pointing at the top right corner of your screen, moving the mouse downwards and clicking "Search." Enter "Control Panel" then select "Apps" and "Control Panel."
Change the default delimiter character for the system. In the Control Panel, browse to the language settings by selecting "Clock, Language and Region" then "Region and Language." Browse to the Regional Settings and select "Additional Settings." Select the "Numbers" tab and enter the pipe character as the new list separator instead of the default comma character. Apply your settings by clicking "OK."
Export your data. Open your spreadsheet in Excel. Any data you export as a CSV file should now use the pipe character instead of the comma. Save the file as a CSV by selecting "File" then "Save As." Select "Computer" in the Places section and choose a location such as your Documents folder. Select "CSV (comma delimited)" in the "Save As type" list to set the output format. Choose a name and location for your exported data file, clicking "Save" to export the data. Click "OK" if prompted that only the current worksheet will be output. Click "Yes" if prompted that certain features may not be supported by your chosen output format.
Check your data. Open the exported file in a text editor such as Notepad. Have a look through the data to make sure that the pipe character has been used instead of the comma and that your data is structured the way you need it to be. If it is, you can go ahead and use it in other applications as necessary.
Alter your settings. Unless you want to set the default delimiter character to the pipe character permanently, you should go back into your settings and change it back to the comma character, which is standard for many uses. Browse back to the regional and language settings for your system and change the pipe character you entered to the comma.
Many applications such as databases can import data values separated by a delimiter character of your choice, allowing you to make use of the data in a variety of web and software programs.
Depending on the content of your Excel spreadsheet, it may not be possible to export all of the data to a pipe delimited CSV file.
Tips and warnings
- Many applications such as databases can import data values separated by a delimiter character of your choice, allowing you to make use of the data in a variety of web and software programs.
- Depending on the content of your Excel spreadsheet, it may not be possible to export all of the data to a pipe delimited CSV file.