Python Forum
Load the data from multiple source files to one table - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Load the data from multiple source files to one table (/thread-33457.html)



Load the data from multiple source files to one table - amy83 - Apr-26-2021

Hi Experts,
Have requirement as below. Our clients are going to place multiple files(.xls,.csv, .txt,etc..) in one share drive. Our Python script need to pick those files and load that data to our Oracle Table. For this process have created Python script. My script is only working for picking .xls and convert into .csv and load it into table. But this script is not working for Multiple file capturing and converting all files to .csv and load the data to table.

Please suggest how can we implement a code to satisfy the above process.

Thanks
Amy.


RE: Load the data from multiple source files to one table - Larz60+ - Apr-26-2021

You can start here: https://python-forum.io/Thread-SqlAlchemy-Tutorial-Basic-Model-database-Creation-and-Data-Load

This tutorial covers most everything that you have as your requirements, albeit the data comes from a website, and the database is SQLite. When using SQLAlchemy, the only changes required to switch from one DBMS to another, is a single line to specify the DBMS, and any name changes required for data-types (which should be minimal if any at all).

And of course, the model will have to be written for you tables.


RE: Load the data from multiple source files to one table - Pedroski55 - Apr-27-2021

My amateur opinion:

You need 3 functions, one for each type of file

import glob

path = '/path/to/myfiles/'

files = glob.glob(path + '*')

for f in files:
    if f.endswith('.xls'):
        data = getData_from_xls(f)
    elif f.endswith('.csv'):
        data = getData_from_csv(f)
    elif f.endswith('.txt'):
        data = getData_from_txt(f)
The functions should get whatever data you need and push the data to SQL.
That depends on the data you want and the structure of your tables.

But if you are getting data from a webpage and writing to MySQL on the same webpage, maybe you should be using PHP.

I do something similar, but the other way around, using the module pymysql

I collect data (student number and score) from MySQL and write it to Excel: 164 students online homework scores, collected from my webpage MySQL table and written to my timetable Excel in about 1 second!! I'm always amazed how fast it is!

I never tried using pymysql to write to MySQL, because PHP does that, but it probably can write to MySQL.