Python Forum
Excel cell(non uniform) to Database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel cell(non uniform) to Database
#1
Hello,

I am working on an automation that requires following steps:

1 Open file: <Done>
2 Read the content(wherever expected data in sheet(s))<Pending>
3 Copy those content in Database.<Pending>

Approach to do from step 2 onward shall be helpful.

Thanks
Reply
#2
That's very broad question and also related to bullet 1. The way you open the file depends on what package to read excel files you use.
The choice of package would also depend on is it xlsx files or/and old xls files. Some options - xlread, openpyxl, xlwings, pandas
What database - MySQL, MSSQL, Postgres, some No-SQL/graph type, etc? Do you plan to use ORM (e.g. SQLAlchemy, etc)?
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
What method are you using to read xlsx file? What SQL db are you going to? Give some more details or show code please. I am wrapping up an excel automation project so it is fresh (in-head).
Reply
#4
(Nov-27-2018, 02:42 PM)thatIsTheCase Wrote: What method are you using to read xlsx file? What SQL db are you going to? Give some more details or show code please. I am wrapping up an excel automation project so it is fresh (in-head).

As i mentioned, reading the file is done.I have used following packages(at the moment)
Excel package used - Openpyxl
Database - As now i am thinking of Sqlite3(Advise me better options ,if any)

Task pending at my end is to read cells and send them into database via insert statements.I need to know the technical approach to accomplish that.
Reply
#5
What will you be doing with the data? What are the downstream requirements?

There are many approaches but without having more context or an understanding of the ultimate objective, I am not sure which way to direct you. I would recommend using pandas’ read_excel. This you will have a dataframe. If you give some more details of data and objective of your project I will try to help.
Reply
#6
(Nov-29-2018, 06:57 AM)thatIsTheCase Wrote: What will you be doing with the data? What are the downstream requirements?

There are many approaches but without having more context or an understanding of the ultimate objective, I am not sure which way to direct you. I would recommend using pandas’ read_excel. This you will have a dataframe. If you give some more details of data and objective of your project I will try to help.

Objective: validate data provided in excel sheet.
Approach: read the excel and send required data to SQL database, then run pre-composed SQL on that data to identify if the excel that has this data passed or fail .

Any other approach to achieve this and technical requirement ?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python read Excel cell data validation anantpatil 0 4,106 Jan-31-2020, 04:57 PM
Last Post: anantpatil
  Slicing String cell by cell Vigneshkumarsakthivel 0 2,361 Sep-02-2018, 05:59 PM
Last Post: Vigneshkumarsakthivel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020