search

Snowflake-Labs / Excelerator

This is an Excel Addin for Windows that reads and writes data to Snowflake
Apache License 2.0
78 stars 22 forks source link

Last option is missing -- "Auto-generate data types when not specified for new columns or a new table " Stored Procedures needed". #12

Closed schenjgc closed 3 years ago

schenjgc commented 3 years ago

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 3 years ago

@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 3 years ago

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 3 years ago

Did you check the 'Create new table and upload' checkbox at the bottom of the page?

schenjgc commented 3 years ago

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 GitHubhttps://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 unsubscribehttps://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 3 years ago

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 GitHubhttps://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 unsubscribehttps://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 3 years ago

I think I might have found the issue. Let me test it and I'll let you know soon.

ssegal100 commented 3 years ago

@schenjgc I resolve the issue. Would you please download the latest and test again. Please let me know the results. Thanks. Steve

schenjgc commented 3 years ago

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==

@schenjgchttps://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 GitHubhttps://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 unsubscribehttps://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 3 years ago

Great, I'll close the issue.

schenjgc commented 3 years ago

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==

@schenjgchttps://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 GitHubhttps://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 unsubscribehttps://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 3 years ago

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 3 years ago

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 GitHubhttps://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 unsubscribehttps://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.

ssegal100 commented 3 years ago

@schenjgc I'm unable to reproduce that. Would create a sample excel file without any sensitive data and upload it here?