Python Forum
Load the data from multiple source files to one table
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Load the data from multiple source files to one table
#1
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.
Reply
#2
You can start here: https://python-forum.io/Thread-SqlAlchem...-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.
Reply
#3
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Open and read multiple text files and match words kozaizsvemira 3 3,782 Jul-07-2021, 11:27 AM
Last Post: Larz60+
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 235 Jul-02-2021, 02:19 PM
Last Post: xtc14
  Plotting Multiple files ! Helen_145 1 229 Jun-26-2021, 03:28 PM
Last Post: snippsat
  Reading Multiple text Files in pyhton Fatim 1 254 Jun-25-2021, 01:37 PM
Last Post: deanhystad
  Plotting sum of data files using simple code Laplace12 3 448 Jun-16-2021, 02:06 PM
Last Post: BashBedlam
  Running script on multiple files Afrodizzyjack 1 387 May-14-2021, 10:49 PM
Last Post: Yoriz
  Need help on extract dynamic table data Dr_Strange 0 558 Apr-30-2021, 07:03 AM
Last Post: Dr_Strange
  API Gateway to manage multiple API's get put data robsuttonjr 2 582 Mar-09-2021, 04:09 PM
Last Post: robsuttonjr
  Pandas: how to split one row of data to multiple rows and columns in Python GerardMoussendo 4 1,168 Feb-22-2021, 06:51 PM
Last Post: eddywinch82
  Rename Multiple files in directory to remove special characters nyawadasi 9 795 Feb-16-2021, 09:49 PM
Last Post: BashBedlam

Forum Jump:

User Panel Messages

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