Developers with C# , VBA, and Python want to send the data into database

Question

I have a question, I have so much confusing. I am recent graduate, started working in a company there lots of old data in text files, so I have organised all these data by using Python and i have generated a excel file. so for the situation good.

The problem, for new incoming data of sensors from different developers

1. Most of the guys using VB scripting , people test the data and saving data in text files by using delimited (, and |).

2. some other guy developing projects in C#, these people got some different kind of text data with different delimiters(: and ,)

The question I have to set up the database, how can i take all these developers data , automatically into the data base, for these text data I have created column names and structure for it.

Do i need to develop individual interface for all these developers or is there any common interface i have to focus or where to start is still lacking me.

I hope people out there understood my situation.

Best regards Vinod

Answer 1

Since you are trying to take data from different sources, and you are using SQL Server, you can look into SQL Server Integration Services (SSIS). You'd basically create SSIS packages, one for each incoming data source and transfer the data into one homogeneous database.

Answer 2

These type of implementations and design patterns really depend on your company and the development environment you are working in.

As for choosing the right database, this also depends on the type of data, velocity, volume and common questions your company would like to answer using that data.

Some very general guidelines:

1. Schematic databases differ from "NoSQL" databases
2. Searching within a text is different than searching simpler primitive types and you have to know what you will be doing!
3. If you are able to, converting both VB scripting & C# text files to popular data structure files (i.e JSON, CSV (comma delimited text file is the same)) might help you with storing the data in almost any database.
4. There is no right or wrong in implementing a specific interface for each data supplier, it depends on the alternatives at stake & your coding style
5. Consult with others & ready Case Studies!

Hope that's helpfull

Answer 3

You should consider using Excel’s Power Query tool. You can intake almost any file type (including text files) from multiple locations, cleanse and transform the data easily (you can deliminate as you please), and storage is essentially unlimited as opposed to a regular excel spread sheet which maxes at around 1.4 million rows.

Answer 4

I don't know your company's situation but PDI (Pentaho Data Integration tool, is good for transformations of data. The Community Edition is free!

However, if you generated them into an excel file Power Query might be a better tool for this project, if your data is relatively small.