csvToQuery

Returns a CFML query object from CSV data (either a string or a file containing CSV data).

csvToQuery( csv, filepath[, firstRowIsHeader[, trim[, delimiter[, queryColumnNames[, queryColumnTypes[, makeColumnNamesSafe ] ] ] ] ] ] )

NOTE: If you are reading from a large CSV file you should consider using readCsv() which is much less memory/CPU intensive.

Required arguments

• csv string: the data in CSV format OR
• filepath string: full file system path of a file containing CSV data

Optional arguments

• firstRowIsHeader boolean default=false: whether the first line of the CSV contains the column names to use in the query
• trim boolean default=true: whether white space should be removed from the beginning and end of the CSV string (usually desirable to prevent blank rows being added to the end of the query)
• delimiter string default=",": the single delimiter used in the CSV to separate the fields. For tab delimited data, use \t or tab or #Chr( 9 )#.
• queryColumnNames array: the names to use for the query columns in the order the columns appear in the spreadsheet. Note that specifying queryColumnNames overrides the use of firstRowIsHeader.
• queryColumnTypes string or struct: the column types to use in the generated query (see below for details).
• makeColumnNamesSafe boolean default=false: ensure that the CSV column names are safe for use in the resulting query (duplicate free and valid CFML variable names).

Chainable? No.

Specifying query column types

If you wish to specify column types for the query you can use the optional queryColumnTypes argument in one of four ways:

1. Pass the list of types you want in exactly the same way as you would with the CFML function QueryNew()
2. Pass a single type to apply to all columns. Normally you would specify VARCHAR.
3. Specify auto to have the types auto-detected from the values in each spreadsheet column. If they are all one type, that will be used. If they are mixed, VARCHAR will be used. Blank cells are ignored. Note that auto-detection only supports the following types: VARCHAR, DOUBLE (numeric values) and TIMESTAMP (date or datetime values). Any other types will be set to VARCHAR. Be aware also that this option has a performance overhead which may be significant on large sheets.
4. Pass a struct which maps each column name to the desired type. Use the column name as they key and the type as the value (see example below). The names/values don't have to be in the same order as they appear in the sheet. Note that you must also either set firstRowIsHeader to true, or supply the queryColumnNames argument (see above). If a header/column is not found in the struct its type will default to VARCHAR.

Related

queryToCsv(), readCsv(), writeCsv()

Example

spreadsheet = New spreadsheet();
savecontent variable="csv"{
  WriteOutput( '
Name,Phone
"Frumpo McNugget",12345
' );
};
data = spreadsheet.csvToQuery( csv=csv, firstRowIsHeader=true );

Convert a csv into a query and specify the column types as a struct

savecontent variable="csv"{
  WriteOutput( '
Name,Phone
"Frumpo McNugget",12345
' );
};
columnTypes = { "Name": "VARCHAR", "Phone": "INTEGER" };
query = spreadsheet.csvToQuery( csv=csv, queryColumnTypes=columnTypes );