Excell and ACL interface

aneder · ‎Oct 23, 2012

Does somebody knows how to read Excell table by ACL code? I need to read an Excell table which is looking as Illustrated Part Catalog, that is it contains rows with cells: callout_number, number_part, name_part, quantity.

Firstly task is looked simple: I saved Excell like csv file and then read lines of csv file as text file by ACL code. But some cells of origin Excell table containes few lines of text in one cell: 2 or 3 or even 4 lines of text, which have been separated by new lineseparator. In such case a string of csv file is interrupted in the separator place and it begins from new line. Structure of csv file becomes complex.

So Ibelieve it is more easy torecognize Excell.

Thanks in advance, if help is possible.

Alexander Neder

lfraley · ‎Oct 23, 2012

Are you trying to maintain the information in Excel and just read it into
the document ?

I think what you're looking for is Data Merge.. We covered that topic in
the Monster Garage last year:

naglists · ‎Oct 23, 2012

Three quick suggestions. The first two work for one-time (or at least
infrequent conversions). The third option would only be useful if you were
going to publish from the spreadsheet frequently "for the rest of time."

1) Try saving your .csv using a different separator. My "favorite" is
usuall the vertical bar | which on a US keyboard is shift-\ just above the
Enter key. This should avoid the problem where the line separators are
being interpreted as cell separators.

2) Try copy/pasting your Excel spreadsheet directly into a table Editor.
Search help for the topic, "Importing Excel tables".

3) Construct a datamerge. Here you essentially pair a table format with a
query that updates the table dynamically. The update can be on open, on
close, on publish, and/or some combination of all three.

The first two options should be easy to do in a short time. The third will
take some development ... not a lot, but some.

Finally, if this doesn't get you where you need to go, let us know and
include what version of Editor you are working with. Some of the copy/paste
stuff is version dependent.

On Tue, Oct 23, 2012 at 12:07 PM, Alexander Neder <aneder@pts-russia.com>wrote:

> Does somebody knows how to read Excell table by ACL code? I need to read
> an Excell table which is looking as Illustrated Part Catalog, that is it
> contains rows with cells: callout_number, number_part, name_part, quantity.
>
> Firstly task is looked simple: I saved Excell like csv file and then read
> lines of csv file as text file by ACL code. But some cells of origin Excell
> table containes few lines of text in one cell: 2 or 3 or even 4 lines of
> text, which have been separated by new line separator. In such case a
> string of csv file is interrupted in the separator place and it begins from
> new line. Structure of csv file becomes complex.
>
> So I believe it is more easy to recognize Excell.
>
> Thanks in advance, if help is possible.
>
> Alexander Neder
>
>
>

ClayHelberg · ‎Oct 23, 2012

Yes, if you are planning to do this on an ongoing basis, then Data Merge
is probably the best way to go.

Another option, if you are good with VB or JScript, would be to access
Excel via COM from Arbortext. You could write code in Arbortext to use
COM to start Excel, load the spreadsheet, navigate the cells in the
sheet, read the values, and do what you want with them in the Arbortext
document.

--Clay

Clay Helberg

Senior Consultant

TerraXML

naglists · ‎Oct 23, 2012

Hi Alexander,

Whoops. Sorry. It looks like I described my process for getting "stuff"
into Excel. Excel can interpret separators other than commas, my favorite
being the vbar. Depending on whether this is a one-time (or only a few
times) conversion/import, it may be possible to use the strategy I've
described, though. You would have to come up with a set of text
find/replace (possibly using regular expressions) in a text editor to build
the guts of your table.

1,2,3

For example could be turned into:
<row><entry><para>1</para></entry><entry><para>2</para></entry><entry><para>3</para></entry></row>

With three find/replaces.

To build on this strategy (which I am not sure is worth your time) you
might have to first find/replace your new line separator into some string
you can change back later, after building the table markup.

1
2,3,4
5,6

For example might have to be first changed to:

1NEWLINE2,3,4NEWLINE5,6

Before being converted to a table. Once a table, you could find/replace
NEWLINE with the newline character.

Anyhow, sorry for the misdirect, but still possible, I think.

On Tue, Oct 23, 2012 at 12:07 PM, Alexander Neder <aneder@pts-russia.com>wrote:

> Does somebody knows how to read Excell table by ACL code? I need to read
> an Excell table which is looking as Illustrated Part Catalog, that is it
> contains rows with cells: callout_number, number_part, name_part, quantity.
>
> Firstly task is looked simple: I saved Excell like csv file and then read
> lines of csv file as text file by ACL code. But some cells of origin Excell
> table containes few lines of text in one cell: 2 or 3 or even 4 lines of
> text, which have been separated by new line separator. In such case a
> string of csv file is interrupted in the separator place and it begins from
> new line. Structure of csv file becomes complex.
>
> So I believe it is more easy to recognize Excell.
>
> Thanks in advance, if help is possible.
>
> Alexander Neder
>
--
Paul Nagai

aneder · ‎Oct 24, 2012

Thanks for all who responded.

I went by simplest way and process csv fileby acl code. (I analise all interruptions and variants in csv and acl code delivers correct table)

Probably I will try to create rev 2 with using com object, as Clay suggested.

Alexander