View source for MakeCSV

[[Category:Text Functions]]
''new to [[Analytica 5.0]]''

== MakeCSV(a, columnIndex, rowIndex'', columnHeaders, rowHeaders, separator, quoteFlags, dateTemplate, decimal, quote'') ==

Makes CSV-formatted text from a two-dimensional array «a» which is indexed by «columnIndex» and «rowIndex». CSV stands for "Comma separated value", but may use a separator other than a comma in practice.  Each row is separated in the result by a new-line (CR), and within a row each item is separated by a comma by default, or by «separator».

[[Null]] values are blank, with no space between the leading and separators.

=== Row and Column Headers ===
A first line can be optionally included containing the column names. If you want a column header line, specify the names of the columns to «columnHeaders». The value you pass must be indexed by «columnIndex». When your column index contains the labels you want to use, simple pass that same index to «columnHeaders». If you omit «columnHeaders», the first line contains the first row of data.

You can optionally prepend a first column containing row headers. To do so, specify the row header labels or values in the «rowHeaders» parameter. The value you pass must be indexed by «rowIndex». When your row index already contains the row headers, pass the same row index for «rowHeaders».

=== Changing the field separator ===
To use a separator other than comma, specify it in the «separator» parameter. «Separator» can contain multiple characters.

A commonly used separator is the tab-character, which you can specify as <code>Chr(9)</code>, or you can type a tab character in a definition with the three keystrokes quote-tab-quote.

In some countries and regions that use comma as the decimal point for numbers, Excel does not recognize the comma as a separator when you try to read in a CSV file. You may need to specify a semi-colon separator in these cases, or the Excel user can read in the CSV file and then convert it from text to columns in a separate step after he loads it into Excel.

=== How values are quoted ===
By default, numbers and dates are not quoted, text is always quoted, and text value that would parse as numbers or dates are escape quoted with an equal sign followed by quotes, like <code>="0012"</code> or <code>="1/2/3"</code>.

The «quoteFlags» parameter is a bit-field, where you can add any of the following flags together.
* <code>1</code> = No quotes around text (except when required)
* <code>2</code> = No escape when text parses as number
* <code>4</code> = No escape when text parses as date
* <code>8</code> = Quotes around numbers
* <code>16</code> = Quotes around dates

Quotes are required whenever the text contains a newline or separator character. Inside quoted text, if a quote appears within the text, it is doubled, so for example text with one quote in the middle appears like <code>"one "" inside"</code>.

=== dateTemplate ===

The «dateTemplate» controls how date-time numbers are written. To control the format of date-time numbers, you can specify a template using the same format used for a custom date format in the [[Number Format Dialog]]. For example, a «dateTemplate» of "dd/MM/yyyy" would write <code>25/05/2016</code>.

The default date template is "SHORT¿ TIME?", which is the regional short date format when there is no time component (e.g., <code>5/13/2016</code> in the US, <code>13/05/2016</code> in Argentina) or the regional short date format followed by the long time format when there is a time component (e.g, <code>5/13/2016 03:38:01 p.m.</code>). Regional formats are set in the '''Region and Language''' dialog in the Windows Control Panel.

When there is both a date and a time component, experimentation has shown that Excel reads in the date part, but drops the time part. This seems to be an inherent limitation of Excel -- we have not found any date-time format or combination of quoting that works around this limitation.

=== Changing the decimal point character ===

The «decimal» parameter specifies the character used to separate the whole number from the fractional part of a number. When not specified, this defaults to the standard convention in the United States of using a dot, e.g., <code>3.14</code>. To use a comma, use <code>MakeCSV(..., separator:';', decimal:',')</code>, which writes <code>3,14</code>. [[MakeCSV]] does not write thousand separators within numbers. When you change the «decimal» to comma, it is recommended that you use a «separator» other than comma, such as a semi-colon.

=== Quote character ===

By default, the double quote character (<code>"</code>) is used. You can specify a different character to use in the «quote» parameter. For example, to use single quotes, use 
<pre style="background:white; border:white;margin-left: 1em;">MakeCSV(..., quote:'''')</pre> 

The value here is typed as four single quotes, which is one way to type a text literal in Analytica consisting of a single character, which happens to be the single quote character. (Another way to type it is doubleQuote - singleQuote - doubleQuote).

== Examples ==

Create a text value in standard CSV form from an array <code>Salary</code> indexed by <code>Person</code> and <code>Time</code>:
:<code>MakeCSV(Salary, Time, Person)</code>

This puts each person on a separate line, and the columns across correspond to [[Time]]. There are so row or column headers, so the first line of data is the first person. Here is a sampling (with 5 Time points and 3 Persons):

<pre style="background:white; border:white;margin-left: 1em;">
45600, 46500, 48800, 51000, 52000
,,71000, 73000, 77500
120000, 120000, 125000, 125000, 125000
</pre>

To write the same data to a csv file, use
:<code>WriteTextFile("salary data.csv", MakeCSV(Salary, Time, Person))</code>

To include the person as the first field on each line, and the Time for each column on the first line, use
:<code>MakeCSV(Salary, Time, Person, columnHeaders: Time, rowHeaders: Person) &rarr;</code>
<pre style="background:white; border:white;margin-left: 1em;">
"Person", 2011, 2012, 2013, 2014, 2015
"00001", 45600, 46500, 48800, 51000, 52000
"00002",,, 71000, 73000, 77500
"00003", 120000, 120000, 125000, 125000, 125000
</pre>

In the CSV here, the <code>Person</code> index contains textual ID numbers with leading zeros.

Assuming <code>FirstName</code> and <code>LastName</code> are indexed by <code>Person</code>, full names can be used for the row header by using
:<code>MakeCSV(Salary, Time, Person, columnHeaders: Time, rowHeaders: FirstName & ' ' & LastName) &rarr;</code>
<pre style="background:white; border:white;margin-left: 1em;">
"Person", 2011, 2012, 2013, 2014, 2015
"Alice Zen", 45600, 46500, 48800, 51000, 52000
"Bob Yarbough",,, 71000, 73000, 77500
"Carol Xian", 120000, 120000, 125000, 125000, 125000
</pre>

== See Also ==

* [[ParseCSV]]
* [[JoinText]]
* [[NumberToText]]
* [[WriteTextFile]]
* [[Chr]]