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
  python move specific files from source to destination including duplicates mg24 3 247 Jan-21-2023, 04:21 AM
Last Post: deanhystad
  Find duplicate files in multiple directories Pavel_47 9 535 Dec-27-2022, 04:47 PM
Last Post: deanhystad
  extract table from multiple pages sshree43 8 1,193 Dec-12-2022, 10:34 AM
Last Post: arvin
  SQL Alchemy help to extract sql data into csv files mg24 1 258 Sep-30-2022, 04:43 PM
Last Post: Larz60+
  Load multiple Jason data in one Data Frame vijays3 6 645 Aug-12-2022, 05:17 PM
Last Post: vijays3
  Apply textual data cleaning to several CSV files ErcoleL99 0 430 Jul-09-2022, 03:01 PM
Last Post: ErcoleL99
  Extract parts of multiple log-files and put it in a dataframe hasiro 4 927 Apr-27-2022, 12:44 PM
Last Post: hasiro
  panda table data kucingkembar 0 572 Mar-01-2022, 10:38 PM
Last Post: kucingkembar
  Sorting table data Blacktime2 1 770 Feb-26-2022, 07:05 PM
Last Post: ibreeden
  Search multiple CSV files for a string or strings cubangt 7 4,186 Feb-23-2022, 12:53 AM
Last Post: Pedroski55

Forum Jump:

User Panel Messages

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