This is an improvement I have implemented for one of my clients who use .csv as their main data format and lack of proper structure of the SQL database. I created a python script to automatically insert the flat file data into SQL database, although my python skill is still rusty the script runs as it is designed. Here’s the breakdown of a sample script.

Pyodbc is the package we gonna use for this script, please check its GitHub or PyPI for more documentations.

1. Import packages

1    # Import packages
2    import pyodbc
3    import pandas as pd

2. Load flat file data into Pandas Dataframe, and print DataFrame for checking.

5    # Load csv file data into DataFrame
6    data = pd.read_csv (r'C:\SQL Test\Test.csv')
7    df = pd.DataFrame(data, columns = ['Column1', 'Column2', 'Column3', 'Column4'])
8    
9    print(df)

3. Connect to your target SQL database

11   # Connect to SQL Database
12   conn = pyodbc.connect('Driver={SQL Server};'
13                         'Server=Your Server Address;'
14                         'Database=Your Database Name;'
15                         'Trusted_Connection=yes;')
16   
17   cursor = conn.cursor()
18   

4. (Optional) If the target database doesn’t have a table created, you can use the following script to create a data table.

Note: please be aware this script can only be run once since it will return an error that the database already exists the same table if you run the second time.

19   # Create new table in SQL database (!!ONLY NEED TO BE EXECUTED ONCE!!)
20   cursor.execute('CREATE TABLE TableName (Column1 Data type,Column2 Data type,Column3 Data type, Column4 Data type)')
21   

5. Insert DataFrame data into SQL Database Table

22   # Insert DataFrame into SQL Database Table
23   for row in df.itertuples():
24       cursor.execute(''' 
25                       INSERT INTO DatabaseName.dbo.TableName (Column1, Column2, Column3, Column4) 
26                       VALUES  (?,?,?,?) 
27                       ''',
28                      row.Column1,
29                      row.Column2,
30                      row.Column3,
31                      row.Column4
32                      )
33   conn.commit()
34

6. At last, we want to pull the data from the SQL database to check it has been inserted properly.

35   # Validate new created SQL table structure and value
36   cursor.execute('SELECT * FROM DatabaseName.dbo.TableName')
37   
38   for row in cursor:
39       print(row)
40   
41   sql_query = pd.read_sql_query('SELECT * FROM DatabaseName.dbo.TableName',conn)
42   print(sql_query)
43   print(type(sql_query))
44   

There you should have it, flat file data is transferred to the SQL Database table.

Please also make sure that in the Step 4 when you trying to create a new table in the database, make sure the column data type align with each column in the flat file.