Csv.Document

Syntax

 
Csv.Document(source as any, optional columns as any, optional delimiter as any, optional extraValues as nullable number, optional encoding as nullable number) as table

About

Returns the contents of the CSV document as a table.

  • columns can be null, the number of columns, a list of column names, a table type, or an options record.
  • delimiter can be a single character, a list of characters, or the value "", which indicates rows should be split by consecutive whitespace characters. Default: ",".
  • Refer to ExtraValues.Type for the supported values of extraValues.
  • encoding specifies the text encoding type.

If a record is specified for columns (and delimiter, extraValues, and encoding are null), the following record fields may be provided:

  • Delimiter: A single character column delimiter. Default: ",".
  • Columns: Can be null, the number of columns, a list of column names, or a table type. If the number of columns is lower than the number found in the input, the additional columns will be ignored. If the number of columns is higher than the number found in the input, the additional columns will be null. When not specified, the number of columns will be determined by what is found in the input.
  • Encoding: The text encoding of the file. Default: 65001 (UTF-8).
  • CsvStyle: Specifies how quotes are handled.
  • QuoteStyle: Specifies how quoted line breaks are handled.
    • QuoteStyle.Csv (default): Quoted line breaks are treated as part of the data, not as the end of the current row.
    • QuoteStyle.None: All line breaks are treated as the end of the current row, even when they occur inside a quoted value.

Example 1

Process CSV text with column headers.

Usage

let
    csv = Text.Combine({"OrderID,Item", "1,Fishing rod", "2,1 lb. worms"}, "#(cr)#(lf)")
in
    Table.PromoteHeaders(Csv.Document(csv))

Output

Table.FromRecords({
    [OrderID = "1", Item = "Fishing rod"],
    [OrderID = "2", Item = "1 lb. worms"]
})

Example 2

Process CSV text with multiple delimiter characters. In this example, the third parameter specifies the delimiter pattern #|# to use instead of the default.

Usage

let
    csv = Text.Combine({"OrderID#|#Color", "1#|#Red", "2#|#Blue"}, "#(cr)#(lf)")
in
    Table.PromoteHeaders(Csv.Document(csv, null, "#|#"))

Output

Table.FromRecords({
    [OrderID = "1", Color = "Red"],
    [OrderID = "2", Color = "Blue"]
})