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
