MakeCSV

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

By default, it generates no column headers: The first line contains the first row of data. To include column headers, use parameter «columnHeaders» and pass it the «columnIndex» (or a vector indexed by «columnIndex» if you want to change the headers from those in «columnIndex».)

You can also prepend a first column containing row headers with the «rowHeaders» parameter. The value you pass could be the «rowIndex» (or a vector indexed by «rowIndex» if you want to change the headers from those in «rowIndex».)

Changing the field separator

By default, it uses comma to separate fields (columns). You can specify a different character (or sequence of characters) with the «separator» parameter.

A common separator is the tab character, which you can specify as Chr(9). You can also type a tab character in a definition as the three keystrokes quote-tab-quote.

Some countries and regions use ',' rather than '.' as the decimal point for numbers. In those regions, Excel does not recognize the comma as a separator in a CSV file. In such cases, you could use ';' or another character as the separator when generating a csv file. Alternatively, you could read the CSV file into Excel with each row in a single cell, and then using Excel code to split the text to columns.

How values are quoted

By default, it puts quotes around text and no quotes around numbers and dates. For a text value that can parse as a number or date, it puts an "=" sign before the text. For example, "0012" becomes ="0012" or "1/2/3" becomes ="1/2/3". This follows a quirk of Microsoft Excel, which, without a "=", converts such text strings into a number or date.

You can override these defaults with the «quoteFlags» parameter. It is a bit field so you can add any of these flags together to combine their effects:

1: No quotes around text (except when required because the text contains a field separator character (default ',') or newsline character)
2: No "=" before a text that parses as number
4: No "=" before a text that parses as date
8: Put quotes around numbers
16: Put quotes around dates

If a quote appears inside quoted text, it doubles the quote. For example text "Say "Hello" "becomes "Say ""Hello"" ". Microsoft Excel and Analytica treat the double quote as a single quote inside the text string when parsing it.

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 25/05/2016.

The default date template is "SHORT¿ TIME?", which is the regional short date format when there is no time component (e.g., 5/13/2016 in the US, 13/05/2016 in Argentina) or the regional short date format followed by the long time format when there is a time component (e.g, 5/13/2016 03:38:01 p.m.). 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., 3.14. To use a comma, use MakeCSV(..., separator:';', decimal:','), which writes 3,14. 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 (") is used. You can specify a different character to use in the «quote» parameter. For example, to use single quotes, use

MakeCSV(..., quote:'''')

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 Salary indexed by Person and Time:

MakeCSV(Salary, Time, Person)

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):

45600, 46500, 48800, 51000, 52000
,,71000, 73000, 77500
120000, 120000, 125000, 125000, 125000

To write the same data to a csv file, use

WriteTextFile("salary data.csv", MakeCSV(Salary, Time, Person))

To include the person as the first field on each line, and the Time for each column on the first line, use

MakeCSV(Salary, Time, Person, columnHeaders: Time, rowHeaders: Person) →

"Person", 2011, 2012, 2013, 2014, 2015
"00001", 45600, 46500, 48800, 51000, 52000
"00002",,, 71000, 73000, 77500
"00003", 120000, 120000, 125000, 125000, 125000

In the CSV here, the Person index contains textual ID numbers with leading zeros.

Assuming FirstName and LastName are indexed by Person, full names can be used for the row header by using

MakeCSV(Salary, Time, Person, columnHeaders: Time, rowHeaders: FirstName & ' ' & LastName) →

"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