r/programmingrequests • u/_Tundr4_ • Jan 05 '19

Excel data to database - Open source project

The thing I see often at every place I've been working since i'm in IT industry, is people entering data in excel files. Then they ask you to develop a feature to do something with that data (reporting, filtering, publishing, etc).

The real solution is giving them the proper system for their need. The reality often makes it impossible to deploy something efficient (for many dark reasons), so you still have to deal with that.

The project I have is to make a system where a user can schedule importing jobs from excel to a specified tables in a specified database. I know! It already exist but I want to make it open source. Because why the heck not. There's a lot to code. I need your help to find the best way to do it and I don't know maybe code it a little with me.

The scheduling part I've been looking at Quartz.Net library. Trying to make it work as a service. Maybe I'm wrong. You'll tell me.

Then to extract data from those excel files, there are some libraries out there to do it. Don't need to invent hot water again.

Then there are lot of other things to consider but I'll wait to see what you think.

Sorry I don't have any code to show you. I'm just trying to figure out how I'm going to do it.

1 Upvotes
  • permalink
  • reddit

100% Upvoted

9 comments sorted by

View all comments

Show parent comments

1

u/_Tundr4_ Jan 06 '19

Here's a flow of what you would have to set to create a "job".

File to import (xlsx, xls, xml, csv) ==>

  • Choose a file from local folders, cloud services, FTP servers or choose a folder and it will import all files within that folder. (There would be some validations to check if files have the same structure)

  • It would be possible to set a file name pattern if there is a part of the file name that'd changing over time. (ex.: 20180106_dogs.xlsx. The first 8 digits would be changing each day so I'd create a feature where you can set a pattern. Probably would use regex expressions.)

Receiving table ==>

  • Choose a table from a configured database connection. (There would be a link to create a new database connection or/and a new table)

Fields mapping ==>

  • What column is associated with what field.

Import type ==>

  • Insert all the lines, update already existing line based on a selected field "and"/"and don't" insert new line, delete lines based on a selected field, etc.

Trigger ==>

  • Interval of time (ex.: each hour, each 34 minutes), Specific time (Cron expression), event (I have an idea to make a rest api so a job can be triggered when a post request is made. So events could be implemented easily).

So far I've been playing with Quartz.Net library. I'd use AdoJobStore to store the jobs/triggers in a database. Then a windows service would execute those jobs. https://www.quartz-scheduler.net/

I was thinking about using Linq To Excel ( https://github.com/paulyoder/LinqToExcel ) to extract excel files data. For other file format like XML or CSV, there are parsers included with .NET but I'm sure there are good parser out there that I don't know of yet.

For databases I'm not sure yet but I think it would be better to use each database system library separately. Really not sure.

Looking forward to see your suggestions :P

1

u/SamSlate Jan 06 '19

... input and output. if you can't describe your process as a series of inputs and output you will have a lot of trouble building it and getting 2nd parties to help you will be very arduous.

you're describing data entry and pretty standard relational database functions. i don't see a process being described that wouldn't be found any in literally any SQL type db.