search

Snowflake-Labs / Excelerator

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

TimeStamp #18

Closed GouriprasadMP closed 3 years ago

GouriprasadMP commented 3 years ago

Hi, When I load data I am facing issue if the data type is Timestamp in the table ... I teseted with mutilple tables I am unable to load data is data type is Timestamp Any idea why this is happening Warm Regards,

ssegal100 commented 3 years ago

Would you send me an example of the file or a screenshot. I need to see what you are actually loading. Also what is the error you are getting? Look in the History tab of the Snowflake UI and see if anything failed and what that error message is. Thanks.

GouriprasadMP commented 3 years ago

Sure,let me try

ssegal100 commented 3 years ago

@GouriprasadMP I believe this issue has been resolved right? Would you add the solution and then close the issue? Thanks.

TonyGaul commented 3 years ago

@ssegal100 I had this issue last week and ended up choosing a text data type. I am not sure if it is fixed. Will leave the OP to update but can dig something up if need required.

ssegal100 commented 3 years ago

@TonyGaul Would you send me the exact format of the timestamp and also the error message you were getting? Please also look at the History tab in Snowflake and tell me what the error is there on the query that received the error. Thanks.

TonyGaul commented 3 years ago

@ssegal100 In the xslx I have a datetime field called "FieldTimestamp", the first value is "2021-19-01 06:28:42". The Excel custom format is "yyyy-mm-dd hh:mm:ss". When uploading to Snowflake I get this error: "Timestamp '2021-19-01 06:28:42' is not recognized File '..

TEMP.CSV.gz', line 3, character 39 Row 1, column "
"["FieldTimestamp":6] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client."

I have tried setting a number of different Snowflake Timestamp format config values such as: yyyy-mm-dd hh:mm:ss yyyy-MM-dd HH:mm:ss YYYY-MM-DD HH:MM:SS YYYY-MM-DD HH24:MI:SS

Have tried different Excel custom formats too.

TonyGaul commented 3 years ago

@ssegal100 Hi, after re-reading my comment, I realised that although the Excel custom format is showing "yyyy-mm-dd hh:mm:ss", the actual cell value is being displayed as "yyyy-dd-mm hh:mm:ss". Therefore a Snowflake timestamp configuration of "YYYY-DD-MM HH24:MI:SS" worked. So I do not have any problem with this issue and other xlsx files with datetime value work. Thanks.

ssegal100 commented 3 years ago

@TonyGaul Thanks for the follow up.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.