I have a bunch of excel files(Multiple sheets) with some data of similar types, but i'm having troubles when this data has to be inserted in SQL DB. the data in these files are unordered (eg: the table header names are different,the columns are in unspecific order, the orientation of data is different, etc ). The excel files are converted into JSON object and sent to server, but the challenge is how to bring all the data in the sheets in one standard form and insert it and automate it
-
This question would heavily improve when you could tell us where the different excel files come from, who defines them and their structure and how much control you have over this process. It would improve even more if you tell us what you tried, how far you got and which unsolved problem you are facing now.– Doc BrownSep 4, 2020 at 21:11
1 Answer
- For each distinct source, write some code which normalizes data from this source. Insert the normalized data into the database.
- Realize that this doesn't scale, and request that the sources provide data in a common format so you don't have to write a normalizer for each one.
- Realize that your sources are not under your control and won't adapt to your needs. Go back to step 1.
Seriously, standardization of input formats and individual converters for non-standard formats are your main options. Companies offering data processing tools might have some which they claim will do it automatically for you. I wouldn't trust them, it's hard enough to get data interpretation right when you interact directly with the data provider to get an agreement on the interface, and automated tools cannot communicate to find the correct interpretation, they need to guess.