Snowflake ODBC to Excel, errors on field names containing spaces

user_14903985 0
Viewed 62

We have a highly complex set of tables, with nested views that eventually feed a series of dashboards on a Tableau server. The base view uses "as" clauses on some data fields to create fields with spaces in the field name (i.e.somefieldname as "Some Field Name"). Later views use the * wildcard to retrieve all values. Tableau is able to handle it.

The problem is now users want to access those final views in Excel.
We set up an ODBC connection on their workstations and when they pull the data from one of the final views. However, the fields that contain blanks in the field names show as errors and are blank in the resulting worksheet. I'm trying to build a view on that final view and use "as" clauses to remove the spaces in the field names, but haven't been able to find the proper SQL syntax for the source field. I've tried brackets but that didn't work.

Would we be better off trying Power BI? Our data management people are just getting started with it; I haven't seen it yet but will be tomorrow.

Thanks in advance for any tips you can provide! Lou

snowflake-cloud-data-platform odbc excel
1 Answers

Creating a view on top of your final view with renamed columns is probably your easiest solution. The SQL syntax for selecting from a column that has been created with spaces (more generally: a column that has been created with " around its field name/s) is to put the column in double quotes (") when you select from it. Here is an example:

-- Create a sample table. The first column contains spaces and capitals
create or replace table test_table
(
    "Column with Spaces" varchar, -- A column created with quotes around it means that you can put anything into the field name. Including spaces & funky characters
    col_without_spaces   varchar
);

-- Insert some sample data
insert overwrite into test_table
values ('row1 col1', 'row1 col2'),
       ('row2 col1', 'row2 col2');

-- Create a view that renames columns
create or replace view test_view as
(
    select
        "Column with Spaces" as col_1, -- But now you have to select it like this since the spaces and capital letters have been treated literally in the create table statement 
        col_without_spaces as col_2
    from test_table
);

-- Select from the view
select * from test_view;

Produces:

+---------+---------+
|COL_1    |COL2     |
+---------+---------+
|row1 col1|row1 col2|
|row2 col1|row2 col2|
+---------+---------+
user_32872500
Related