|
|
Importing and Exporting Comma and Tab Delimited Files
From NeoWiki
(Difference between revisions)
| Revision as of 19:31, 20 April 2007 (edit) Jgd (Talk | contribs) ← Previous diff |
Current revision (21:37, 18 October 2010) (edit) (undo) Sardisson (Talk | contribs) (add a section on exporting fixed-width flat ASCII files) |
||
| (3 intermediate revisions not shown.) | |||
| Line 1: | Line 1: | ||
| - | + | The file type Text CSV (Comma Separated Values) separates data fields by using delimiters such as commas, tabs, etc. Using the CSV format in NeoOffice allows you to import the data of a text file into a spreadsheet (in order to use the document as a database, for example). Conversely, you can export the data of a spreadsheet to a CSV-formatted file for use in a text document. | |
| - | The file type Text CSV (Comma Separated Values) separates data fields by using delimiters such as commas, | + | |
| ==Importing a Text File into Calc== | ==Importing a Text File into Calc== | ||
| To import a file saved in <tt>.txt</tt> or <tt>.csv</tt> format, follow the steps below: | To import a file saved in <tt>.txt</tt> or <tt>.csv</tt> format, follow the steps below: | ||
| * In the {{menu|File}} menu, choose {{menu|Open…}}. | * In the {{menu|File}} menu, choose {{menu|Open…}}. | ||
| - | * In the dialog which opens, in the {{ | + | * In the dialog which opens, in the {{prefName|File type}} drop-down menu, choose {{prefName|Text CSV}}. |
| * Select the file you want to import and click {{button|Open}}. | * Select the file you want to import and click {{button|Open}}. | ||
| * In the {{window|Text Import}} dialog, select the row from which you want to import the text and the separator used in the file which you import, e. g. {{prefName|Tab}}. | * In the {{window|Text Import}} dialog, select the row from which you want to import the text and the separator used in the file which you import, e. g. {{prefName|Tab}}. | ||
| - | * In the {{section|Fields}} section, choose the {{prefName|Column type}} for each column. You can hide some columns | + | * In the {{section|Fields}} section, choose the {{prefName|Column type}} for each column. You can also hide some columns and they will not be imported. |
| * Click {{button|OK}}. | * Click {{button|OK}}. | ||
| - | '''N.B''' If the file has the <tt>.csv</tt> extension, you simply double-click it to display the | + | '''N.B''' If the file has the <tt>.csv</tt> extension, you can simply double-click it in the Finder to display the {{window|Text Import}} dialog. '''In order for import to work properly, a file must''' either have the <tt>.txt</tt> extension and be opened using the steps above, or have a <tt>.csv</tt> extension. If your file does not have a filename extension, or has an extension other than <tt>.txt</tt> or <tt>.csv</tt>, NeoOffice will not show the {{window|Text Import}} and will open the document as a text file in Writer instead. |
| - | ==Exporting a Spreadsheet | + | ==Exporting a Spreadsheet as a CSV File == |
| - | * In the spreadsheet you want to save as a Text CSV file, choose the {{menu|File}} menu, then the {{menu|Save | + | * In the spreadsheet you want to save as a Text CSV file, choose the {{menu|File}} menu, then the {{menu|Save As…}} item. |
| - | * A dialog opens. In the {{section|File type}} drop-down menu choose {{prefName| | + | * A dialog opens. In the {{section|File type}} drop-down menu choose {{prefName|Text CSV (.csv)}}. |
| * Select {{prefName|Edit filter settings}} | * Select {{prefName|Edit filter settings}} | ||
| - | * Give a name | + | * Give your document a name and choose the location where you want save it. Click {{button|Save}}. |
| - | * In the window where you are asked for confirmation, click {{button|Yes}} to save the file in Text CSV format. | + | <!-- * In the window where you are asked for confirmation, click {{button|Yes}} to save the file in Text CSV format. -- I don't see this confirmation window, so hiding --> |
| - | * In the {{window|Export of text files}} window, choose the {{ | + | * In the {{window|Export of text files}} window, choose the {{prefName|Character set}}, e.g. {{prefName|Unicode (UTF-8)}} and the {{prefName|Field delimiter}}, e. g. {{prefName|{Tab} }}. |
| * Click {{button|OK}}. | * Click {{button|OK}}. | ||
| - | * The file can be converted into <tt>.txt</tt> format by changing the extension in the Finder: select the file, then press {{key|Return}} or {{key|Enter}} and replace <tt>.csv</tt> with <tt>.txt</tt>. | + | * The file can be converted into <tt>.txt</tt> format by changing the extension in the Finder: select the file, then press {{key|Return}} or {{key|Enter}} and replace <tt>.csv</tt> with <tt>.txt</tt> (depending on your setting for displaying extensions in the Finder, you may have to select the file, choose {{menu|Get Info}} from the {{menu|File}} menu, expand the {{section|Name & Extension}} section, and modify the filename and/or uncheck the {{prefName|Hide extension}} checkbox). |
| + | ==Exporting a Spreadsheet as a Fixed-Width ASCII File== | ||
| + | For some scientific or mathematical applications, you may need to export data from a spreadsheet in a fixed-width ASCII format (also known as a "space-separated" format; Excel calls this format a “.prn†file). NeoOffice supports this, but rather than selecting a special file type, you must save the spreadsheet using the Text CSV format and set filter options. | ||
| - | {{botlangbarEN|[[Fr:Importer et Exporter des Fichiers Texte CSV dans Calc|Français]]}} | + | * Begin preparing the spreadsheet for export by first selecting all columns with data in them. |
| + | * Then choose {{menu|Optimal Width...}} from the {{menu|Columns}} sub-menu of the {{menu|Format}} menu. | ||
| + | * In the {{window|Optimal Column Width}} window, make sure the {{prefName|Default value}} checkbox is checked and that the {{prefName|Add}} field shows '''0.1"''' (or the equivalent value in metric units) if you want each “column†of data in the ASCII file to be separated by a single space. If you do not have at least '''0.1"''' (or the equivalent) added to the optimal column width, data from one “column†in the ASCII file will directly abut data from the next column. | ||
| + | * Now choose the {{menu|File}} menu, then the {{menu|Save As…}} item. | ||
| + | * A dialog opens. In the {{section|File type}} drop-down menu choose {{prefName|Text CSV (.csv)}}. | ||
| + | * Select {{prefName|Edit filter settings}} | ||
| + | * Give your document a name and choose the location where you want save it. Click {{button|Save}}. | ||
| + | <!-- * In the window where you are asked for confirmation, click {{button|Yes}} to save the file in Text CSV format. -- I don't see this confirmation window, so hiding --> | ||
| + | * In the {{window|Export of text files}} window, choose the {{prefName|Character set}}, e.g. {{prefName|Unicode (UTF-8)}}, and then check the {{prefName|Fixed column width}} checkbox. | ||
| + | * Click {{button|OK}}. | ||
| + | * The file can be converted into <tt>.prn</tt> format by changing the extension in the Finder: select the file, then press {{key|Return}} or {{key|Enter}} and replace <tt>.csv</tt> with <tt>.prn</tt> (depending on your setting for displaying extensions in the Finder, you may have to select the file, choose {{menu|Get Info}} from the {{menu|File}} menu, expand the {{section|Name & Extension}} section, and modify the filename and/or uncheck the {{prefName|Hide extension}} checkbox). | ||
| + | |||
| + | |||
| + | {{botlangbarEN|[[Fr:Importer et Exporter des Fichiers Texte CSV dans Calc|Français]] [[It:Importare_file_delimitati_da_virgole_o_tabulazioni|Italiano]]}} | ||
| [[Category:Tips and Hints]][[Category:NeoOffice]] | [[Category:Tips and Hints]][[Category:NeoOffice]] | ||
Current revision
The file type Text CSV (Comma Separated Values) separates data fields by using delimiters such as commas, tabs, etc. Using the CSV format in NeoOffice allows you to import the data of a text file into a spreadsheet (in order to use the document as a database, for example). Conversely, you can export the data of a spreadsheet to a CSV-formatted file for use in a text document.
Importing a Text File into Calc
To import a file saved in .txt or .csv format, follow the steps below:
- In the File menu, choose Open….
- In the dialog which opens, in the File type drop-down menu, choose Text CSV.
- Select the file you want to import and click Open.
- In the Text Import dialog, select the row from which you want to import the text and the separator used in the file which you import, e. g. Tab.
- In the Fields section, choose the Column type for each column. You can also hide some columns and they will not be imported.
- Click OK.
N.B If the file has the .csv extension, you can simply double-click it in the Finder to display the Text Import dialog. In order for import to work properly, a file must either have the .txt extension and be opened using the steps above, or have a .csv extension. If your file does not have a filename extension, or has an extension other than .txt or .csv, NeoOffice will not show the Text Import and will open the document as a text file in Writer instead.
Exporting a Spreadsheet as a CSV File
- In the spreadsheet you want to save as a Text CSV file, choose the File menu, then the Save As… item.
- A dialog opens. In the File type drop-down menu choose Text CSV (.csv).
- Select Edit filter settings
- Give your document a name and choose the location where you want save it. Click Save.
- In the Export of text files window, choose the Character set, e.g. Unicode (UTF-8) and the Field delimiter, e. g. {Tab} .
- Click OK.
- The file can be converted into .txt format by changing the extension in the Finder: select the file, then press Return or Enter and replace .csv with .txt (depending on your setting for displaying extensions in the Finder, you may have to select the file, choose Get Info from the File menu, expand the Name & Extension section, and modify the filename and/or uncheck the Hide extension checkbox).
Exporting a Spreadsheet as a Fixed-Width ASCII File
For some scientific or mathematical applications, you may need to export data from a spreadsheet in a fixed-width ASCII format (also known as a "space-separated" format; Excel calls this format a “.prn†file). NeoOffice supports this, but rather than selecting a special file type, you must save the spreadsheet using the Text CSV format and set filter options.
- Begin preparing the spreadsheet for export by first selecting all columns with data in them.
- Then choose Optimal Width... from the Columns sub-menu of the Format menu.
- In the Optimal Column Width window, make sure the Default value checkbox is checked and that the Add field shows 0.1" (or the equivalent value in metric units) if you want each “column†of data in the ASCII file to be separated by a single space. If you do not have at least 0.1" (or the equivalent) added to the optimal column width, data from one “column†in the ASCII file will directly abut data from the next column.
- Now choose the File menu, then the Save As… item.
- A dialog opens. In the File type drop-down menu choose Text CSV (.csv).
- Select Edit filter settings
- Give your document a name and choose the location where you want save it. Click Save.
- In the Export of text files window, choose the Character set, e.g. Unicode (UTF-8), and then check the Fixed column width checkbox.
- Click OK.
- The file can be converted into .prn format by changing the extension in the Finder: select the file, then press Return or Enter and replace .csv with .prn (depending on your setting for displaying extensions in the Finder, you may have to select the file, choose Get Info from the File menu, expand the Name & Extension section, and modify the filename and/or uncheck the Hide extension checkbox).
