|
Html table --> DataReader --> SQL Server via BulkCopy
Profit.
|
|
|
|
|
You're working too hard. 
One of the utilities I've been working on at work recently is a very simple ETL tool.
Source Destination
CSV Y Y
JSON F F
Sharepoint (list) Y N
SPLUNK F N
SQL Server Y Y
SSAS (OLAP) Y N
XHTML Y Y
XLSX Y N
XML Y Y
Y=Yes , N=No , F=Future support possible
Each "source" provides a DataReader and each "destination" reads a DataReader.
One design decision I made was not to use any third-party technology (e.g. Newtonsoft), it's all .net -- it doesn't use interop either.
I intend to add reading and writing JSON to it once I have .net 4.7 or newer -- which supports JSON natively.
The point being that opening an XLSX file and wrapping a "worksheet" in a DataReader is not all that difficult -- non-trivial, yes.
With the DataReader, you can create and populate a DataTable, but what do you do with it then?
|
|
|
|
|
Great stuff 
I'm just working on a similar thing where you can also inject a database scheme and with this it does recursively collect the data from any starting record from a table. Additionally this part does also support the mapping of 'internal technical id' (usually an integer in my case) against 'user id' (usually a nvarchar(n) in my case).
Nice challenge to solve it in general for compound keys of any data type, detect self references on the same record, detect circular references...
Quote: With the DataReader, you can create and populate a DataTable, but what do you do with it then?
o Briefcase to exchange data (e.g. customer has a specific problem and you need to debug)
o Importing data from e.g. Excel (often ask by customers)
o ...
It does not solve my Problem, but it answers my question
Chemists have exactly one rule: there are only exceptions
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Definitely my favourite format for passing around information too.
It makes me wonder who would want to use XML, or rather why on earth XML became so popular when JSON simply lets you pass objects around between different platforms/services.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Because XML is more robust, it allows finer granularity than JSON, the ability to apply attributes to values is vital in many cases.
SQL Server has an XML datatype and functions for working with it.
When I read a JSON file, I convert it to XML so I can store it in SQL Server and query it.
JSON is fine for simple data transfer, as it is intended, certainly better than CSV.
Use the right tool for the right job.
|
|
|
|
|
Sure, one of the advantages of xml is that you can check that it is correctly formed before importing it while JSON is much more "free".
Interestingly enough I had to do some work recently deserialising some XML and the advantage was that it was fairly straightforward given the libraries available in .NET
My preference towards JSON is that I find it easier to read, if I am having to debug data, and javascript likes it.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Yeah, an XML file can be expected to be well-formed, but that's not necessarily the case with CSV, HTML, and JSON.
What drives me nuts about XML is namespaces -- they're best avoided in most cases.
|
|
|
|
|
Not to forget that XML has been around since the 1970s, and JSON was only described in the early 2000s by Douglas Crockford et al.
So in the meantime, A LOT of applications went with XML as the default for exchanging data.
-- Peter.
|
|
|
|
|
Good point, it also seems to have taken a long time after 2000 for devs to trust using JSON.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
I agree, it really is so much better than XML.
|
|
|
|
|
I first read that as "I am coming to seriously love JSOP" !!
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Let me guess who the one up-vote is...
|
|
|
|
|
|
I liked it until i had to do fixed length allocations with it (fixed number of elements) in my IoT stuff. Now I kind of find it a pain.
Real programmers use butterflies
|
|
|
|
|
better late than never, I guess.
|
|
|
|
|
I discovered the simpleton's beauty in it a year or two ago.
The need to get multiple pieces of data back from AJAX. It can do much more but I like to imagine it was designed and built for this purpose - where only one string comes back. No more worries with parsers where where, no matter what delimiter I choose some jackass user decided they must have it in their input. Even '|' had its sanctity violated. (I gave up and sanitize all user input). JSON gets rid of that for me in the name of "going forward".
One oddity in my use: I typically build the JSON stings from my own code but do convert them back with the ubiquitous version of it that exist it most languages (that I care about).
One thing - you'll likely do your own research: SQL Server actually allows a JSON type - a type that can be searched with with SQL WHERE clauses. So I asked my fearless leader, a particularly good DBA, what he thought of it. Apparently, as he sees it, the overhead involved will be horrific in terms of performance.
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
The only thing I've noticed, is that there are more JSON questions in Q&A than XML.
My last web service dealt with USPS, FedEx and UPS: all XML; sending and receiving.
I serialize and deserialize my app settings: XML
Post performances at the race track: XML
etc.
My main complaint with XML, a little more verbose; i.e. no shortcut ending tag. But since it's all generated from class definitions, the only drawback is in my head.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
for you, and your families, friends, neighbors ... [^]Quote: Toronto, Canada's largest city, was placed into lockdown for 28 days on Friday, with officials shutting shops, businesses and restaurants and banning indoor gatherings to curb a growing spike in Covid-19 cases.
«One day it will have to be officially admitted that what we have christened reality is an even greater illusion than the world of dreams.» Salvador Dali
|
|
|
|
|
But ... but ... who will feed the hamsters, and change their bedding? 
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
OriginalGriff wrote: who will feed the hamsters
For CP staff locked in the building, more like "the hamsters will be fed with whom?"OriginalGriff wrote: and change their bedding
The hamsters won't eat the clothing, obviously, so that will serve as bedding.
Software Zen: delete this;
|
|
|
|
|
Only some of them are Killer Attack Hamsters - the rest are standard Robo Hamsters[^] which don't eat much meat.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Thanks, Bill-ji. This is serious stuff... 
/ravi
|
|
|
|
|
Ravi Bhavnani wrote: Bill-ji
I always suspected Bill might be one of the atevi[^].
Software Zen: delete this;
|
|
|
|
|
Yay. Someone else who reads CJ.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
