Repository files navigation

This extension adds support for theTEXT function for formatting numbers from Microsoft Excel.

Example usage:

SELECTtext(1234567.897,'$#.##')AS result;┌────────────┐│   result   ││varchar   │├────────────┤│ $1234567.9 │└────────────┘

See theExcel page in the DuckDB documentation.

.xlsx files can be read using theread_xlsx function. The following named parameters are supported.

Options:

Example usage:

SELECT*FROM read_xlsx('test.xlsx', header'true');----┌────────┬────────┐│   a    │   b    ││ double │ double │├────────┼────────┤│1.0 │2.0 ││3.0 │4.0 │└────────┴────────┘-- Alternatively, we can use a xlsx file as a "replacement scans" and select from it immediately-- but without being able to pass options.SELECT*FROM'test.xlsx';----┌────────┬────────┐│   a    │   b    ││ double │ double │├────────┼────────┤│1.0 │2.0 ││3.0 │4.0 │└────────┴────────┘

Writing.xlsx files is supported using theCOPY statement withXLSX given as the format. The following additional parameters are supported.

Options:

Example usage:

CREATETABLEtestASSELECT*FROM (VALUES (1,2), (3,4))AS t(a, b);COPY test TO'test.xlsx' (format'xlsx', header'true');

Because XLSX files only really support storing strings and numbers, the equivalent ofVARCHAR andDOUBLE, the following type conversions are applied when writing XLSX files.

• Numeric types are cast toDOUBLE when writing to an XLSX file.
• Temporal types (TIMESTAMP,DATE,TIME, etc.) are converted to excel "serial" numbers, that is the number of days since 1900-01-01 for dates and the fraction of a day for times. These are then styled with a "number format" so that they appear as dates or times in Excel.
• TIMESTAMP_TZ andTIME_TZ are cast to UTCTIMESTAMP andTIME respectively, with the timezone information being lost.
• BOOLEANs are converted to1 and0, with a "number format" applied to make them appear asTRUE andFALSE in Excel.
• All other types are cast toVARCHAR and then written as text cells.

When reading XLSX files, almost everything is read as eitherDOUBLE orVARCHAR depending on the Excel cell type. However, there are some caveats.

• We try to inferTIMESTAMP,TIME,DATE andBOOLEAN types when possible based on the cell format.
• We infer text cells containingTRUE andFALSE asBOOLEAN, but that is the only type-inference we do that is based on the actual content of the cell.
• Empty cells are considered to beDOUBLE by default, unless theempty_as_varchar option is set totrue, in which case they are typed asVARCHAR.

If theall_varchar option is set totrue, none of the above applies and all cells are read asVARCHAR.

When no types are specified explicitly, (e.g. when using theread_xlsx function instead ofCOPY TO ... FROM '<file>.xlsx')the types of the resulting columns are inferred based on the first "data" row in the sheet, that is:

• If no explicit range is given
  • The first row after the header if a header is found or forced by theheader option
  • The first non-empty row in the sheet if no header is found or forced
• If an explicit range is given
  • The second row of the range if a header is found in the first row or forced by theheader option
  • The first row of the range if no header is found or forced

This can sometimes lead to issues if the first "data row" is not representative of the rest of the sheet (e.g. it contains empty cells) in which case theignore_errors orempty_as_varchar options can be used to work around this.Alternatively, when theCOPY TO ... FROM '<file>.xlsx' syntax is used, no type inference is done and the types of the resulting columns are determined by the types of the columns in the table being copied to. All cells will simply be converted by casting fromDOUBLE orVARCHAR to the target column type.