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

[@sauravshrestha1890]

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(.columns))}) This will count the number of columns and gives you back the required '?' as shown in the example.

[@jiejenn]

Great tip, thanks for sharing.

[@sauravshrestha1890]

@@jiejenn my pleasure. I recently had to deal with multiple tables containing atleast 60 columns. Typing 60 to 70 ‘?’ Was confusing, untidy and higher chances of mistakes. Glad I found this trick somewhere in youtube as well 😃 happy to share here. Thank you for great video

[@sauravkumar9454]

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

[@maikolvargas7149]

if you struggle with the blank values in your files use this df_data = df[columns] newdf = df.fillna(' ') records = newdf.values.tolist()

[@irvingceron1016]

Great tip

[@Krazy_Shorts]

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

[@DavidRodrigues-bj6wv]

Excellent tutorial. Thank you for sharing!

[@fernandoflores3728]

Thanks a lot, great tutorial!

[@ruthfussee5503]

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?

[@boogeyman9824]

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

[@nishantm1924]

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?

[@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 meeeee.do i need to install any ODBC DRIVER to start with?

[@jiejenn]

Try Google.

[@goanywhere7693]

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.

[@ozzyit7512]

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

[@jiejenn]

The workflow is a bit different. I will cover that topic in a separate video.

[@Arunkumar-ky9cd]

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

[@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?

[@jiejenn]

The script doesn't generate any data, it merely import a data file into your SQL Server database.

[@aianIII]

@@jiejenn I'm sorry if I didn't explain it clearly. I used a SELECT script to query the data in MS SQL, and it was successful. However, when I executed the same script for the second time, the queried data disappeared.

[@jiejenn]

@@aianIII Then I don't know if I can help without looking at your script. Perhaps there might a typo or two in your code somewhere.

[@adilsonpinto2752]

FileNotFoundError: [Errno 2] No such file or directory: 'Real-Time_Traffic_Incident_Reports.csv' which is?

[@jiejenn]

Perhaps this will help www.google.com/search?client=firefox-b-1-d&q=python+FileNotFoundError

[@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

[@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?

[@erenhan]

you can iloc or loc and store it another dataframe and send via to_sql

[@yusliandi642]

Thank for this

[@muhammadosama5666]

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

[@jiejenn]

I will be covering that topic in a separate video.

[@muhammadosama5666]

@@jiejenn Ok i'll be waiting for that video Thankyou

[@nghiatran1509]

Can you import database in XBRL to Microsoft Access ?

[@jiejenn]

Don't have experience with XBRL, so I am not sure to be honest.

[@ririraman7]

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

[@jiejenn]

Insert into appends records to the same table you specified.

[@ririraman7]

@@jiejenn Yes correct, I did today and it worked! Thank you very much brother! you are saving lives!

[@cvillejin]

Hi Jie - When I try to run this code, I get an error "Database Error: [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied" how can I fix this?

[@jiejenn]

It is difficult to tell based on a very general error message. I would suggest you post your question on Stack Overflow.

[@cvillejin]

@@jiejenn no problem. I was able to combine what I learned in your video with some other guides and it is now fully working! Thanks again for all your great videos!

[@yannantso4652]

@@cvillejin I got the same problem, coud you tell how does you have solved your problem ? thx

[@cvillejin]

@@yannantso4652 try changing the driver to the version of your sql server

[@LoveisHell85]

Thanks for this

[@jiejenn]

You're welcome.

[@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.

[@jiejenn]

Maybe try Google.

[@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?

[@jiejenn]

It's really depending on the system. I came from MS SQL Server, so usually for bulk upload, I would start with SSIS. For anything else required data exchange with 3rd-party system, that's when I would go with scripting route. Executemany() method despite is efficiently when it comes to memory allocation, it is still not a true bulk insert.

[@ozzyit7512]

Bro when i'm trying to import multiple sheets from an excel file its not working ! Can you please make a tutorial on how to import multiple sheets, any help would very helpfull

[@jiejenn]

I will look into it.

[@adilsonpinto2752]

Step 3 It is giving the following error: NameError: name 'cursor' is not defined

[@jiejenn]

Makes sure cursor is created first.

[@wingscofant]

Hi man you're awesome, can you help me sending code for inserting/uploading csv and images to database(SQL Server/Wamp Server)

[@tekindogan9164]

👍👍👍

[@wilsonusman]

Hi Jie, thanks for making this video. I was wondering if you could implement something like tqdm library into this same script. That would be very helpful specially if you're trying to load millions of records.

[@jiejenn]

Hi Wilson. Never used tqdm library before, so don't know if I will be able to help.

[@akhilseeramsetty9125]

PERFECT SYNTAX FOR USERNAME AND PASSWORD PLEASE IN CONN_STRING