Transformers, FormatDataAsArray and string to DATE

App Building
,
1

So I have a transformer on a Snowflake SQL view returning data as shown below. The issue I have is I need the transformer to also convert those two columns that are dates from string to dates. They are actually dates in Snowflake, but somehow the transformer is bringing them in as string. Also, these are very large datasets, so I do not put them in a table, I simply export the transformed data to Excel using the XLSX add-in. Is there a reference on how to format a subset of the columns into a different format as part of the transformation?

image
image1920×1032 213 KB

2

Hey @Orennia!

It looks as though SheetJS has some specific methods of handling dates. Am I correct in thinking that's the extension you're using? If so, can you try passing { cellDates: true } as an option to your XLSX reader? (docs here)

Let me know if that works or if I'm totally off-base :sweat_smile:

3

Hey thanks for the reply, but I am actually using these:

https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.2/xlsx.min.js
https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js

I don't actually export column by column so adding that in didn't seem to affect it at all. Is there literature on how to export columns from a query explicitly into a sheet?

4

I believe the cdnjs xlsx library is the same as SheetJS (that's at least the GitHub repo it links to from this page). Could you maybe share the code you're using to export the data so I can take a look at what methods you're using specifically?

5

Yep here’s the bit of code I use to export the results of the query.


// Resource Unit Dates ###########################################################
      if (checkboxGroupExportsResources.value[i] == 'SELECT_RESOURCE_UNIT_DATES') {
        await SELECT_RESOURCE_UNIT_DATES.trigger({
          // You can use the argument to get the data with the onSuccess function
          onSuccess: function(data) {
            console.log('SELECT_RESOURCE_UNIT_DATES executed successfully!');
            const data_RESOURCE_UNIT_DATES = SELECT_RESOURCE_UNIT_DATES.data;
            const sheet_RESOURCE_UNIT_DATES = XLSX.utils.json_to_sheet(data_RESOURCE_UNIT_DATES, { cellDates: true });
            XLSX.utils.book_append_sheet(workbook, sheet_RESOURCE_UNIT_DATES, "Resource Unit Dates");
6

I see, it looks like the XLSX library can recognize date columns as long as they are Date() objects. You might try something like data.yourColumn = data.yourColumn.map(dateString => new Date(dateString));

The following seems to work using the user table from onboarding_db:

const workbook = XLSX.utils.book_new();
const data = await sql_query.trigger();

//replace created_at column with corresponding date objects
data.created_at = data.created_at.map((dateString) => new Date(dateString));

const sheet = XLSX.utils.json_to_sheet(formatDataAsArray(data), {
  cellDates: true,
});
XLSX.utils.book_append_sheet(workbook, sheet, "Dates");

const base64Download = XLSX.write(workbook, { type: "base64" });
utils.downloadFile({ base64Binary: base64Download }, "test", "xlsx");

1484×610 309 KB

Does that work?