Loading data into Snowflake from shared locations automatically

Question

Is it possible to load data into Snowflake from shared locations automatically on a schedule rather than having to manually load the data in.

Answer 1

It sounds like you should be looking at Snowpipe for that. There's no need for schedules: it loads new files automatically as soon as they become available in cloud storage.

See also the continuous loading guide on Snowflake's docs site

Answer 2

First, you should process your excel file using PYthon, then load it into Snowflake. Use the code below:

from sqlalchemy import create_engine
import pandas as pd
snowflake_username = 'username'
snowflake_password = 'password'
snowflake_account = 'accoutname'
snowflake_warehouse = 'warehouse'
snowflake_database = 'database'
snowflake_schema = 'public'

engine = create_engine(
'snowflake://{user}:{password}@{account}/{db}/{schema}?warehouse 
 {warehouse}'.format(
    user=snowflake_username,
    password=snowflake_password,
    account=snowflake_account,
    db=snowflake_database,
    schema=snowflake_schema,
    warehouse=snowflake_warehouse,
    ),echo_pool=True, pool_size=10, max_overflow=20
)

try: 
 connection = engine.connect()

 df_sensor.columns = map(str.upper, df_sensor.columns)
 df_sensor.to_sql('tb_equipments'.lower(), con=connection, 
 schema='public', index=False, if_exists='append', chunksize=16000)
 results = connection.execute('select count(1) from 
 tb_equipments').fetchone()

print('\nTotal de linhas inseridas: ',results[0], '\n')

finally:
  connection.close()
  engine.dispose()