Insert data from text delimiter | file into SQL Server table in python script

Question

I have a delimited file like this:

    name1|9|111|replace|12|sds22|dsd|GDS-sw-E|1|1|0|No|21|43|No|0.1
    name1|9|222|replace|33|dfs|SWR|RRR-ddd@-S1|1|1|0|No|33|4|None|0.9

I want to import this data into SQL Server using Python code.

This is my code:

import pyodbc

def sql_conn():
    conn = pyodbc.connect('Driver={SQL Server};'
                          'Server=servername\dbname,1433;'
                          'Database=dbname;'
                          'UID=uname;'
                          'PWD=pass;'
                          'Trusted_Connection=no;')
    cursor = conn.cursor()
    SQLCommand = (
        "INSERT INTO dbname.schema.table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
    print(SQLCommand)
    Values = ['name1', '9', '1111', 'replace', '12', 'sds22', 'dsd', 'GDS-sw-E',
               '1', '1', '0', 'No', '21', '43', 'None', '0.1']

    print(Values)
    # Processing Query
    cursor.execute(SQLCommand, Values)

    conn.commit()
    print("Data Successfully Inserted")
    conn.close()


if __name__ == "__main__":
    sql_conn()

Currently I have to manually put values in variable, I am looking to automate this to read the data from the delimited file and insert it into the SQL Serve database.

question from:https://stackoverflow.com/questions/65599471/insert-data-from-text-delimiter-file-into-sql-server-table-in-python-script

Answer 1

Assuming your lines are composed of strings like this:

line = "name1|9|111|replace|12|sds22|dsd|GDS-sw-E|1|1|0|No|21|43|No|0.1"

Since the delimiter is fixed, you can get the values directly by splitting:

Values = line.split("|")

gives Values as

['name1', '9', '111', 'replace', '12', 'sds22', 'dsd', 'GDS-sw-E', '1', '1', '0', 'No', '21', '43', 'No', '0.1']

Basically, just loop over the lines and split them around the delimiter and then pass in your SQL insert statement. Something like:

cursor = conn.cursor()
SQLCommand = (
    "INSERT INTO dbname.schema.table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
)
lines = f.readlines() # assuming you opened your file with 'f = open("file.txt", "r")'
for line in lines:
    Values = line.split("|")
    cursor.execute(SQLCommand, Values)
    conn.commit()
    print("Data Successfully Inserted")
conn.close()