How to get the column index number of a specific field name in a staged file on Snowflake?

Asked Jan 18 '21 at 11:54

Active Mar 11 '21 at 13:18

Viewed 311 times

I need to get the column number of a staged file on Snowflake.

The main idea behind it, is that I need to automate getting this field in other queries rather than using t.$3 whereas 3 is the position of the field, that might be changed because we are having an expandable surveys (more or less questions depending on the situation).

So what I need is something like that:

SELECT COL_NUMBER FROM @my_stage/myfile.csv  WHERE value = 'my_column_name` 
-- Without any file format to read the header

And then this COL_NUMBER could be user as t.$"+COL_NUMBER+" inside merge queries.

edited Jan 18 '21 at 12:30

Shadow

33,525
10
51
64

asked Jan 18 '21 at 11:54

alim1990

4,656
12
67
130

probably the easiest way to do this is in your client (Python, Go, etc.). You select all columns, find the position of your column and generate the sql statement. – Rick Jan 18 '21 at 12:14
@Zephro I am doing a JavaScript procedure, how can I achieve this with JS? – alim1990 Jan 18 '21 at 12:18
a Snowflake stored procedure? – Rick Jan 18 '21 at 12:32
@Zephro yes in a snowflake stored procedure. – alim1990 Jan 18 '21 at 12:54

How to get the column index number of a specific field name in a staged file on Snowflake?

0 Answers0