nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Extract excel cell data

msrinivas

Use case: We have 100's of excel spreadsheets that are identical in format, meaning cell layout matches in all of them. We are trying to extract cell data from them and apply as EA's on files in TS.

I know we can easily write a script to do this but we are trying to leverage the magic of MT to do this. Is there a way to accomplish this using MT?

TIA.

Find more posts tagged with

Comments

nipper

Hey Srini

I tried this a couple years ago, I know the MT can crack an Excel spreadsheet, but I was not able to get specific columns/rows, though I assume Morgen will know for sure.

We scripted it. Perl opens of an Excel file are a pain (used perl Smiley Very Happy

BD/DBI), if I have to do it again I would save the spreadsheets as XML or CSV and parse it that way.

msrinivas

Thanks for the tip, Andy. Waiting for Morgen..... Smiley Happy

customers.rptdesign

customers.xls

Migrateduser

I have actually never tried this.
If the cracked file retains the tabbing then it should be fairly easy to do in MT.

Write a perl script that identifies the cell you want - via row and col umn (using tabs). Grab the cell (tab to tab). Hand it to MetaTagger to store in a field. Repeat as necessary.

So MT can crack the excel into text, manage the process and the fileds. Perl or your favorite runtime will have to do the manipulation and regex to capture.

msrinivas

Thank you for the reply, Morgen. I have been able to crack the excel sheet and read data. I am trying to pass them over to MT. I am using the date pre-processor as an example.

Migrateduser

That is exactly right. Let me know if you have any other issues with it.

reqd.JPG

msrinivas

Morgen,

How can I configure a new content processor for this same use case in which I can pass the cracked text to my custom pre-processor?

I was able to get the metadata that I need by doing the CLT steps (iwmttransconverter, iwgenmetadata and type) manually but when I try to configure a custom content processor and use the CIWeb application against the same file I am not getting the metadata.

TIA.

Migrateduser

The cracked text will automatically be passed to the preprocessor stage.

So your Content Processor, let's call it - TOM, should look like this:
TOM =
1. FileTypeGroup: Binary_Files (this equals a list of binary files you want cracked - like xsl, doc, pdf.)
2. Transconverter: [leave this blank to use the automatic OOTB transconverter]
3. Preprocessors Stage: Brinary_pre (list of scripts that do little jobs - getTitle.pl -> getCell.pl) put values into metadata fields = myCells, myAuthor, myTitle).
4. Projects: (list here all the fields you want MT to populate. This is so these items are sent to requesting servers.)
- myCells [Name = myCells, FieldType = Atribute]
- myTitle [Name = myTitle, FieldType = Atribute]
- myAuthor [Name = myAuthor, FieldType = Atribute]
5. Postprocessors Stage: Binary_post (list of scripts that do little jobs - cleanIMD.pl -> addAuthor.pl) manipulate values in metadata file, field name = myAuthor.
6. Final Processors Stage: Can leave blank or if you have a vocabulary in your Content Processors use iwmtresolveUIDs.

The result of this is this metadata record:

<metadata>

english
utf_8
input\spreadsheetX.xsl

myTitle
string
War and Peace

myCell
string
555-1212
555-5555

myAuthor
string
Joe Q Employee