Import Records From CSV File (or any data file) to SQL Server (or any database system) With Python

In this Python tutorial, we are going to learn how to import records from a CSV file to Microsoft SQL Server.

The script can also be adapted to import dataset from an Excel file, a text file, or any data file as we will be reading the dataset into a pandas dataframe first. And the database system is not limited to just SQL Server, it can be MySQL, Oracle, or any other database systems. I am using SQL Server because this is the database system I know the best.

Buy Me a Coffee? Your support is much appreciated!
----------------------------------------------------------------------------------------------------------------
Venmo: @Jie-Jenn

Check out Kit →

Timestamp:
00:00 - Tutorial info
00:45 - Data source used in this video
02:15 - Table Creation SQL Statement
02:35 - Writing automation script in Python
03:20 - How to find out what drivers are available on your PC
03:45 - Get SQL Server Server Name
04:45 - Import data to DataFrame
07:45 - Data clean up
14:52 - Convert DataFrame object to List object
16:00 - Create connection string
19:45 - Create connection object
22:00 - Create cursor object
25:35 - Run the script for testing.

Support my channel so I can continue making free contents
----------------------------------------------------------------------------------------------------------------

Tags:
#SQLServer #Python

[sauravkumar]

Great tutorial mate. just had difficulty in understanding few words but the subtitle made sure I have none. Thanks a lot.

[Krazy_Shorts]

Great vid! Thanks...This will help me big time!

[harmonicsaurav]

For Step 3.3, I usually use following syntax, instead of using '?' for each columns. It will be helpful for larger data with huge number of columns.

VALUES ({', '.join(['?']*len(<your_dataframe>.columns))})

This will count the number of columns and gives you back the required '?' as shown in the example.

[DavidRodrigues-bjwv]

Excellent tutorial. Thank you for sharing!

[ruthfussee]

Do you have a tutorial where we do something like this, but update if there is a primary key exist or add in the entire row if it doesnt?

[boogeyman]

Getting pypyodbc .error data source name not found and no default driver specified pls help me in this 🙏

[maikolvargas]

if you struggle with the blank values in your files use this

df_data = df[columns]
newdf = df.fillna(' ')
records = newdf.values.tolist()

[goanywhere]

Hello Jie, I need to parallel ingest a CSV file with half million records for a assignment. Should i chunk the file and then follow this video? or do you have some other suggestion. Thanks.

[Arunkumar-kycd]

Hi Jie,
I got the Error as GETDATA' is not a recognized built-in function name, Any Suggestion?

[nishantm]

how can you import csv file to an already existing table using sql query?
like suppose you have Customer table and now you want to add data from xyz.csv file and both Customer and csv file have same attribute, then how to do such task?

[cvillejin]

Hey Jie - do you have any solutions for "TypeError: tuple indices must be integers or slices"? - This guide worked for most of my .csv files, but for one particular file, I'm getting the tuples error.

edit: files

[MichelUNITED]

@Jie jenn. i need your help and it is urgent. I need to know how i can create A SCRIPT to populate a database. The tools i have to do this are python interpreter(visual studio code), Mysql workbench and xampp. Please help i need to install any ODBC DRIVER to start with?

[ozzyit]

Very helpful.. But what if i have to handle with multiple sheets in an exce(csv) file can you tell me what addition i need to do in this code

[aianIII]

I followed your code and tried to generate the data in SQL Server by running the SELECT script. However, when I ran the SELECT script again, the data was gone.

It appears that the data is not being stored in the database. Could you please help me?

[K-Von]

Thanks for the video! Could you help me with a question, how could I restrict the number of insertions given a number, instead of inserting all the rows?

[Boswortel]

Very nice tutorial but i am wondering if this is the only/best way to import a large amount of data. In bash i always use mysqlimport but does python also have something like this?

[muhammadosama]

Great video thanks..But i have a question how can i import multiple sheets from excel document?

[ririraman]

can we use this python script to append data in same table of SQL if new files arrive?

[adilsonpinto]

FileNotFoundError: [Errno 2] No such file or directory: which is?

[MichelUNITED]

please i don't have a SQL Server but i have Xampp connected to Mysql workbench. i know i can run the script on MYsql workbench. i don't understand the ODBC FILE you imported on visual studios. Also the connection is not clear to the data base is not clear.