I failed to predict this one...new csv format
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
It's because sadly, it's not the good old days anymore where some things are a given. Like CSV.
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
But hey, at least they can post selfies on Facebook!
Jeremy Falcon
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
It sounds like the columns of the rows are being put together in a string variable and the string writing function is wrapping the entire string in double quotes?
-
It sounds like the columns of the rows are being put together in a string variable and the string writing function is wrapping the entire string in double quotes?
Duncan Edwards Jones wrote:
the columns of the rows are being put together in a string variable
That was my guess as well. It could have been easily handled by the other programmer...he knew it was a problem, hence the phone call to me stating that my import isn't working properly with their new file format. :sigh: C'mon man, the thing won't even open properly in Excel! :laugh:
"Go forth into the source" - Neal Morse
-
I think that's more of an ISV file, or Idiot Separated Value file.
What do you get when you cross a joke with a rhetorical question? The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism. Do questions with multiple question marks annoy you???
Brisingr Aerowing wrote:
Idiot Separated Value file
:thumbsup: But here, we take what we can get, which is why it became my problem...add function isTheWholeFrigginLineDoubleQuoted to set a Boolean PleaseRemoveTheFrigginDoubleQuotesOnlyAtTheBeginningAndEndOfThisLine which when true will perform the needed action. (who says you can't have self-documenting code!) :laugh:
"Go forth into the source" - Neal Morse
-
Duncan Edwards Jones wrote:
the columns of the rows are being put together in a string variable
That was my guess as well. It could have been easily handled by the other programmer...he knew it was a problem, hence the phone call to me stating that my import isn't working properly with their new file format. :sigh: C'mon man, the thing won't even open properly in Excel! :laugh:
"Go forth into the source" - Neal Morse
Yeah - I think at this stage I have seen every type of craziness in CSV files - including columns containing the separator in the data with no quotes at all.
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
This sounds a lot like an issue that I encountered about 3 years ago, which gave rise to the [GitHub - txwizard/AnyCSV: Parse ANY CSV String, even X.509 Digital Signature Fields!](https://github.com/txwizard/AnyCSV) library for the Microsoft .NET Framework, about which I wrote two years ago, in [A Robust CSV Reader](https://www.codeproject.com/Tips/987455/A-Robust-CSV-Reader). The library is also available as a NuGet package, at [NuGet Gallery | WizardWrx.AnyCSV 4.0.106.27625](https://www.nuget.org/packages/WizardWrx.AnyCSV/).
David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting
-
There is no standard for "CSV" other than the de facto standard of "whatever Excel will open". Some of the "CSV"s I get have multiple headers and tables.
There is no standard for "CSV". Yes, there is: Common Format and MIME Type for CSV Files[^]. (Admittedly, its a bit of a post facto standard)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
There is no standard for "CSV". Yes, there is: Common Format and MIME Type for CSV Files[^]. (Admittedly, its a bit of a post facto standard)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
That is not a standard for CSV outside of a very limited domain.
-
There is no standard for "CSV". Yes, there is: Common Format and MIME Type for CSV Files[^]. (Admittedly, its a bit of a post facto standard)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
:thumbsup: I'd send this to my client, but it's already been 'fixed'. btw, I actually saw firsthand how the problematic file (each line is enclosed in double-quotes) is being generated...via a SSRS report having all the values in a single column. :sigh: The real world standard for CSV = 'It opens fine with Excel'. :laugh:
"Go forth into the source" - Neal Morse
-
Duncan Edwards Jones wrote:
the columns of the rows are being put together in a string variable
That was my guess as well. It could have been easily handled by the other programmer...he knew it was a problem, hence the phone call to me stating that my import isn't working properly with their new file format. :sigh: C'mon man, the thing won't even open properly in Excel! :laugh:
"Go forth into the source" - Neal Morse
If it won't open in Excel, its because Microsoft didn't plan accordingly. Isn't that the general thought process? Microsoft should account for everything?
-
If it won't open in Excel, its because Microsoft didn't plan accordingly. Isn't that the general thought process? Microsoft should account for everything?
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
kmoorevs wrote:
(generally a sign of formatted numbers)
Wait, what? Quoted text is supposed to be string data, particularly when there are commas in it, not a way of saying 'it's a number, Jim, but not as we know it'. They are really killing that CSV format. I feel your pain.
-
kmoorevs wrote:
(generally a sign of formatted numbers)
Wait, what? Quoted text is supposed to be string data, particularly when there are commas in it, not a way of saying 'it's a number, Jim, but not as we know it'. They are really killing that CSV format. I feel your pain.
Fueled By Caffeine wrote:
Quoted text is supposed to be string data
Yes, sorry I misquoted! :laugh: Yes, what I meant to say was that we apply special treatment to quoted fields/text where a number is expected but never expected each line to be double-quoted. It basically caused my parsing routine to behave in an unexpected way...not too difficult to fix once you see it coming. :)
"Go forth into the source" - Neal Morse
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
Nothing is more annoying than a "programmer" who can't program or do simple calculations. I would have done the same thing. Arguing with these 'dips' is like talking to a wall. Just fix the darn thing and move on. What sucks even more is when you have to pay them and they send you a word invoice. Like have you heard of technology my friend? Plus no matter how properly you schedule in some of these programmers, they would rather slack off than do the job on time. Like last time we had these guys who were working on a DAW and their sound alogrithm was so off that we didn't even move forward with the project.
-
I've been importing from text files, spreadsheets, etc. for over 18 years now. Just when you think you've seen it all a client brings you this: Each line of a *.csv file starts and ends with double-quotes. :omg: Oh yes, we've had handling in place for many years to deal with embedded double-quotes in csv files (generally a sign of formatted numbers)...basically by stripping the formatting between pairs of double-quotes. It's worked great for many years until now...where it unfortunately removed all the commas leaving a big mess...not good! X| Of course, it's my fault! :sigh: It was an easy fix, but I'm still shaking my head over the fact that their programmer who wrote that export told me that he didn't know why it was getting formatted that way, and that it looked fine to him. :| (due to the fact that the double-quotes didn't show up in Excel...nevermind the fact that column A contained a comma-separated list of the items!) I must be getting too old to argue, instantly agreed that it was my problem, fixed, tested, signed, sealed and delivered in < 30 minutes. Jpb security I guess! :laugh:
"Go forth into the source" - Neal Morse
Doesn't that simply import each row as a single field per row? Seems to me your code is working.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Doesn't that simply import each row as a single field per row? Seems to me your code is working.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
Seems to me your code is working.
Getting one field where multiple fields are expected was a problem. :confused: The original logic was just to remove any commas found between double-quotes. Doing this with the single field (of comma delimited values) just pushed everything together and made a mess. A little added logic to detect if the entire line is wrapped fixed everything. :) Easy. The point was that sometimes you just have to take what the client gives you and make it work...that and how lazy (or stupid) the client's dev was for not understanding what the problem was. :)
"Go forth into the source" - Neal Morse
