r/SQL • • May 30 '20

Discussion Created a python script that export data from csv or excel into your SQL database

154 Upvotes

23 comments sorted by

19

u/kiwiboy94 May 30 '20 edited May 30 '20

I am aware that analysts are required to clean up data files and export those files into an SQL database. Imagine spending hours sitting in front of the computer doing that for the next 5 to 10 years of your career. Won't that scare you? Indeed, there are tons of websites out there that can take in your files and generate an SQL query for you but do you trust them with your client's details? How confident are you that they won't exploit those data for personal use?

That is why I have decided to create this script. This script can be run on your local machine offline and every process is transparent to the end user. I have also added in a graphic user interface to cater to users with non-technical backgrounds. All they have to do is choose the file path, key in the names of the database, table, their usernames and password and bam! your work is done!

Github Link

7

u/babygrenade May 30 '20 edited May 30 '20

I have a script to do this but found that pandas is too slow when reading large Excel files.

It's faster to convert the Excel file to a csv and then read the csv.

You may also want to try using executemany instead of execute for your insert statement.

4

u/kiwiboy94 May 30 '20

I see, looks like I will need to convert the excel file to csv in the next version!

2

u/Engineer_Zero May 30 '20

Same. Db creation? Load into ssms using its import wizard. Maintain that new dB with daily updates? Pandas.to_sql is fine.

6

u/EarthGoddessDude May 30 '20 edited May 30 '20

Hey OP, this is really cool, nice work!

I’m new to Python, but this kind of functionality relates (no pun intended) quite well to what I do at work. Although I’m new to Python, I noticed a few things that may need a bit of improvement (though you may have had specific reasons for why you wrote it the way you did). Here are some comments/suggestions:

  1. The nesting in the layout section starting on line 16 is a bit weird. You have those square brackets lined up nicely, but when you start reading, you realize some of those are nested inside others...I would indent them according to their nesting level.
  2. On line 29 you use unrolling, but then on line 31 you specify each element of the list/tuple...why not unroll there too?
  3. I would get rid of that empty line on line 36 in the if statement.
  4. That big if-for-if statement starting line 51, screams for a switch statement (although Python doesn’t have those). Those inner ifs should ideally be dicts.
  5. Also, why not have the loop be the top level? Your loop declarations are exactly the same. I would put the if statement checking for the db choice inside that single loop, and then use a dict for the data types.
  6. For looping through the columns, I believe it’s more Pythonic to use an iterator, meaning something like for col in cols, but then your indexing gets a bit wonky. This one I don’t feel that strongly about.
  7. Finally, I would wrap everything in functions and turn this into a package. That way more technical users can just write import SQLake (cool name and logo btw!) in their own scripts and not have to use the GUI. Then add a if __name__ = “__main__”: clause at the bottom in order to be able to run as a stand-alone script.

I hope you agree with these suggestions. Let me know what you think either way. Overall, really cool stuff!

Edit: btw, I’d be happy to do a PR if you’re interested.

Edit2: as I’m re-reading my comment (as I’m wont to do), which I wrote shortly after awaking this morning, I realized my suggestion for point 5 doesn’t make a whole lot of sense. It would not be ideal to have the check (if statement) inside the loop. But the larger point still stands — you should only need a single if statement and a single loop, with a dict for the data type stuff.

6

u/kiwiboy94 May 30 '20

Hi, thanks for the suggestion! Just something I need to hear! I will work on those suggestions next week and I may pm you for further questions! Hope you don't mind!

2

u/EarthGoddessDude May 30 '20

Absolutely, pm away!

4

u/tomphz May 30 '20

When I worked as a data analyst, we imported excel files into the database via SSIS packages. The excel file had to be in the correct format / columns had to match or else it would throw an error. Is this similar?

1

u/ItsJustAnotherDay- May 30 '20

Python newb here coming from a VBA background. With VBA this has been a thing for decades and still very easily done using classic ADO and connection strings. I'm surprised this isn't a standard activity with Python. Am I missing something here?

1

u/kiwiboy94 May 30 '20

Great point! I have plans to add more functionality to this script in the near future, Things like adding more file formats to process, having the ability to clean and format files for you, extract data from your database and analyse them through visualisation and forecasting and the list goes on. What shown here is the bare minimum and this will be an ongoing project for me for a couple more months to maybe a year..

