Don,
Excel cells can hold a lot of data…definitely more than a single cell of an edittable! 
I know. At a conference I once attended I actually saw an Excel spreadsheet where somebody kept a downloaded copy of the whole Internet - in a single cell! 
I don't think your concerns have merit. We've placed highly variable lengths into the Excel document via an HTML export and it works great.
Excel can wrap cell data if formatted to do so.
See below.
Even if the cell didn't wrap, the data itself isn't truncated just the display of the data, which is a different thing altogether.
Sure. The internal version of the data is not truncated. But for some reason I assumed the visual appearance was important to Mark. I must have thought he exported data to Excel because he wanted to print or study the data in a spreadsheet format or layout. Not that the Excel version of the data was just another stop on the road to yet other programs.
However, if I recall correctly, the cells knew to word wrap automatically.
All right. But assume you have a COUNTRY and CITY AMV with the following data:
<code>
USA Las Vegas
Norway Bergen
</code>
You don't want that formatted like
<code>
USA Las
Norway Vegas
Bergen
</code>
You want either
<code>
USA Las
Vegas
Norway Bergen
</code>
or
<code>
USA Las Veg
Norway Bergen
</code>
Can Excel do either automatically?
HTML imports help the Excel columns become much more intelligent whereas straight ASCII imports don't.
Sure. More structure. Of a kind that Excel can understand.
- Oystein -
</QUOTE>
—-
=== At 01 FEB 2002 03:56PM Oystein Reigem wrote: ===
<QUOTE>Don,
Excel cells can hold a lot of data…definitely more than a single cell of an edittable!
I know. At a conference I once attended I actually saw an Excel spreadsheet where somebody kept a downloaded copy of the whole Internet - in a single cell!
I don't think your concerns have merit. We've placed highly variable lengths into the Excel document via an HTML export and it works great.
Excel can wrap cell data if formatted to do so.
See below.
Even if the cell didn't wrap, the data itself isn't truncated just the display of the data, which is a different thing altogether.
Sure. The internal version of the data is not truncated. But for some reason I assumed the visual appearance was important to Mark. I must have thought he exported data to Excel because he wanted to print or study the data in a spreadsheet format or layout. Not that the Excel version of the data was just another stop on the road to yet other programs.
However, if I recall correctly, the cells knew to word wrap automatically.
All right. But assume you have a COUNTRY and CITY AMV with the following data:
<code>
USA Las Vegas
Norway Bergen
</code>
You don't want that formatted like
<code>
USA Las
Norway Vegas
Bergen
</code>
You want either
<code>
USA Las
Vegas
Norway Bergen
</code>
or
<code>
USA Las Veg
Norway Bergen
</code>
Can Excel do either automatically?
HTML imports help the Excel columns become much more intelligent whereas straight ASCII imports don't.
Sure. More structure. Of a kind that Excel can understand.
- Oystein -
</QUOTE>
—-
=== At 02 FEB 2002 08:32PM Bob Carten wrote: ===
<QUOTE>If you want line breaks in cells with tab-delimited data, try converting @vm to Char(10) and quoting everything, e.g.
equ vblf$ to \0A\
equ tab$ to \09\
…
* Cute trick, assumes no need for oconv, no quotes in data
excel_row=quote(@record)
swap @fm with quote(tab$) in excel_row
convert @vm to vblf$ in excel_row
…
I think I did that for somebody and it worked, kept values aligned too. HTML is really cool because you can do fonts, colors, even live formulae. Tab delimited opens faster though. BTW Office XP uses XML as native format. In theory you could make a template, osread it, swap in your data and save it.
Hope this helps
Bob
Bob
</QUOTE>
—-
=== At 04 FEB 2002 09:09AM Oystein Reigem wrote: ===
<QUOTE>Bob,
If you want line breaks in cells with tab-delimited data, try converting @vm to Char(10) and quoting everything, e.g.
Couldn't get it to work. Had no success with HTML either.
Could go into details, but fear it would be a waste of time.
If somebody could supply me with working text and HTML files for the following record of mv data I'd be delighted. When the files are opened in Excel each of the two country names should appear at its own unwrapped line in cell A1, each city likewise in B1:
<code>
USA Las Vegas
Norway Bergen
</code>
Priority:
For me: Not high at present.
For you: Depends on your professional pride.
- Oystein -
</QUOTE>
—-
=== At 04 FEB 2002 02:37PM Donald Bakke wrote: ===
<QUOTE>Oystein,
If somebody could supply me with working text and HTML files for the following record of mv data I'd be delighted. When the files are opened in Excel each of the two country names should appear at its own unwrapped line in cell A1, each city likewise in B1:
USA Las Vegas
Norway Bergen
The description of the above is a bit different than your earlier post. Before the implication was that USA would be in cell A1, Norway would be in cell A2, Las Vegas would be in cell B1, and Bergen would be in cell B2. Now it appears that you want USA and Norway to be in the same cell A1. Similarly, Las Vegas and Bergen should both be in cell B1. Either scenario is possible with HTML-]Excel. If you clarify exactly which way you want it done I'll post code for it.
dbakke@srpcs.com
SRP Computer Solutions, Inc.
</QUOTE>
—-
=== At 04 FEB 2002 04:13PM Oystein Reigem wrote: ===
<QUOTE>Don,
Sorry. I should have posted a screen dump. Then we could have avoided the misunderstanding.
Here you can see the reason I want all mv values in one cell - single value fields sometimes containing long values, causing the second set of mv values to appear unnecessarily far down:
I'd rather have
- Oystein -
Øystein Reigem,
Humanities Information Technologies,
Allégt 27,
N-5007 Bergen,
Norway.
Tel: +47 55 58 32 42.
Fax: +47 55 58 94 70.
oystein.reigem@hit.uib.no
Home tel/fax: +47 56 14 06 11.
</QUOTE>
—-
=== At 04 FEB 2002 04:52PM Donald Bakke wrote: ===
<QUOTE>Oystein,
Okay, I understand what the target is. What does the original data look like in terms of field positions and value marks? For example:
Record="
Record=USA":@VM:"Norway"
Record=Las Vegas":@VM:"Bergen"
or
Record="
Record=USA":@TM:"Norway"
Record=Las Vegas":@TM:"Bergen"
or other?
dbakke@srpcs.com
SRP Computer Solutions, Inc.
</QUOTE>
—-
=== At 04 FEB 2002 08:13PM Oystein Reigem wrote: ===
<QUOTE>Don,
The first one.
- Oystein -
</QUOTE>
—-
=== At 05 FEB 2002 05:35PM Frank Tomeo wrote: ===
<QUOTE>The way to get a line break within an Excel cell IS to use the
tag, however it needs a special style to function properly.
Consider the following code and note the tag in the header portion of the HTML (Oystein… this should duplicate your screenshot):
Equ File$ to "C:\WINDOWS\DESKTOP\TEST.XLS"
Record ="
Record=Long field value wrapping over several lines"
Record=USA":@VM:"Norway"
Record=Las Vegas":@VM:"Bergen"
Swap @VM with '
' in Record
htm ='
htm :=
htm := 'HTML to Excel'
htm :=
htm := ' br {mso-data-placement:same-cell;}'
htm :=
htm :=
htm := '
'
htm := ' ':Record:' '
htm := ' ':Record:' '
htm := ' ':Record:' '
htm := ' '
htm := ''
OSwrite htm to File$
ftomeo@srpcs.com
SRP Computer Solutions
</QUOTE>
—-
=== At 06 FEB 2002 11:27AM Oystein Reigem wrote: ===
<QUOTE>Frank,
Neat. Where can I read about mso-data-placement and other relevant attributes?
But there is one final twist before I get what I really want, which is for each mv value to appear on its own line - unwrapped, even if the value is longer than the width of the cell. Is that possible?
I used PhotoShop for this illustration, but that's inconvenient in the long run.
- Oystein -
</QUOTE>
—-
=== At 06 FEB 2002 11:33AM Donald Bakke wrote: ===
<QUOTE>Oystein,
But there is one final twist before I get what I really want, which is for each mv value to appear on its own line - unwrapped, even if the value is longer than the width of the cell. Is that possible?
Do you mean that you want the column width to be always wide enough to fully display the longest MV entry so that visual truncation does not occur?
dbakke@srpcs.com
SRP Computer Solutions, Inc.
</QUOTE>
—-
=== At 06 FEB 2002 02:47PM Oystein Reigem wrote: ===
<QUOTE>Don,
That's not what I meant. But it can't hurt if I learn that too.
What I meant is:
- In a columnar report there will often be so many fields and so long values that some wrapping or visual truncation is unavoidable
- I want single value fields wrapped, so the whole value shows
- Ideally I'd like to see the whole content of multivalued fields too, but with AMVs it's more important that corresponding values line up. Therefore I want one value per line, and I accept visual truncation of values if that is what it takes.
E.g, if there are two values "Las Vegas" and "Bergen" in a cell, I want them displayed on two lines exactly - no matter how narrow the cell is. If the cell is narrow enough, visual truncation will occur. Only the beginning of each value will show.
Like the way long lines are visually truncated in Notepad.
Or like in a horizontal scroll bar-equipped OI edit box for that matter.
But I haven't had any success with my formatting efforts. I don't think the problem has anything to do with importing data, really, just which formatting options Excel offer. I've tried every setting I found, but either the whole cell content wraps (think of paragraphs in Word), or the whole content becomes one single line (with the undisplayable character symbol between the values).
- Oystein -
</QUOTE>
—-
=== At 06 FEB 2002 04:41PM Frank Tomeo wrote: ===
<QUOTE>Oystein,
I actually found the "mso-data-placement" by editing and snooping though the HTML code that gets created when you save an Excel spreadsheet as a HTML file. There are tons of other attributes, but I don't know where there is a list.
The "word wrap" cell property in Excel is pretty ridged. Either it wraps or it doesn't; there doesn't seem to be a setting to allow multiple lines seperated by breaks to not wrap if the column is too short.
Does your output need to be in Excel or can you use another output option like OIPI32/.PDF?
ftomeo@srpcs.com
SRP Computer Solutions
</QUOTE>
—-
=== At 07 FEB 2002 07:29AM Oystein Reigem wrote: ===
<QUOTE>Frank,
I actually found the "mso-data-placement" by editing and snooping though the HTML code that gets created when you save an Excel spreadsheet as a HTML file. There are tons of other attributes, but I don't know where there is a list.
I tried to search the web, but most hits seemed to be the attribute in actual use, not documents about such attributes. But I'll have another go.
The "word wrap" cell property in Excel is pretty ridged. Either it wraps or it doesn't; there doesn't seem to be a setting to allow multiple lines seperated by breaks to not wrap if the column is too short.
You've confirmed my suspicions.
Does your output need to be in Excel or can you use another output option like OIPI32/.PDF?
I'd like it to be in Excel. But I don't think I've run out of options yet. Here are a couple of untried strategies:
(1)
For some AMV data it's not very important that each value has its own column. For such data I might string together all the fields of the AMV into one symbolic MV field:
This will save some horizontal space too, so the data won't wrap as often as with individual columns.
And by using various kinds of character formatting on each individual value, and/or delimiter characters between the values, I can make the original field values stand out, even if they don't line up any more:
(2)
Have each MV value in its own line, and let single fields have merged cells spanning several lines:
There might be a problem with getting the line heights suitably set, though.
- Oystein -
</QUOTE>
—-
=== At 07 FEB 2002 05:37PM Frank Tomeo wrote: ===
<QUOTE>I think the line heights can be set with those odd "mso" parameters as well. Its easier to make an Excel sheet with the correct format, save it as an HTML file, then edit that and see how it sets it.
ftomeo@srpcs.com
SRP Computer Solutions
</QUOTE>
—-
=== At 07 FEB 2002 09:10PM Oystein Reigem wrote: ===
<QUOTE>Frank,
I did some experiments with one cell per mv value, and cells that span several rows for the single fields. I got a half decent result. But it's sort of bottom-aligned, like:
<code>
Long, long,
long, long,
long, long, USA Las Vegas
long, long, Norway Bergen
long value Norway Tromsø
</code>
and:
<code>
USA Las Vegas
Norway Bergen
Short value Norway Tromsø
</code>
(Don't laugh!) I can also do:
<code>
Long, long, USA Las Vegas
long, long,
long, long,
long, long, Norway Bergen
long value Norway Tromsø
</code>
but the coveted version
<code>
Long, long, USA Las Vegas
long, long, Norway Bergen
long, long, Norway Tromsø
long, long,
long value
</code>
still escapes me.
- Oystein -
</QUOTE>
—-
=== At 08 FEB 2002 07:22AM Oystein Reigem wrote: ===
<QUOTE>Frank,
(This is just my last posting with better illustrations.)
I did some experiments with one cell per mv value, and cells that span several rows for the single fields. I got a half decent result. But it's sort of bottom-aligned, like:
and:
(Don't laugh!) I can also do:
but the coveted version
still escapes me.
- Oystein -
</QUOTE>
View this thread on the Works forum...