Loading data from csv via a workflow

Workflows
,
1

Data sources guides

Hi all, I'm a week into learning this app-building environment. I have built an executive dashboard for my company that's just what I need.
Now I'm working on building a workflow to handle daily data updates that has the following steps:

  1. Upload the file through app UI - saves to retool storage
  2. Read the file using Retool Storage - Get contents of a file
  3. decoding the base64 string into a json array using atob()
  4. update the table using Retool Database - Bulk insert records. This is where i'm stuck with the error:
    column "0" of relation "shopify_data_extract_curr" does not exist
    the array ids (0,1,2...n) are being used as the table column names and the content instead of the values in the array.
    image
    image1856×640 99.7 KB

    any ideas about what I am doing wrong here?
2

Can you share a bit more about how the bulk insert request is built? Are you able to call upon the specific index of your atob() output node when building the Bulk Insert and log the value along the way?

atob.data[i] kind of thing

3

@pyrrho thanks for having a look.
I'm trying to accomplish this upldate with the retool Bulk Insert Recordsfeature
In the screenshot you can see that the array that I'm feeding contains the column names in the first row of the array and the values are contained in the other array rows.
executing this gets the error insert into "shopify_data_extract_curr" ("0", "1", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "2", "3", "4", "5", "6", "7", "8", "9") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20), ($21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40), ($41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60), ($61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80), ($81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100), ($101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120), ($121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140), ($141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160), ($161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180), ($181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200) - column "0" of relation "shopify_data_extract_curr" does not exist. its not picking up the array values just the references,

image
image1920×1200 147 KB

4

I think the issue is in the return value of the decode_content2 query.

Your screenshot is showing the array of 20 items in the value, similar to this:
image

but I think you might need to form it more like this:
image

Or possibly use {{decode_content2.data.data[0]}}

5

@pyrrho got it. i've given this a shot. when i pass in {{ decode_content2.data.data[0] }} then i'm only passing the first row of the array which contains the db column names. But the system is still reading that as 0,1,2...20 and then the data to update with is all just defaults.

column "0" of relation "shopify_data_extract_curr" does not exist

image
image1064×996 148 KB

image
image1088×1038 78.6 KB

the docs in Retool are frustratingly bad. Cloud-hosted Retool 2.3: October 1, 2018 | Retool Docs