'Comedy Limited' with SQL Server
-
Comments posted to this topic are about the item 'Comedy Limited' with SQL Server
Best wishes,
Phil Factor -
Wrote a RFC-4180 compliant parsing routine a while back, the only one I've seen so far, ever (not) so surprised that there is no native support for a format in SQL Server, which is used by so many Micro$oft products.
😎 -
Microsoft has a history of issues with delimited file parsers which they they don't bother fixing since everybody codes work-arounds to correct the issues.
I wish they would take the brains and code behind CSVed and use that.
-
Great article and very appropriate rant, Phil. I look at the tag bloat for both XML and JSON and the relatively poor performance of both, especially when the data being transmitted is a simple two dimensional (row/column) data set that true CSV would handle much easier and better, and just shake my head.
Shifting gears, I also look at all the brew-ha-ha concerning CSV and TSV and other delimited formats and realize they're only the way they are because humans think they need to read the files and the world of spreadsheets seems to have demanded it. When I first started with data transmission (about 5 years before Bill Gates could even spell "CPM" or "DOS", the hardware wasn't a whole lot better than Teletype technology and so every byte saved during transmission was important. ASCII characters below ASCII 32 played an important part, especially characters 28 through 31.
Ironically, characters 28 thru 31 would still blow even CSV and TSV away for mostly user proof data entry and you wouldn't have to worry about such silly things as "field embedded delimiters or quotes", etc, etc. It would, once again, work so well that I'm considering finding and buying a hat that looks like the following 😀 ...
I'll also state that the transmission of numeric values as text is contrary to performance. People do that simply because they believe that humans should be able to read the file... yeah.... a human is going to "desk check" a million line file for accuracy.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CSV... how about even supporting MS's own Excel standard? In SSMS, why can't I Save a result set as xls/xlsx with properly typed columns?
Like what is discussed here: https://dba.stackexchange.com/q/190898/6229
Very simple for MS to fix. Very frustrating that this has been an open issue for 20+ years. - rafael.zimberoff - Sunday, August 26, 2018 11:06 AM
That would require the teams at MS to actually talk to each other. 😀 Even copy'n'paste between Office products doesn't work reliably for a whole lot of things.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I spent much of my career just working with the data in the database. I really felt dumb when I was tasked with importing and exporting CSV to an external company. It took me much longer than I estimated and I never felt comfortable with my solution. Working my CSV in MySQL is straightforard.
- Mpumelelo - Monday, August 27, 2018 3:45 AM
That's definitely one of the things that I got out of Phil's article. Contrary to popular belief, CSV is still used a whole lot especially in the shop I work in. Microsoft is seriously remiss in thinking they don't need to spend some time on it via T-SQL. The normal answer I get from MS or anyone closely associated with MS (some damned gold partner, whatever) is that we should be using SSIS. My thought on that suggestion is, NNNNNOOOOOOOOOOO!!!!!! We just converted our stuff to all T-SQL and final got rid of the train wreck known as SSIS last year. In previous companies, one of my major tasks was to write stored procedures that did imports because SSIS couldn't actually handle them quick enough.
One example was that an SSIS package was taking 45 minutes just to get one file ready for import to a table. We needed to process hundreds of such files per day and so 45 minutes of file prep time just wasn't cutting it. When I got done with it, we were importing 8 such files, validating their content, and merging the data with the final tables every 2 minutes.
My only regret was the amount of time I had to spend building the tools (and the 8K per row limit in SQL Server 2000 back then). I'd have loved to have some of the tools that MySQL and other database engines have available in them.
To Phil's point, "C'mon Microsoft"! You regularly spend time building features that very few people actually use... Can you catch up on the practical world, please???"
Sorry for the rant but Phil's article reminded me of the huge disappoint I have with Microsoft's poor file handling capabilities when that should be one of the most important and, no, SSIS doesn't cut it for the stuff that I do and neither does (gaaaahhh) XML or JSON bloatware.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
getting tabular data into a text format is a really really important tool for communication.
It would be nice if we could just have a reference document for the issues. Sort of like a stack exchange for problems instead of questions.
If there is a link out there, please let me know.
412-977-3526 call/text
- robert.sterbal 56890 - Monday, August 27, 2018 8:26 AM
It's actually incredibly easy in SQL Server. The problem is that people believe that the main tool for doing so is a security risk. They're wrong but it's difficult to convince people who don't actually know to do otherwise.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OK, I'm probably really dating myself here, but text file import/export is something that I think was actually handle better in the mid 90's in FoxPro for DOS, where it was a 1 line command. Ironically Microsoft bought out Fox software at some point. Guess they never realized what they had.
- Jeff Moden - Monday, August 27, 2018 9:31 AM
What am looking for is a link that can be easily updated to reflect the easiest ways of handling this.
Stack Exchange only lets you address this one question at a time.
412-977-3526 call/text
-
The comma-separated value (CSV) file format ... When done properly, it is the best, probably the only, convention for representing tabular data in a text file. It works.
Eh? What is inherently superior about the comma as a delimiter?
I completely agree that MS has been dropping the ball for years on this.
- robert.sterbal 56890 - Monday, August 27, 2018 11:11 AM
There's the problem, though. Who will do the updating? If you have a public Wiki (which is akin to what you're talking about), it's likely that it will turn into a public food fight between morons and moroffs that think they know the right way or have some warnings based on old wives tales and other misleading information. Especially in this case, "One article to rule them all" is probably not the best idea. Of course, that's just my opinion.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, August 27, 2018 12:09 PM
I have a public wiki, and am better at managing those conversations than starting or contributing to them.
412-977-3526 call/text
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply