Ingesting an EXCEL spreadsheet into ERR

[DDP021]

Can anyone provide the procedure for ingesting a NON indexed EXCEL spreadsheet into OnDemand8.4?....Basically all we're trying to do is 'dump' a spreadsheet into CMOD...No indexing....Currently we are using ARSLOAD to ingest both text, AFP and PDF documents...We had an inquiry from a user to load an excel (XLS) spreadsheet...any help or specific direction would be appreciated.

[jeffs42885]

Can the file be saved as a CSV file? If so, I would assume you would just index it like a text file.

[DDP021]

That I'm not sure....The question was just raised if we could ingest a true EXCEL spreadsheet...my guess is they don't want to do anything on there end other than providing us the spreadsheet itself.

[jeffs42885]

I just tried doing this, It did not open properly with the indexer. (xls file)

My advice would be to load the file with a generic index file that contains rname, rdate...etc..and tailor your ARSLOAD to it.

If not, some type of third party product might need to be used..I think XENOS can do this, though I have never done it.

[Alessandro Perucchi]

Hello,

you can ingest whatever format you want in OnDemand, just be sure for such non indexed data to choose "User Defined Data" with the extension "xls" or "xlsx" in the application, and choose "Generic Index".
Meaning that you must provide a minimum index with it like, "date" and "report type" for example.

Sincerely yours,
Alessandro

[DDP021]

Alessandro,  I had attempted previously to do what you suggested..I defined the application with the view information as 'user defined' and xls as the application...I also set the indexer as Generic..I've read in other posts when the generic indexer is used, a separate index file must be created...I'm just not sure how it is to be formatted or where it is stored...Currently we have applications who send pdf files directly to our OnDemand server under an ARSLOAD library which then automatically gets ingested into Content Manager OnDemand by ARSLOAD...A majority of the data going to OnDemand currently is coming from the mainframe and the files all end in .ARD...I believe out systems area had to update something in ARSLOAD to allow the automatic ingestion of PDF's...The format of the files are Application.ApplicationGroup.pdf..I'm assuming with the XLS files, something will need to be updated in ARSLOAD to accept a file formatted as Application.ApplicationGroup.xls...But again, the question I have is regarding how to provide this minimum index file needed, its format and where it's stored.

[DDP021]

Alessandro, here is a copy of the 88 message I receive with just having the application defined with "user defined" and the generic indexer...It seems to point back to the indexer file but as my previous post indicated, not sure how to define that piece...

arsload: 06/06/12 09:18:36 -- Indexing started, 432128 bytes to process
arsload: Unknown Indexer ><
arsload: Output/Indexer file was not created
arsload: 06/06/12 09:18:36 Indexing failed

[rstockton]

We use the method defined by Alessandero - user defined, xls and generic indexer - with .ARD, .IND and .OUT files with the .IND containing the index inofrmation.

Regards,
Russell

[DDP021]

Russell, do you have any examples?..Currently we have users sending PDF files right to our PROD ERR server (to an ARSLOAD) directory...They format their files as ApplicationName.ApplicationGroupName.pdf...Once the file gets to the ARSLOAD directory, it is automatically ingested...We obviously define the ApplicationName and ApplicationGroupName...The difference being with it being a PDF, the application contains the indexing information which we obtain by running the data through the report wizard on the ADMIN client..We would like to accomplish the same thing with an XLS file..(ex ApplicationName.ApplicationGroupName) minus the indexing......I'm just not sure how you incorporate all these files...Especially the .IND file that contains the indexing...I've not been able to find a true example of this kind of file and how it would get incorporated in the process...Specifically where it's defined and stored...

[rstockton]

Following is an example of how we load files to OnDemand.


Our user application sends us the following files.  The .ARD is the trigger file, the .IND is the Index file and the .OUT file is the data file.  These three files are dropped into a loading zone and picked up by arsload job for the application group.

ADVCLAIM.PD.ACLAIMINB.ACLAIMINB.D030312.T18300200901207908.ARD
ADVCLAIM.PD.ACLAIMINB.ACLAIMINB.D030312.T18300200901207908.ARD.IND
ADVCLM/ADVCLAIM.PD.ACLAIMINB.ACLAIMINB.D030312.T18300200901207908.ARD.OUT


.IND file example

CODEPAGE:850
GROUP_FIELD_NAME:InboundDataType
GROUP_FIELD_VALUE:1
GROUP_FIELD_NAME:File_type
GROUP_FIELD_VALUE:BIN
GROUP_FIELD_NAME:CustomerID
GROUP_FIELD_VALUE:xxxxxx
GROUP_FIELD_NAME:Processed_date
GROUP_FIELD_VALUE:03/03/12
GROUP_FIELD_NAME:Processed_time
GROUP_FIELD_VALUE:183000000002
GROUP_FIELD_NAME:FileID
GROUP_FIELD_VALUE:36355375
GROUP_FIELD_NAME:TransID
GROUP_FIELD_VALUE:TRB3B2F0821000526
GROUP_OFFSET:000000000
GROUP_LENGTH:213
GROUP_FILENAME:/arsacif/load3/ADVCLM/ADVCLAIM.PD.ACLAIMINB.ACLAIMINB.D030312.T18300200901207908.ARD.OUT

[DDP021]

Thanks Russell.....What I'm attempting to do for now is just ingest a sample Excel spreadsheet with NO indexing...So on my Application Group the only fields I have defined are POSTING DATE, REPORT NAME, REPORT DESCRIPTION, LOAD DATE and PAGE COUNT.  These are all FILTERS.  For our normal text files the .ARD is the actual data file. From what you indicated, the .OUT is the actual spreadsheet data file... Forgive my ignorance, but not familiar with a trigger file..with never having used a separate index file (.IND), just not sure how/where it's defined.  And once i get past that point, will it have to be supplied to the end user who is actually sending the spreadsheet data to our server to be ingested by ARSLOAD?....

[DDP021]

Russell,  what fields need to be defined in the .ind file?...As indicated, this is an excel spreadsheet with NO indexing...I'm basically attempting to ingest it into Content Manager OnDemand8.4 as is...Does the .ind file need to contain all the fields defined under the application group?...It appears from your example you are specifying values for these fields...The 'fields' I have defined in the application group are:
POSTING_DATE
REPORT_NAME  <  Report name defined
DESCRIPTION  <  Description of the report
LOAD_DATE
PAGE_COUNT

For the POSTING_DATE and LOAD_DATE we set the Default Value with a lower case 't' on Load Information tab on the Application definition.

I just don't know what I have to have specified on this .ind file...

Also, when you define it, do you simple use notepad and then save it in the name format needed, (ie ApplicationGroup.Application.ARD.IND).?

[Stephen McNulty]

If you look in the Indexing Reference manual (on any platform)  look to the chapters on Generic indexer reference.  You will see the parameters listed, as well as sample index files.  and yes, any editor will do.

[Alessandro Perucchi]

DDP021,

CMOD is a structure archiving system, meaning that you MUST provide an index with your file.
If your user don't provide an index, then you must provide a mechanism that will create the generic index that Russel provided as an example.

What I do sometimes with my customer is create a script that at the end launch "arsload" manually.
And before launching arsload, it will create an index automagically for the files that doesn't have indexes. But to create such index, then you must have some naming convention, index convention, etc... and then you can create your script that create the index for you.

But basically, no index, no archiving of documents.

The ONLY thing I might imagine is that, with the explanation I gave you (APP with User Type : xls  + Generic Index)
Then you give default values for "Report Type" like "Excel report"
and for the date you give the value "t"   <- not a typo, really the letter "t", which means "today"
If you want for the date, you might set it up in the application group as a date/time... so it might be easier to find the document you want.

1) create a script that check every minute if a file is in the input directory.
2) check if this is a .xls
3) create a index with that template:

CODEPAGE:923
GROUP_OFFSET:0
GROUP_LENGTH:0
GROUP_FILENAME:<your_file_that_you_have_found_in_the_input_directory.xls>

Name it for example:   myfile-xls-20120618221902.ind
4) call arsload:
      arsload -I archive -u admin -p password -g myAG-XLS -a myAPP-XLS -nvf myfile-xls-20120618221902

it will archive automatically without any index, because they are automatically generated in the application with default values.

But again, you need something... without anything it won't work.

If it's not clear (it could be... It's quite late for me right now!!) tell me, and I will do a full explanation with all details.

Sincerely yours,
Alessandro

[DDP021]

Alessandro,  thank you (and Russell) for all the information...I finally was able to get things to work...I didn't realize at first the trigger file was simply a blank file...Once I realized that I created it, along with the index file (.ind)...My index file simply contains just the Codepage, Group offset, Group length and Group file name information.  Currently our systems group have files ending with .pdf configured to automatically ingest with ARSLOAD...So here's what configured:

TEST.TEST.PDF  < Empty file
TEST.TEST.PDF.IND < within this file I have the complete path and data file name (TEST.XLS)
TEST.XLS < Actual data file

I copy the TEST.XLS to one of our ARSLOAD directories on our server first
I then copy both the TEST.TEST.PDF and TEST.TEST.PDF.IND to the same directory as the TEST.XLS file.
ARSLOAD then automatically ingests the file.  After ingestion is complete, the TEST.TEST.PDF and TEST.TEST.PDF.IND files are deleted.
We just need to work with our systems group to configure ARSLOAD to also delete the TEST.XLS file after ingestion

We loaded not only XLS but also TXT and CSV file types configuring the view information on the Application setup with the file extension it should be opened with in CMOD.

Again, thanks to everyone for all the valuable information you provided. 

Take care.

Dave