I'm working on the project. I need to create a website. On it, my colleagues will be able to upload files with data in Excel format on the server. This will be a table with standardized column names. These files need to be attached to the database. The data is stored in SQL format. I will need to process this data and show some statistics on them. I plan to implement these transformations on the python. As a result, i will need to display certain graphics. All this will be done on the server of the company. I didn't have to solve such tasks before. Advise me, please, how can I convert new files from Excel to sql on the server? And how to automate this? I could not find anything about it. And also what language is best used to create a site so that it can work with the python and the sql?
-
1SQL Server has a OPENROWSET "method" that can be used for things like this – Anthony Horne Jun 22 '17 at 10:04
-
1See: https://stackoverflow.com/a/9904172/1662973 – Anthony Horne Jun 22 '17 at 10:07
-
1You can then process the transformations on a raw xls table in sql OR you can use SQL procedures - you have a choice. – Anthony Horne Jun 22 '17 at 10:08
-
"also what language is best" - did you mean "framework"? – Paul Jun 22 '17 at 10:23
-
@Paul yes, I think to choose Django,but I decided to find out, maybe there might be a more suitable option. – yanadm Jun 22 '17 at 10:29
1 Answers
I'm not sure how to automate it more clean and idomatic for SQL-Server (maybe it has some built-ins to handle excel files), but maybe this thoughts may help you as a more 'general' approach:
1) Since you want python, the best web-framework for the site would be Flask or Django. It depends on what functionality you would like to add to this site later. Current task looks like Flask is ok for it, since it is smaller and simplier than Django.
2) There are a lot of python libraries to work with excel for python. Here is a question and answers about parsing excel files with python: parsing excel documents with python I would say that it makes sense to create some web form where user uploads excel-file, and you placing it on the servers disk (and it makes sense to rename that file somehow to avoid overriting files with the same names) into predefined directory.
3) Next step - to read that file from disk with one of tools for parsing excel files and get needed columns from it. Maybe you would also want to reformat that data somehow.
4) Final step - create a pure SQL query with python (there are a lot of libraries for it too, I guess) with the data you got from file to INSERT it into some table. If you do not want to work with pure SQL queries, you can take some ORM to work with it. Like SQLAlchemy. But it looks like overhead for this task for me.
Here also a documentation on how to work with file uploads with Flask: http://flask.pocoo.org/docs/0.12/patterns/fileuploads/
- 6,641
- 8
- 41
- 56