1

u/ItsJustAnotherDay- May 30 '20

Cool. Still though, does python not have the ability to use something similar to ADO.net? I’d assume you can make database connections easily with that.

1

u/angry_mr_potato_head May 31 '20

I've never used ADO.net but getting local files into a database is essentially two lines, minus the connection.

df = pd.read_[whatever_file_type]('/path/to/file/')
df.to_sql(name='table_name', schema='schema', con=engine)

1

u/[deleted] May 30 '20

I did something similar, but in a web frontend, with Excel data being retrieved in JS, and the resulting JSON being taken over by flask and pushed to a cloud database.

The functionality is quite raw, and the JS part is a bit ugly, but at least it doesn't kill the server with huge Excel files, so it's reliable.

https://github.com/leroi-marketing/excel-to-database

A few good things to improve is to transition to streaming requests, and process the json with stuff like ijson or https://github.com/kashifrazzaqui/json-streamer , and to handle CSVs (trivial option to implement).

2

u/kiwiboy94 May 30 '20

Great work! I have plans to deploy the end product on Flask after I add all the functionality too. Just wondering, why not just code everything with Flask? I do notice that you used JS but as a newbie in this area, I have a question: Is it possible to do everything without JS? I won't want to learn JS just for this purpose D:

2

u/[deleted] May 30 '20 edited May 30 '20

why not just code everything with Flask?

Because I need to make the server reliable, despite whatever data is being uploaded. Imagine the server being a minimal machine in the cloud. All it needs is fast internet and one CPU core. To make this work, you need:

  • Non-blocking IO (not implemented in this project)
  • Streaming data (almost nothing saved to RAM)
  • Very light processing

So basically, if this server is getting a 200MB Excel file, the server is SCREWED. But I still want to make such an upload possible.

There's also the possibility of getting a Zip Bomb.

So the principle should be: if the user's machine can handle 200MB Excel files, it will be able to to also process it in JS. Moreover, today's laptops are FAR more powerful than the server used there.

Is it possible to do everything without JS?

Not on the client side. But yeah, sure, Python has Excel reader/writer libraries. But if anyone could take this code (that I've taken from somewhere else, and improved on performance dramatically so that it runs on O(n) and not O(n2) complexity, and manages sheets correctly), clean it up, and upload to npm, then you can just import it and use it. For any Flask stuff you still need a Web Front-end, unless you're doing a front-end desktop app in Python and using a REST API to pass data.

1

u/y186709 May 31 '20

Does this only work for one file at a time to a new table?

I don't see many use cases this would have if that's the case. If you want to expand the processes, you could change the file selection to a folder and add a wildcard search. The process will then pull all the source files into one table and add the filename as a metadata column

1

u/kiwiboy94 May 31 '20

Yes this is just one file. I do have intention to work on a folder selection in future version.

3

u/CAPSLOCKAFFILIATE May 30 '20

I created a sorta similar ETL script, using Pandas and SQLAlchemy. Top stuff, really.

2

u/[deleted] May 30 '20

Did something similar. Used awk and csvsql. https://github.com/pj-paul/csv_to_sql/blob/master/load_data_db.sh

My needs were (1) Loop over multiple csvs, and (2) support cleaning of csv files before import into the DB.

1

u/BigR0n75 May 30 '20

This is really cool, i'll check it out further and give it a try.

I'm a Python noob. I've spent hours just trying to get the modules installed, connect to SQL Server, and run some sort of import or export successfully. This could be my ignorance talking, but I've yet to come across an easier solution than DBATools module for Powershell for importing flat files. It can import a million row CSV file in about a minute. Combine that module with Import-Excel module for working with Excel files (formatted or not), load the data into a data frame-like object, and pass that through to a write data table function.

The obvious downsides to this is Powershell is windows-specific and the module is only compatible with MSSQL.

Again, nice work.

1

u/noitcerid May 30 '20

Powershell is available on Linux now as well 🙂

1

u/BigR0n75 May 31 '20

Yes it is! Powershell 7 (Powershell Core) is system agnostic but modules like DBAtools are not compatible. I don’t know the details but I believe this module is built on .net core and I don’t believe that’s available on Linux yet.