• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1653
  • Last Modified:

All document - All field - Export to Excel Agent

Can you create a agent that will go through every document and pull information from every field in the form and export that to an excel file?

I know you can do it views, but I don't want to have to create a view with every field (lazy).

Any Help would be great.

Jaz
0
Jaziar
Asked:
Jaziar
  • 10
  • 6
  • 5
1 Solution
 
CRAKCommented:
I've done so once...
2 cycles through the database: the first checks every document in the database and makes an inventory of the fields that are available. Those fields are stored in an array of lists (or something similar). It also ticked off the characters found in the contents of those fields, from a list of ascii codes (0-255). This is to determine free separation characters (14 and 15 often do well!). You can probably skip the larger part of all that.
Before the 2nd cycle started, I created a separate file for each type of document, and printed the headers (fieldnames) . Separated with one of the free separators (coulndn't use commas).
Then the 2nd cycle through all doc's in the database. For each document I retieved an empty set of variables, collected in a list (=fixed order, like the headers! fieldname = key). The value of each field in that document was "imploded" (2nd free separator!), and stored in the list of empty values. This way the documents set of fields would always be supplemented with a set of empty values, that may only occur only in certain other documents (yep... the db I dumped was one huge mess!).
The filled list was appended to the existing file for that document type and erased before I hopped towards the next document.
Worked great! Me and my collegues have already used it to dump a handfull of db's to some kind of "comma" separated files that cold be processed in (in our case) oracle.
But if you choose your field separator carefully enough, you can also read that file in excel.
Beware of the max. no of rows/columns in excel!
0
 
JaziarAuthor Commented:
All I can say is WOW!!

I will start working on it
0
 
CRAKCommented:
Clarification:

1st pass creates following:
Array(0):
  fieldnames("field1")=0
  fieldnames("field2")=0
  fieldnames("field3")=0
  etc.

Array(1):
  fieldnames("fieldA")=0
  etc.

2nd pass created following:
valuelist("field1")="9/5/2006"
valuelist("field2")="3.14"
valuelist("field3")=""
valuelist("field4")="My Name/Org"
etc.
(all converted to text).

I also added an marker (3rd separator) at the end of each line, and replaced certain characters (LF, CR, TAB, Quote) in each field's value. E.g. to drop LF's (for unix), avoid splitting of records over multiple lines in the file, aboid reading problems due to quotes etc.

So you're already impressed!?
Wait till you've got it working! It feels great to have built the single "dump any database"-tool!  ;-))
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
marilyngCommented:
Hi CRAK,

About the only thing I would add, is to check excel limits... :)  I think the rows are 30,000 and not sure about the columns, but there is a limit, I just don't recall what it was when I was dumping a notes document with 6,000 fields on it.

Regards!
0
 
marilyngCommented:
Did you skip hidden and system fields? :)

This is pretty much like the excel export, except instead of using a view's columns, you step through the view to open and export the documents.  
0
 
CRAKCommented:
To be honest, we were working in an environment with a set of regional servers with non-replicating data.
At the end of cycle 1, all recorded fieldnames and used characters were written to documents, that were replicated (yep, the dump-db was a org-wide replica) to a central server.
That one joined those sets to a single list (per doc type) in (again) new, documents.
Those doc's formed the input for cycle 2.
Yes... the complete list of fields could be edited between the two cycles.

It is pretty much like an excel export, but I had to make sure the field order would be the same across all replica's (or should I say copies). And I needed to know what separators I could use: no doubt that ",", ";", ":", "~" and such were already using withing certain text fields. And it doen't depend on a view.... my 1st dump already did 3 (or so) databases. Each of those were under a "design freeze": I wasn't allowed to make any modifications!

Regarding the excel limits:
I mentioned that, didn't I? 1st post, bottom line.
Have't mentioned a number though. ;-))
0
 
marilyngCommented:
Regarding the excel limits<< Ah, so you did, apologies, missed that.  Well, I can tell you the rows.. :)  32,000.  I'm thinking it's 255 columns, but could be wrong.  I'd have to look it up.  Been awhile since I discovered both excel and Notes limits during one three month contract job.  :)
0
 
CRAKCommented:
Are you sure?
End > Down in an empty worksheet shows row 65536.
End > Right takes me to column IV = 256.
Anyway.... those are the trivial details....

0
 
marilyngCommented:
This was excel 97?  The version before 2000,  so the rows increased but not the columns....maybe I'm remembering wrong.. it was all such a haze, especially getting the "bummer dude.. out of fields.." message in every application I was working in.
0
 
CRAKCommented:
Excel 2003. Haven't checked at home (2000).

Jaziar,
I know I didn't write it in a single day, but I wonder how you're doing.
0
 
JaziarAuthor Commented:
To be honest, I have not had the chance to start on it yet.  I have a web based database that the search bar has stopped working.  I can not figure out why.  I am in hopes of starting it tomorrow.

0
 
marilyngCommented:
CRAK,
 
The definitive Excel 2003 Limits:

Open workbooks: Limited by available memory and system resources
Worksheet size: 65,536 rows by 256 columns
Column width: 255 characters
Row height: 409 points
Page breaks: 1000 horizontal and vertical
Length of cell contents (text): 32,767 characters.
    Only 1,024 display in a cell; all 32,767 display in the formula bar.
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook: 56
Cell styles in a workbook: 4,000
0
 
CRAKCommented:
Jaziar.... the dump can be done. What about importing the data?
0
 
JaziarAuthor Commented:
I am just getting ready to start working on the dump.  I am going to increase the points, but I am sure I will have problems.
0
 
CRAKCommented:
I meant to say: if you intend to dump your database to excel.... have you verified that the no. of documents (all, or of a certain type) and the no. of fields (columns) will not exceed excel's limits? Otherwise you might end up in an "operation succesful, but patient deceased" situation.
0
 
JaziarAuthor Commented:
OK CRAK - where do we start?  I am guessing I need to start an agent.  Do I have specify each field by name?

1st pass creates following:
Array(0):
  fieldnames("field1")=0
  fieldnames("field2")=0
  fieldnames("field3")=0
  etc.

Array(1):
  fieldnames("fieldA")=0
  etc.

Would fieldA have to be the actual name of the field in the form?
0
 
CRAKCommented:
You'll need to cycle through NotesDatabase.AllDocuments, using GetFirstDocument, GetNextFocument etc.
For each document you'd need to use the items property to get a list of fields. Create a separate list for each form; the code should be able to decide on that for itself. All lists together an be collected in..... oh well, I might as well send you the entire thing over.
It's on https://filedb.experts-exchange.com/incoming/ee-stuff/91-DbDump.zip 
You'll need to login on ee-suff.com using your normal EE account. The site is obviously related to EE, but doesn't run on the same servers/databases. It only uses EE's acount info. That account info is NOT recorded and certainly not available to me or the public.

The file is a translation (from dutch) of the database that I've used in the past. Please see if you can get it to work.
Start by making a database profile. It contains further instructions.

Enjoy!
0
 
JaziarAuthor Commented:
I got the file and starting to look through it.  It is a nice piece of work!!

Jaz
0
 
CRAKCommented:
Thanks!
0
 
JaziarAuthor Commented:
Very nice - It works very nicely
0
 
CRAKCommented:
;-))
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now