This is standard and documented behaviour of a csv - if you have a cell with a " in it, it is 'escaped' as "" Anything that then reads a csv must unescape such characters.
-----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: 09 February 2012 14:46 To: U2 Users List Subject: Re: [U2] Building an Excel File While everyone is thinking about CSV and Excel, maybe I can get a solution to a problem that's been plaguing me for a long time. First, let me say that it's been my experience that Excel (and Open Office, which emulates Excel /so /well) has a problem with saving spreadsheets with double quotes as CSV. The typical result is 2 double quotes where there was one in the original cell. Saving as tab delimited seems to solve the problem, and appears to be more reliable. That is what I do pretty much all the time. This is my problem: I get a weekly product spreadsheet for my online store, which contains a detailed description. I have found that if the description cell contains a URL link, usually to a YouTube video about the product, that row gets split into 2 CSV lines. I have changed tabs to bars and broken all the lines displayed below into 80 column lines for clarity. Here is an example: Copy and paste of Excel cell: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new innovat ive way to feed the birds. This adjustable Handi-Stand will bring your bird feeder to waist height with the lift of a handle. No need for la dders, stools, buckets, etc to fill your feeder. Adjustable from 5 fee t to 8 feet. Three adjustable heights. Total height of item is 92 inch es and the shortest setting is 59 inches with a middle setting of 70 i nches. The 54 inch measurement is the length of the item when it is bo xed for shipping. Wheel chair accessible. Powder coated steel frame. Although it was all in one cell, when I copied and pasted, it split into 2 lines. This is the second line: Click here to view product video.|689466277531|23.50|2.50|4.00|54.00|2 .50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2010-11 -18 00:00:00|171|171|196|194||||USA URL link referenced in the Excel cell: http://www.youtube.com/watch?v=Sn2JFhAfiPU Tab delimited "Save As" from Excel file shown in a line editor: 3041: Handi Stand|HANDI1|Handi Stand Bird Feeder Stand (Black)|A new i nnovative way to feed the birds. This adjustable Handi-Stand will brin g your bird feeder to waist height with the lift of a handle. No need for ladders, stools, buckets, etc to fill your feeder. Adjustable from 5 feet to 8 feet. Three adjustable heights. Total height of item is 9 2 inches and the shortest setting is 59 inches with a middle setting o f 70 inches. The 54 inch measurement is the length of the item when i t is boxed for shipping. Wheel chair accessible. Powder coated steel f rame. 3042: Click here to view product video.|689466277531|23.50|2.50|4.00|5 4.00|2.50|4.00|92.00|0.00|65.80|131.60||0||HANDI1|HANDI1.jpg|1|1|287|2 010-11-18 00:00:00|171|171|196|194||||USA Comma delimited "Save As" from Excel file shown in a line editor: 3041: "Handi Stand","HANDI1","Handi Stand Bird Feeder Stand (Black)"," A new innovative way to feed the birds. This adjustable Handi-Stand wi ll bring your bird feeder to waist height with the lift of a handle. N o need for ladders, stools, buckets, etc to fill your feeder. Adjustab le from 5 feet to 8 feet. Three adjustable heights. Total height of it em is 92 inches and the shortest setting is 59 inches with a middle se tting of 70 inches. The 54 inch measurement is the length of the item when it is boxed for shipping. Wheel chair accessible. Powder coated s teel frame. 3042: Click here to view product video.","689466277531","23.50","2.50" ,"4.00","54.00","2.50","4.00","92.00","0.00","65.80","131.60",,"0",,"H ANDI1","HANDI1.jpg","1","1","287","2010-11-18 00:00:00","171","171","1 96","194",,,,"USA" I've been working around it by highlighting all the cells in the description column and applying default formatting. That seems to remove the URL link and then everything is fine. Does anyone know of a better way to deal with this? I've asked my distributor to remove the links, but so far they have not. Thanks, Charlie Noah Tiny Bear's Wild Bird Store "Everything For The Backyard Bird Enthusiast, Except For The Birds" Info, Forum: http://www.TinyBearMarketing.com Store: http://Stores.TinyBearMarketing.com Toll Free: 1-855-TinyBear (855-846-9232) On 02-06-2012 6:06 PM, Holt, Jake wrote: > > From what I have seen Excel2010 (I think 2007 did it as well) will > > often > strip off the first occurrence of the double quote in the field and > leave the ending quote, but still get the columns right. > > For instance: test,"he said, "Watch out below."","test2" > > Will show up as > > Col1 test > Col2 he said, Watch out below."" > Col3 test2 > > You have to escape them with an additional double quote to get it > right > > This: test,"he said, ""Watch out below.""","test2" > > Works as expected. > > So the moral is, replace your double quotes with 2 double quotes > before you write surround it with double quotes and write it. > > > -----Original Message----- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of John Hester > Sent: Monday, February 06, 2012 5:45 PM > To: U2 Users List > Subject: Re: [U2] Building an Excel File > > It's been my experience in the past that you can avoid issues with the > comma delimeter as long as you enclose your data in double-quotes. > Supposedly this even applies to data that already contains > double-quotes, although I've never attempted it personally. Wikipedia > has that specific example documented, though: > > http://en.wikipedia.org/wiki/Comma-separated_values > > -John > > -----Original Message----- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, > John R. > Sent: Monday, February 06, 2012 3:26 PM > To: U2 Users List > Subject: Re: [U2] Building an Excel File > > I have always tried to avoid csv files because we have all kinds of > characters in our descriptions (commas, double and single quotes, > etc.) It is also very easy to build a row of data with 254 delimiters, > then simply convert @FMs to tabs. > > That being said, do you have a good subroutine to build csv rows that > will not get confused with commas or quotes in the data? I am willing > to give this a try. > > John Israel > > Sent from my iPhone > > On Feb 6, 2012, at 5:28 PM, "Wjhonson"<wjhon...@aol.com> wrote: > >> Rebuild the files as csv files and they will open on the Iphone >> without any further intervention This is what we do. >> >> >> >> >> > > > > John Israel > Senior ERP Developer > > Dayton Superior Corporation > 1125 Byers Rd Miamisburg, OH 45342 > Office: 937-866-0711 x44380 > Fax: 937-865-9182 > > johnisr...@daytonsuperior.com > > This message w/attachments (message) is intended solely for the use of > the intended recipient(s) and may contain information that is > privileged, confidential or proprietary. If you are not an intended > recipient, please notify the sender, and then please delete and > destroy all copies and attachments, and be advised that any review or > dissemination of, or the taking of any action in reliance on, the > information contained in or attached to this message is prohibited. > > > > > -----Original Message----- >> From: Israel, John R.<johnisr...@daytonsuperior.com> >> To: 'U2 Users List'<u2-users@listserver.u2ug.org> >> Sent: Mon, Feb 6, 2012 2:25 pm >> Subject: Re: [U2] Building an Excel File >> >> >> No, the Smart Phones can not open the XML at all. The txt and xls >> files can be pened, but they are just text. Managers want to be able >> to read the files leanly. >> I am guessing the issue is that when a smart phone opens a true Excel >> file, it s not running Excel, but some sort of emulator. The >> emulator does not have all he bells and whistles that a full blown >> version of Excel has, thus undesirable esults. >> John >> >> -----Original Message----- >> rom: u2-users-boun...@listserver.u2ug.org >> [mailto:u2-users-boun...@listserver.u2ug.org] >> n Behalf Of George Gallen >> ent: Monday, February 06, 2012 4:45 PM >> o: U2 Users List >> ubject: Re: [U2] Building an Excel File Can they open an XML file >> coded for Excel, but without formatting and colors and uch? >> f so, you may just have to have two files produced, one for the >> phones and one or desktops. >> I haven't checked, but are there any perl modules that can convert an >> XML file o an .xls file This would require unix, but not any user >> intervention. >> -----Original Message----- >> rom: u2-users-boun...@listserver.u2ug.org >> [mailto:u2-users-boun...@listserver.u2ug.org] >> n Behalf Of Israel, John R. >> ent: Monday, February 06, 2012 4:40 PM >> o: 'U2 Users List' >> ubject: [U2] Building an Excel File >> We are running UniData on a UNIX platform. We create tab-delimited >> txt files and tab delimited txt files with xls extensions) all the >> time that users open ith Excel. Many of these jobs are run in the >> middle of the night or in a rigger program. In all cases, there is >> no direct user interaction building hese files. >> We also have some XML files that are coded so that Excel opens them >> with ormatting, colors, filters, etc. >> Unfortunately, our BB users and iPhone users can not open these files >> up in a eaningful way. >> Is there a way to build a TRUE Excel file without any user >> intervention from niData or UNIX? >> John >> >> >> John Israel >> enior ERP Developer >> Dayton Superior Corporation >> 125 Byers Rd Miamisburg, OH 45342 >> ffice: 937-866-0711 x44380 >> ax: 937-865-9182 >> johnisr...@daytonsuperior.com >> This message w/attachments (message) is intended solely for the use >> of the ntended recipient(s) and may contain information that is >> privileged, onfidential or proprietary. If you are not an intended >> recipient, please notify he sender, and then please delete and >> destroy all copies and attachments, and e advised that any review or >> dissemination of, or the taking of any action in eliance on, the >> information contained in or attached to this message is rohibited. >> >> >> ______________________________________________ >> 2-Users mailing list >> 2-us...@listserver.u2ug.org >> ttp://listserver.u2ug.org/mailman/listinfo/u2-users >> ______________________________________________ >> 2-Users mailing list >> 2-us...@listserver.u2ug.org >> ttp://listserver.u2ug.org/mailman/listinfo/u2-users >> _______________________________________________ >> 2-Users mailing list >> 2-us...@listserver.u2ug.org >> ttp://listserver.u2ug.org/mailman/listinfo/u2-users >> >> _______________________________________________ >> U2-Users mailing list >> U2-Users@listserver.u2ug.org >> http://listserver.u2ug.org/mailman/listinfo/u2-users >> > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > _______________________________________________ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ----- No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1424 / Virus Database: 2112/4797 - Release Date: 02/08/12 _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
