Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed".
Closed this issue · 13 comments
schenjgc commented
I am missing the 3rd check box under advanced options:
"Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed".
I am using the most recent SnowflakeExcelAddin.xlam (1/22/21) x64 add in
Thanks
ssegal100 commented
@schenjgc Due to some complications we removed this feature.
Is it something that would be important to you? The other way of loading new columns would be to explicitly set the datatype.
I'd like to get a understanding of the value it would add to help determine if we should work at enabling it.
Thanks!
Steve
schenjgc commented
when I try creating a new table I get the error:
Error executing SQL: SQL compilation error:
Object 'DEMO_DB.PUBLIC.EXAMPLE' does not exist or not authorized
ssegal100 commented
Did you check the 'Create new table and upload' checkbox at the bottom of the page?
schenjgc commented
Yes its checked.
From: Steven Segal <notifications@github.com>
Sent: Thursday, January 28, 2021 12:14 PM
To: Snowflake-Labs/Excelerator <Excelerator@noreply.github.com>
Cc: Stephen Chen <SChen@jgoldmanlp.com>; Mention <mention@noreply.github.com>
Subject: Re: [Snowflake-Labs/Excelerator] Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". (#12)
==EXTERNAL==
Did you check the 'Create new table and upload' checkbox at the bottom of the page?
-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FSnowflake-Labs%2FExcelerator%2Fissues%2F12%23issuecomment-769238683&data=04%7C01%7Cschen%40jgoldmanlp.com%7C84a98fedd63e490d523408d8c3b0252e%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474508599698552%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=e8WsA0vJ2wA7pYFIxRlAiJWRS1Mu%2FD7Jx0Wcs1Uio8w%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FASTNXP3KF5FJKBCKDAQ4IADS4GLORANCNFSM4WT5UCWA&data=04%7C01%7Cschen%40jgoldmanlp.com%7C84a98fedd63e490d523408d8c3b0252e%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474508599708544%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=XVJUj8ejfzvqNSVRZYcoX2DLwcu1N%2BPu5PJY08udwGw%3D&reserved=0>.
…________________________________
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If the reader of this message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination or use of the information contained in this communication is strictly prohibited and may be illegal. J. Goldman & Co., L.P., its affiliates and subsidiaries reserve the right to monitor, review, and archive the content of all electronic messages sent and/or received by any of its employees. If you received this email in error, please delete it and notify the sender. This communication is for informational purposes only and should not be regarded as an offer, solicitation or recommendation to purchase or sell any financial product.
schenjgc commented
The only way I'm able to get data into Snowflake from excel is if I first create the table in Snowflake in the same format as excel:
create or replace table "DEMO_DB"."PUBLIC".bbg_example3(DATE date, LB double, GPS double, URBN double);
insert into "DEMO_DB"."PUBLIC".bbg_example3 values
('2021-01-11', 46.91, 22.39, 28.65);
Then I query the table from excel using excelerator and then I add the data I want to upload into the table that was returned from my query in Excel.
From: Steven Segal <notifications@github.com>
Sent: Thursday, January 28, 2021 12:14 PM
To: Snowflake-Labs/Excelerator <Excelerator@noreply.github.com>
Cc: Stephen Chen <SChen@jgoldmanlp.com>; Mention <mention@noreply.github.com>
Subject: Re: [Snowflake-Labs/Excelerator] Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". (#12)
==EXTERNAL==
Did you check the 'Create new table and upload' checkbox at the bottom of the page?
-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FSnowflake-Labs%2FExcelerator%2Fissues%2F12%23issuecomment-769238683&data=04%7C01%7Cschen%40jgoldmanlp.com%7C84a98fedd63e490d523408d8c3b0252e%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474508599698552%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=e8WsA0vJ2wA7pYFIxRlAiJWRS1Mu%2FD7Jx0Wcs1Uio8w%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FASTNXP3KF5FJKBCKDAQ4IADS4GLORANCNFSM4WT5UCWA&data=04%7C01%7Cschen%40jgoldmanlp.com%7C84a98fedd63e490d523408d8c3b0252e%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474508599708544%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=XVJUj8ejfzvqNSVRZYcoX2DLwcu1N%2BPu5PJY08udwGw%3D&reserved=0>.
…________________________________
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If the reader of this message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination or use of the information contained in this communication is strictly prohibited and may be illegal. J. Goldman & Co., L.P., its affiliates and subsidiaries reserve the right to monitor, review, and archive the content of all electronic messages sent and/or received by any of its employees. If you received this email in error, please delete it and notify the sender. This communication is for informational purposes only and should not be regarded as an offer, solicitation or recommendation to purchase or sell any financial product.
ssegal100 commented
I think I might have found the issue. Let me test it and I'll let you know soon.
ssegal100 commented
@schenjgc I resolve the issue. Would you please download the latest and test again. Please let me know the results.
Thanks.
Steve
schenjgc commented
Fixed! Thank you!
From: Steven Segal <notifications@github.com>
Sent: Thursday, January 28, 2021 1:55 PM
To: Snowflake-Labs/Excelerator <Excelerator@noreply.github.com>
Cc: Stephen Chen <SChen@jgoldmanlp.com>; Mention <mention@noreply.github.com>
Subject: Re: [Snowflake-Labs/Excelerator] Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". (#12)
==EXTERNAL==
@schenjgc<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fschenjgc&data=04%7C01%7Cschen%40jgoldmanlp.com%7C3d941c575cbc4150e6fa08d8c3be3ad8%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474569090821380%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=DZaWVaHk0I%2FpWvmkTAdOIRzDMLry32JezTxuv3IVVH4%3D&reserved=0> I resolve the issue. Would you please download the latest and test again. Please let me know the results.
Thanks.
Steve
-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FSnowflake-Labs%2FExcelerator%2Fissues%2F12%23issuecomment-769300154&data=04%7C01%7Cschen%40jgoldmanlp.com%7C3d941c575cbc4150e6fa08d8c3be3ad8%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474569090821380%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=EeLO6%2F07%2FRevxQlDcNZkZFu2Oy33M4jBix6OudnlSqk%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FASTNXPZKKSXYWOLDKHBZH6TS4GXIVANCNFSM4WT5UCWA&data=04%7C01%7Cschen%40jgoldmanlp.com%7C3d941c575cbc4150e6fa08d8c3be3ad8%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474569090831369%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=fTsFhvjzs0x2Z%2FjvWeyHZpt1%2BoV0sJLYSic2TWhudkY%3D&reserved=0>.
…________________________________
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If the reader of this message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination or use of the information contained in this communication is strictly prohibited and may be illegal. J. Goldman & Co., L.P., its affiliates and subsidiaries reserve the right to monitor, review, and archive the content of all electronic messages sent and/or received by any of its employees. If you received this email in error, please delete it and notify the sender. This communication is for informational purposes only and should not be regarded as an offer, solicitation or recommendation to purchase or sell any financial product.
ssegal100 commented
Great, I'll close the issue.
schenjgc commented
A couple more questions if you can:
Does the data in excel need to be in a Table format to uplaod? And do inserts without updating the rest of the table take exceptionally long?
From: Stephen Chen
Sent: Thursday, January 28, 2021 2:14 PM
To: Snowflake-Labs/Excelerator <reply@reply.github.com>; Snowflake-Labs/Excelerator <Excelerator@noreply.github.com>
Cc: Mention <mention@noreply.github.com>
Subject: RE: [Snowflake-Labs/Excelerator] Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". (#12)
Fixed! Thank you!
From: Steven Segal <notifications@github.com<mailto:notifications@github.com>>
Sent: Thursday, January 28, 2021 1:55 PM
To: Snowflake-Labs/Excelerator <Excelerator@noreply.github.com<mailto:Excelerator@noreply.github.com>>
Cc: Stephen Chen <SChen@jgoldmanlp.com<mailto:SChen@jgoldmanlp.com>>; Mention <mention@noreply.github.com<mailto:mention@noreply.github.com>>
Subject: Re: [Snowflake-Labs/Excelerator] Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". (#12)
==EXTERNAL==
@schenjgc<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fschenjgc&data=04%7C01%7Cschen%40jgoldmanlp.com%7C3d941c575cbc4150e6fa08d8c3be3ad8%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474569090821380%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=DZaWVaHk0I%2FpWvmkTAdOIRzDMLry32JezTxuv3IVVH4%3D&reserved=0> I resolve the issue. Would you please download the latest and test again. Please let me know the results.
Thanks.
Steve
-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FSnowflake-Labs%2FExcelerator%2Fissues%2F12%23issuecomment-769300154&data=04%7C01%7Cschen%40jgoldmanlp.com%7C3d941c575cbc4150e6fa08d8c3be3ad8%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474569090821380%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=EeLO6%2F07%2FRevxQlDcNZkZFu2Oy33M4jBix6OudnlSqk%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FASTNXPZKKSXYWOLDKHBZH6TS4GXIVANCNFSM4WT5UCWA&data=04%7C01%7Cschen%40jgoldmanlp.com%7C3d941c575cbc4150e6fa08d8c3be3ad8%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474569090831369%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=fTsFhvjzs0x2Z%2FjvWeyHZpt1%2BoV0sJLYSic2TWhudkY%3D&reserved=0>.
…________________________________
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If the reader of this message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination or use of the information contained in this communication is strictly prohibited and may be illegal. J. Goldman & Co., L.P., its affiliates and subsidiaries reserve the right to monitor, review, and archive the content of all electronic messages sent and/or received by any of its employees. If you received this email in error, please delete it and notify the sender. This communication is for informational purposes only and should not be regarded as an offer, solicitation or recommendation to purchase or sell any financial product.
ssegal100 commented
Q: Does the data in excel need to be in a Table format to uplaod?
A: It doesn't need to be an Excel table, but it does need to have the column names in the first row or the second row if you define the columns datatypes for new columns. It can't have any blank columns to the left of the data.
Q:And do inserts without updating the rest of the table take exceptionally long?
A:Updates should be pretty fast. How long are you seeing?
But to answer the question, if you check 'Insert all data...' or 'Replace all data...' it will be faster since it doesn't have to do a merge, which compares every row.
Also, just so you know, you don't have to upload all the columns or all the rows. If columns or rows are missing from the spreadsheet, it will just ignore them, unless you choose the replace option.
Hope this helps.
schenjgc commented
Hey Steven,
Replacing all data and creating new tables works now but I'm having issues on updates and inserts.
Anytime I try to insert it gives me a SQL compilation error: unsupported data type 'LB' since it appears to think I'm defining data types in the first column when they are just column names.
If I then try to define the data types it errors and says the column headers I have already exist and that you should not define the data type for a column that already exists.
Updates also run into similar errors.
It seems like the only updates that work are adding columns
From: Steven Segal <notifications@github.com>
Sent: Thursday, January 28, 2021 4:36 PM
To: Snowflake-Labs/Excelerator <Excelerator@noreply.github.com>
Cc: Stephen Chen <SChen@jgoldmanlp.com>; Mention <mention@noreply.github.com>
Subject: Re: [Snowflake-Labs/Excelerator] Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". (#12)
==EXTERNAL==
Q: Does the data in excel need to be in a Table format to uplaod?
A: It doesn't need to be an Excel table, but it does need to have the column names in the first row or the second row if you define the columns datatypes for new columns. It can't have any blank columns to the left of the data.
Q:And do inserts without updating the rest of the table take exceptionally long?
A:Updates should be pretty fast. How long are you seeing?
But to answer the question, if you check 'Insert all data...' or 'Replace all data...' it will be faster since it doesn't have to do a merge, which compares every row.
Also, just so you know, you don't have to upload all the columns or all the rows. If columns or rows are missing from the spreadsheet, it will just ignore them, unless you choose the replace option.
Hope this helps.
-
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FSnowflake-Labs%2FExcelerator%2Fissues%2F12%23issuecomment-769414746&data=04%7C01%7Cschen%40jgoldmanlp.com%7C47bb99d3c6c94cc6727108d8c3d4b5e9%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474665678662929%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=lOid4JJGQH6uKOXmKm5MW0YfG5Meyc%2FnxHu9ZfiLlTI%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FASTNXP62UYMVYXKBHAUZC3TS4HKEDANCNFSM4WT5UCWA&data=04%7C01%7Cschen%40jgoldmanlp.com%7C47bb99d3c6c94cc6727108d8c3d4b5e9%7C3f872cc255074259b94ec11ec61f9cf3%7C1%7C0%7C637474665678672921%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=mkYxbMAJwtFmIwwu%2FkJIvnaFNgkUNMRkK0ffafHsr7o%3D&reserved=0>.
…________________________________
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If the reader of this message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination or use of the information contained in this communication is strictly prohibited and may be illegal. J. Goldman & Co., L.P., its affiliates and subsidiaries reserve the right to monitor, review, and archive the content of all electronic messages sent and/or received by any of its employees. If you received this email in error, please delete it and notify the sender. This communication is for informational purposes only and should not be regarded as an offer, solicitation or recommendation to purchase or sell any financial product.