<?xml version="1.0" encoding="UTF-8"?>
<helpdocument version="1.0">
<!--
* This file is part of the LibreOffice project.
*
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
* file, You can obtain one at http://mozilla.org/MPL/2.0/.
*
-->
<meta>
<topic id="csvparams" indexer="include" status="PUBLISH">
<title id="tit" xml-lang="en-US">CSV Filter parameters</title>
<filename>/text/shared/guide/csv_params.xhp</filename>
</topic>
</meta>
<body>
<bookmark xml-lang="en-US" branch="index" id="bm_id181634740978601">
<bookmark_value>CSV;filter options</bookmark_value>
<bookmark_value>CSV;separator specification line</bookmark_value>
<bookmark_value>CSV;import options</bookmark_value>
<bookmark_value>CSV;export options</bookmark_value>
<bookmark_value>CSV;command line filter options</bookmark_value>
</bookmark>
<section id="csv_params_Head">
<h1 id="hd_id551634734576194"><variable id="csv_params_h1"><link href="text/shared/guide/csv_params.xhp">CSV Filter Options</link></variable></h1>
<paragraph id="par_id401634734576197" role="paragraph" xml-lang="en-US">The CSV filter accepts an option string containing five to fifteen tokens, separated by commas. Tokens 6 to 15 are optional.</paragraph>
</section>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<paragraph role="paragraph" id="par_id431634743318433">Import from UTF-8, Language German, Comma separated, Text delimiter ", Quoted field as text. CSV file has columns formatted as date, number, number, number:</paragraph>
<paragraph role="paragraph" localize="false" id="par_id331634746049789"><literal>soffice --infilter="Text - txt - csv (StarCalc):44,34,76,1,1/5/2/1/3/1/4/1,1031,true,true" test.csv</literal></paragraph>
<paragraph role="paragraph" id="par_id281634743298078">Export to Windows-1252, Field delimiter : comma, Text delimiter : quote, Save cell contents as shown:</paragraph>
<paragraph role="paragraph" localize="false" id="par_id411634746578360"><literal>soffice --convert-to "csv:Text - txt - csv (StarCalc):44,34,ANSI,1,,0,false,true,true" --outdir=/home/user test.ods</literal></paragraph>
<table id="tab_id391634735255956">
<tablerow>
<tablecell>
<paragraph id="par_id511634735255956" role="tablehead">Token Position</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id71634735255956" role="tablehead">Definition</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id581634735255956" role="tablehead">Meaning and Example of Token</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id351634735255956" localize="false" role="tablecontent">1</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id691634735255956" role="tablecontent">Field Separator</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id501634735255956" role="tablecontent">Field separator(s) as ASCII values. Multiple values are separated by the slash sign ("/"), that is, if the values are separated by semicolons and horizontal tabulators, the token would be 59/9. To treat several consecutive separators as one, then append '/MRG' to the token. If the file contains fixed width fields, then use 'FIX'. Example: 44 (,)</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id471634735403557" localize="false" role="tablecontent">2</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id661634735416764" role="tablecontent">Text Delimiter</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id131634735421911" role="tablecontent">The text delimiter as ASCII value, like 34 for double quotes and 39 for single quotes. Example: 34 (").</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id11634735622679" localize="false" role="tablecontent">3</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id901634735627024" role="tablecontent">Character Set</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id871634735631362" role="tablecontent">The character set code used in the file as described in the table below. Example: 0 (System).</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id991634735701128" localize="false" role="tablecontent">4</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id631656948569749">Line number to start reading.</paragraph>
<paragraph id="par_id371634735705688" role="tablecontent">CSV Import</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id681634735710417" role="tablecontent"><emph>N</emph>: line number to start reading. Example: 3 (start from third line).</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id501634735814198" localize="false" role="tablecontent">5</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id741634735821982" role="tablecontent">Cell Format Codes for Each Column</paragraph>
<paragraph role="paragraph" id="par_id591656948484541">CSV Import</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id481634735825359" role="tablecontent">A sequence of column/formatting code, where the formatting code is given in the table below. Example: "1/5/2/1/3/1/4/1".</paragraph>
<paragraph id="par_id831634735631362" role="tablecontent">If value separators are used, the form of this token is column/format[/column/format/…] where column is the number of the column, with 1 being the leftmost column. The format code is detailed below.</paragraph>
<paragraph id="par_id831635735631362" role="tablecontent">If the first token is FIX it has the form start/format[/start/format/…], where start is the number of the first character for this field, with 0 being the leftmost character in a line. The format is explained below.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id411634736837462" localize="false" role="tablecontent">6</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id971634736857464" role="tablecontent">Language identifier</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id951634736861475" role="tablecontent">String expressed in decimal notation. This token is the equivalent of the "Language" listbox in the user interface for CSV import. If the value is 0 or omitted, the language identifier of the user interface is used. The language identifier is based on the Microsoft language identifiers.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id501634736905190" localize="false" role="tablecontent">7</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id41656949386421">Quoted field as text</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id481634736922278" role="tablecontent">String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>false</literal>. This token is the equivalent of the check box "Quoted field as text".</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id971634737041223" localize="false" role="tablecontent">8</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id741656949088694">Detect special numbers</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id41634737061097" role="tablecontent"><emph>Import:</emph> String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>false</literal>. This token is the equivalent of the check box "Detect special numbers".</paragraph>
<paragraph role="tablecontent" id="par_id161634737264744"><emph>Export:</emph> String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>true</literal>. This token has no UI equivalent. If <literal>true</literal>, the number cells are stored as numbers. If <literal>false</literal>, the numbers are stored as text, with text delimiters.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id381634737684396" localize="false" role="tablecontent">9</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id321656948708285">Save cell contents as shown</paragraph>
<paragraph id="par_id961634737712752" role="tablecontent">CSV Export</paragraph>
</tablecell>
<tablecell>
<paragraph role="tablecontent" id="par_id701634737971414">String, either <literal>false</literal> or <literal>true</literal>. Default value:<literal>true</literal>. This token is the equivalent of the check box "Save cell contents as shown".</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id651634896749838" localize="false" role="tablecontent">10</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id601656948754932">Export cell formulas</paragraph>
<paragraph id="par_id481634896761359" role="tablecontent">CSV Export</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id411634896764659" role="tablecontent">String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>false</literal>. Export cell formulas.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id421634896890527" localize="false" role="tablecontent">11</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id921656948771058">Remove spaces</paragraph>
<paragraph id="par_id221634896896383" role="tablecontent">CSV Import</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id641634896897119" role="tablecontent">String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>false</literal>. Remove spaces. Trim leading and trailing spaces, when reading the file.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id881634896966734" localize="false" role="tablecontent">12</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id501656949158133">Export sheets</paragraph>
<paragraph id="par_id521634896971296" role="tablecontent">CSV Export</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id161634896971802" role="tablecontent">Export the entire document to individual sheets .csv files or a specified sheet.</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id341634897309489" role="listitem"><emph>0</emph> or absent: means the default behaviour, first sheet from command line, or current sheet in macro filter options, exported to sample.csv</paragraph>
</listitem>
<listitem>
<paragraph id="par_id381634897377753" role="listitem"><emph>-1</emph>: for all sheets, each sheet is exported to an individual file of the base file name concatenated with the sheet name, for example sample-Sheet1.csv, sample-Sheet2.csv and sample-Sheet3.csv</paragraph>
</listitem>
<listitem>
<paragraph id="par_id531634897438255" role="listitem"><emph>N</emph>: export the N-th sheet within the range of number of sheets. Example: to export the second sheet, set 2 here to get sample-Sheet2.csv</paragraph>
</listitem>
</list>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id131635293273891" localize="false" role="tablecontent" xml-lang="en-US">13</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id61656948865298">Import as formulas</paragraph>
<paragraph id="par_id451635293273892" role="tablecontent" xml-lang="en-US">CSV Import</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id701635293273893" role="tablecontent" xml-lang="en-US">String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>false</literal>. Determines whether formula expressions starting with a = equal sign character are to be evaluated as formulas or imported as textual data. If <literal>true</literal> evaluate formulas on input. If <literal>false</literal> formulas are input as text. If omitted (not present at all), the default value is <literal>true</literal> to keep the behaviour of old versions' options string that didn't have this token at all. If present and empty (or any other value than <literal>true</literal>) the default value is <literal>false</literal>.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id541678874653434" localize="false" role="tablecontent" xml-lang="en-US">14</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id221678874688127">Include a byte-order-mark (BOM)</paragraph>
<paragraph id="par_id451635293273893" role="tablecontent" xml-lang="en-US">CSV Export</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id41678874718368" role="tablecontent" xml-lang="en-US">String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>false</literal>. If <literal>true</literal> include a byte-order-mark (BOM) in the export. If <literal>false</literal> the export does not include a BOM. If omitted (not present at all), the default value is <literal>false</literal> to keep the behaviour of old versions' options string that didn't have this token at all. If present and empty (or any other value than <literal>true</literal>) the default value is <literal>false</literal>. Automatically detected during the import.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id541678874653433" localize="false" role="tablecontent" xml-lang="en-US">15</paragraph>
</tablecell>
<tablecell>
<paragraph role="paragraph" id="par_id221678874688126">Detect numbers in scientific notation</paragraph>
<paragraph id="par_id451635293273894" role="tablecontent" xml-lang="en-US">CSV Import</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id41678874718367" role="tablecontent" xml-lang="en-US">String, either <literal>false</literal> or <literal>true</literal>. Default value: <literal>true</literal>. If <literal>true</literal> detect if a cell content containing an 'E' or 'e' is a number in scientific notation. If <literal>false</literal> do not try to detect numbers in scientific notation. Token can be <literal>false</literal> only if token 8 (Detect special numbers) is <literal>false</literal>. If omitted, the default value is <literal>true</literal> to keep the behaviour of old versions' options string that didn't have this token at all.</paragraph>
</tablecell>
</tablerow>
</table>
<h2 id="hd_id591638374883162">Special case of CSV files with separator defined in the first line</h2>
<paragraph role="paragraph" id="par_id781638374952502">CSV import and export support a <literal>sep=</literal> and <literal>"sep="</literal> field separator setting. When reading a CSV document, the separator is taken from the initial <literal>sep=</literal> or <literal>"sep="</literal> single field, if that is the only line content. </paragraph>
<paragraph role="paragraph" id="par_id561638377619263">When reading a CSV file, the quoted form is preserved as (unquoted) cell content. You see <literal>sep=|</literal> when | is the separator in the first line. In the unquoted form, the separator is discarded because it is a real field separator in the context. You see <literal>sep=</literal> in the first line.</paragraph>
<paragraph role="paragraph" id="par_id761638377626465">When writing a CSV file, the existing single top left cell's content such as <literal>sep=|</literal> is adapted to the current separator with the quoted form of <literal>"sep=|"</literal> (if quotes / text delimiters aren't set empty and | is the separator) and always uses the ASCII " double quote character.</paragraph>
<paragraph role="paragraph" id="par_id61638377631743">If the line containing the <literal>sep=|</literal> is not to be imported as data, remember to set the <emph>From row</emph> number in the dialog to 2. Note that this line will not be preserved when re-saving.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<bascode>
<paragraph role="bascode" id="par_id371638374806568" xml-lang="en-US" localize="false">sep=|</paragraph>
<paragraph role="bascode" id="par_id731638374814029" xml-lang="en-US">"LETTER"|"ANIMAL"</paragraph>
<paragraph role="bascode" id="par_id801638374818291" xml-lang="en-US">"a"|"aardvark"</paragraph>
<paragraph role="bascode" id="par_id621638374822275" xml-lang="en-US">"b"|"bear"</paragraph>
<paragraph role="bascode" id="par_id851638374831208" xml-lang="en-US">"c"|"cow"</paragraph>
</bascode>
<h2 id="hd_id181634739011588">Formatting Codes for Token 5</h2>
<table id="tab_id491634738948892">
<tablerow>
<tablecell>
<paragraph id="par_id31634738948892" role="tablehead">Meaning</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id101634738948892" role="tablehead">Code</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id1011670216">Standard</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id6835787852">1</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id1605952714">Text</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id5487051433">2</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id5066036143">MM/DD/YY</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id5817743300">3</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id6386378851">DD/MM/YY</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id3144990816">4</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id6847541095">YY/MM/DD</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id6717497377">5</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id5281422501">-</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id7094333156">6</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id9997403722">-</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id7314932752">7</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id3739500102">-</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id6512617577">8</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id7881263433">Ignore field (do not import)</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id9786657086">9</paragraph></tablecell>
</tablerow>
<tablerow>
<tablecell><paragraph role="tablecontent" id="par_id6920129719">US-English</paragraph></tablecell>
<tablecell><paragraph role="tablecontent" localize="false" id="par_id2181849223">10</paragraph></tablecell>
</tablerow>
</table>
<h2 id="hd_id591634740467955">Character Set Codes for Token 3</h2>
<embed href="text/shared/guide/lotusdbasediff.xhp#charsetcodes"/>
<section id="relatedtopics">
<embed href="text/shared/guide/convertfilters.xhp#convertfilters_h1"/>
<embed href="text/shared/guide/start_parameters.xhp#Title_h1"/>
<embed href="text/shared/00/00000208.xhp#TextImporth1"/>
</section>
</body>
</helpdocument>