[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

importing an excel spreadsheet into a form to generate documents in database. 1 row per document

I have a functionality form defined with approx. 40 fields.  I have a 1 time import needed from a spreadhseet that contains all the data I want in each document.  therefore I would be able to establish views on how to show all the new documents created from the import and of course new ones can be created going forward.  but this first part is a 1 time conversion as I indicated.  My questions is:  What is the best way to do this.??  I believe I need to create a view that contains all the fields in the form in sequential order.  then I need to ensure the excel spreadsheet contains column by column match to the view and required fields in the view would have to have data  in that column of the spreadhseet.  Each row of the spreadsheet would be a saved document in notes.  500 excel rows would equal 500 documents in notes.  so do I open the view in notes and then perform a file import ??? or do I have to convert the excel to a structured text, etc ??
Thank You !
Paul
0
pratigan
Asked:
pratigan
  • 3
  • 3
1 Solution
 
Sjef BosmanGroupware ConsultantCommented:
Basically (and don't hesitate to try), you can import a text file right from the file. I used to use Lotus 1-2-3 format: a column header contains the field name, and an additional column Form for the fixed form name. But the file type can no longer be output from the latest Excel versions...

I assume it's the same with the tabular text format. Try to Export a view in tabular text format, and have a look at the output format. I suppose it's much like csv, so maybe you can use that format when you save the Excel spreadsheet. Do some experiments, the file import feature is quite powerful! You can even use file import format descriptor files, but I never figured out exactly how they work.

See the topics called "How can I import tabular text files? " and "How can I import Lotus 1-2-3 spreadsheet files into views? " in the Notes Help db (not the Designer Help!).

Or here: http://blog.elexie.ch/#post14
0
 
pratiganAuthor Commented:
I did try all of those and the furthest I got was to get the import working but the fields were all mixed up.  I saved the excel spreadheet in a 2003 version and emailed it to my home address where I have excel 2003.  I then opened the file and saved as a WK4 after I had to make a registry entry to allow WK4 formats.  I then emailed the file back to my work email address.  I will be trying to import that file shortly.  I'll keep you posted.
Thank You !
0
 
Sjef BosmanGroupware ConsultantCommented:
Did you also try to use the .COL file? See the last link I posted, which says: "This method allows for extra control of the import process and works for up to 65,000 records. The key idea is that you use a small control file (.COL) that tells Lotus Notes how to import a tab-separated text file."

But the 1-2-3 format should do just fine.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pratiganAuthor Commented:
Hi Sjef... I did not try the COL file.  looked lot alot of work.
However will probably have to if I ever need to do this months or years from now since Miscrosoft no longer supports WK4.  
I was able to get the 2003 version converted and imported successfully into notes.  A round about way to do it but it worked.
Thank You !
0
 
pratiganAuthor Commented:
Thank you for your continued support !!
0
 
Sjef BosmanGroupware ConsultantCommented:
You're welcome!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now