def load_delimited_file_to_table(connection,
table,
source_file,
schema_file,
skipfirstrow=1,
delimiter=','):
"""Takes delimited file name, schema file, and db connection and inserts data to a specified table
Args:
table: table name where csv data will be written
schema_file: schema file that has all column names and data type names
csv_file: data being loaded
server: sql server host name
config: which configuration name to pull username and password credentials
cred_file: location of db login config file
skipfirstrow(optional): if 1 then skip the first row of data (exclude headers)
Returns:
None
"""
data_list = loop_delimited_file(source_file, delimiter=delimiter)
schema_list = get_schema_file(schema_file)
#skips the first value of data_list which is the header
data_list = iter(data_list)
if skipfirstrow == 1:
next(data_list)
insert_datarows_to_table(data_list, schema_list, connection, table)
def get_schema_file(schema_csv):
"""Pull in schema from a file and return list with column and type
Args:
schema_csv: two column comma delimited schema file formatted ColumnName,Type
Returns:
list, Schema list of column and type
"""
schema_raw = loop_delimited_file(schema_csv)
schema =[]
for column,datatype in schema_raw:
schema.append([column.strip(' '),datatype.strip(' ')])
return schema
def load_delimited_file_to_table(connection, table , source_file, schema_file, skipfirstrow=1, delimiter=','):
"""Takes delimited file name, schema file, and db connection and inserts data to a specified table
Args:
table: table name where csv data will be written
schema_file: schema file that has all column names and data type names
csv_file: data being loaded
server: sql server host name
config: which configuration name to pull username and password credentials
cred_file: location of db login config file
skipfirstrow(optional): if 1 then skip the first row of data (exclude headers)
Returns:
None
"""
data_list = loop_delimited_file(source_file,delimiter=delimiter)
schema_list = get_schema_file(schema_file)
#skips the first value of data_list which is the header
data_list = iter(data_list)
if skipfirstrow == 1:
next(data_list)
insert_datarows_to_table(data_list,schema_list,connection,table)