Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
excel data coversion
- Thread starter moonie
- Start date
corporatepunkrocker
Programmer
Moonie,
Just a suggestion but I've always had better luck importing data from fixed-field length formatted text files, rather than Excel files. I would recommend structuring your data and then converting your Excel file to a straight text file. From there, you could write a LotusScript routine which would grab data from each "field" in your text file, and perform a lookup to an existing document by using any text key you desire. If you want more info on writing the import Script or configuration documents to process the incoming data and position data into the proper fields, write back. I'll help as much as I can.
Cheers,
Simon
Just a suggestion but I've always had better luck importing data from fixed-field length formatted text files, rather than Excel files. I would recommend structuring your data and then converting your Excel file to a straight text file. From there, you could write a LotusScript routine which would grab data from each "field" in your text file, and perform a lookup to an existing document by using any text key you desire. If you want more info on writing the import Script or configuration documents to process the incoming data and position data into the proper fields, write back. I'll help as much as I can.
Cheers,
Simon
- Thread starter
- #3
There is an existing export agent running which converts to a csv file. I didn't write this code and there is no documentation, so I am having trouble finding the sort key.
A few questions
since there are 30 or so people putting this file on their local drive. How do I identify the name of the file? Even when I have it on my local I can't open the file in my agent.
When I "grab" from field would I use FirstName:WkscolG type of format or change it doc.form = "importForm1"
doc.Firstname = .cell(col, 7).Value
Thanks
A few questions
since there are 30 or so people putting this file on their local drive. How do I identify the name of the file? Even when I have it on my local I can't open the file in my agent.
When I "grab" from field would I use FirstName:WkscolG type of format or change it doc.form = "importForm1"
doc.Firstname = .cell(col, 7).Value
Thanks
corporatepunkrocker
Programmer
Moonie,
I understand that there is an existing export agent which creates a csv file. My point was that this file would be more easily imported if it were first converted to a text file. Microsoft Excel will enable you to open csv files and save them as straight text. However, if you need to import csv files, make sure that you are referencing the correct source for the file and that you have set the appropriate Notes view to update documents based on the fields/columns in that csv file.
You should place the file in a specific directory which your script will reference. If you were to place the new text file in the c:\test directory, the script to find it would look like this.
sub Initialize
Dim importfilepathname As String
Dim importview as NotesView
Dim doc as NotesDocument
importfilepathname = "C:\test\*.csv" ' this will grab all csv files in the test directory
basefilepath$ = "C:\test\"
N$ = Dir$(importfilepathname)
Print n$
While Not n$ = ""
set importview=db.getview("Importview"
Set doc=importview.getfirstdocument
' Map fields ... update values... goto next document in view
Wend
End Sub
This is just a sample script but will help you verify that you've got the right path for importing data.
After you;ve gotten through this step, the code format you provided in this structure is recommended for pulling data out of csv files.
doc.Firstname = .cell(col, 7).Value
Hope that little bit helps.
Good luck.
Cheers,
Simon
I understand that there is an existing export agent which creates a csv file. My point was that this file would be more easily imported if it were first converted to a text file. Microsoft Excel will enable you to open csv files and save them as straight text. However, if you need to import csv files, make sure that you are referencing the correct source for the file and that you have set the appropriate Notes view to update documents based on the fields/columns in that csv file.
You should place the file in a specific directory which your script will reference. If you were to place the new text file in the c:\test directory, the script to find it would look like this.
sub Initialize
Dim importfilepathname As String
Dim importview as NotesView
Dim doc as NotesDocument
importfilepathname = "C:\test\*.csv" ' this will grab all csv files in the test directory
basefilepath$ = "C:\test\"
N$ = Dir$(importfilepathname)
Print n$
While Not n$ = ""
set importview=db.getview("Importview"
Set doc=importview.getfirstdocument
' Map fields ... update values... goto next document in view
Wend
End Sub
This is just a sample script but will help you verify that you've got the right path for importing data.
After you;ve gotten through this step, the code format you provided in this structure is recommended for pulling data out of csv files.
doc.Firstname = .cell(col, 7).Value
Hope that little bit helps.
Good luck.
Cheers,
Simon
- Thread starter
- #5
Simon,
If I save in straight .txt would I still use the doc.Firstname = .cell(col 7).Value format. I thought it would be easier as csv files because each field would be delimted and easier to match with my database. Could you tell me a little more about the text method, I haven't seen any samples going that route.
Sorry I need so much hand-holding.
If I save in straight .txt would I still use the doc.Firstname = .cell(col 7).Value format. I thought it would be easier as csv files because each field would be delimted and easier to match with my database. Could you tell me a little more about the text method, I haven't seen any samples going that route.
Sorry I need so much hand-holding.
corporatepunkrocker
Programmer
Moonie,
Never apologize for asking questions. It's always better to have an open mind to different points of view and to feel free to ask questions in order to learn from other people's experiences. That in my mind, is the true purpose of this forum. That having been said...
It might be easier to import data from a csv file, but I'm not expert in that process. I am very familar with the import of straight text files. However, there are some limitations to this approach. You must establish a fixed field length for each field and always make sure that it is at least as large as the lenghtiest value which might exist within it. The process I normally use for importing data involves a separate import database in which views are built to display information converted from text format into Notes document format before data is actually imported into a live application. This approach has several advantages, the biggest being that I can see what my data looks like ( and catch errors) before modifying any application data records. This involves two processes - one to import data into the import database, and two to selectively export data from this storage facility into your application database.
Step 1:
1. Create a new Notes database to house your imported data.
2. Create a configuration record form.
3. Create a document from this form which will house the field names and fixed lengths corresponding to all of the fields in your external data source.
4. Write the agent to pull data from the exernal source data into your import dabatase.
Configuration record form basics:
You will need to set up a virtual table consisting of four fields.
a. Multi value editable text field (new line as separator) in which you will be able to type the names of the fields corresponding to the incoming data records.
b. Multi value editable text field (new line as separator) in which you will place each field's starting position.
c.Multi value editable text field (new line as separator) in which you will place each field's ending position.
A sample table might look like this:
Field Name Start End
Company Name 1 10
Street Address 11 27
City Name 28 40
State 41 42
The script to import data using this config record (and massively oversimplified) would look like this:
dim db as NotesDatabase
Dim view as NotesView
Set view=db.getview("Importdata" ‘ view storing config doc
Dim importdoc as NotesDocument
Set importdoc=importdata.getfirstdocument
Dim doc As New notesdocument(thisdb)
Dim n as String
filenum% = Freefile
Open n For Input Access Read Shared As #filenum%
counter = 0
Do While Not Eof(filenum%)
Line Input #filenum%, linestring$
If Not linestring$ = "" Then
' call a routine to parse the data line out into fields
' Use the fields on the config document to set the values on the IncomingRecord document. Create an Incoming Record document for each row/line being imported.
doc.Form="IncomingRecord"
Step 2:
Once you've gotten all of your data to be imported into the application in record "form", you will be able to use whatever key you wish, a unique customer, contact, or company id is usually recommended) to easily match up data in your application with data in your import database.
I realize this is limited info to go on and a roundabout process if you are comfortable working with csv files and have that data format on hand. I wish I could be of more help, but the process is quite difficult (and unfortunately quite time-consuming) to explain via a bulletin board posting forum.
Good luck,
Simon
Never apologize for asking questions. It's always better to have an open mind to different points of view and to feel free to ask questions in order to learn from other people's experiences. That in my mind, is the true purpose of this forum. That having been said...
It might be easier to import data from a csv file, but I'm not expert in that process. I am very familar with the import of straight text files. However, there are some limitations to this approach. You must establish a fixed field length for each field and always make sure that it is at least as large as the lenghtiest value which might exist within it. The process I normally use for importing data involves a separate import database in which views are built to display information converted from text format into Notes document format before data is actually imported into a live application. This approach has several advantages, the biggest being that I can see what my data looks like ( and catch errors) before modifying any application data records. This involves two processes - one to import data into the import database, and two to selectively export data from this storage facility into your application database.
Step 1:
1. Create a new Notes database to house your imported data.
2. Create a configuration record form.
3. Create a document from this form which will house the field names and fixed lengths corresponding to all of the fields in your external data source.
4. Write the agent to pull data from the exernal source data into your import dabatase.
Configuration record form basics:
You will need to set up a virtual table consisting of four fields.
a. Multi value editable text field (new line as separator) in which you will be able to type the names of the fields corresponding to the incoming data records.
b. Multi value editable text field (new line as separator) in which you will place each field's starting position.
c.Multi value editable text field (new line as separator) in which you will place each field's ending position.
A sample table might look like this:
Field Name Start End
Company Name 1 10
Street Address 11 27
City Name 28 40
State 41 42
The script to import data using this config record (and massively oversimplified) would look like this:
dim db as NotesDatabase
Dim view as NotesView
Set view=db.getview("Importdata"
‘ view storing config docDim importdoc as NotesDocument
Set importdoc=importdata.getfirstdocument
Dim doc As New notesdocument(thisdb)
Dim n as String
filenum% = Freefile
Open n For Input Access Read Shared As #filenum%
counter = 0
Do While Not Eof(filenum%)
Line Input #filenum%, linestring$
If Not linestring$ = "" Then
' call a routine to parse the data line out into fields
' Use the fields on the config document to set the values on the IncomingRecord document. Create an Incoming Record document for each row/line being imported.
doc.Form="IncomingRecord"
Step 2:
Once you've gotten all of your data to be imported into the application in record "form", you will be able to use whatever key you wish, a unique customer, contact, or company id is usually recommended) to easily match up data in your application with data in your import database.
I realize this is limited info to go on and a roundabout process if you are comfortable working with csv files and have that data format on hand. I wish I could be of more help, but the process is quite difficult (and unfortunately quite time-consuming) to explain via a bulletin board posting forum.
Good luck,
Simon
- Thread starter
- #7
Simon,
I'll definately try the text method next time, but it seems a little over my head at the moment. I do have another question about the csv method, I ran the code you sent to check for the paths and everything is opening fine now, but now I am having trouble with the .col method, nothing is updating.
Thanks for all your time
K
I'll definately try the text method next time, but it seems a little over my head at the moment. I do have another question about the csv method, I ran the code you sent to check for the paths and everything is opening fine now, but now I am having trouble with the .col method, nothing is updating.
Thanks for all your time
K
corporatepunkrocker
Programmer
Moonie,
Unfortunately, the .col method for importing data is outside of my experience. My recommendation is that you define the delimiter most likely ";" or "," if the file is a comma separated values file. Then you will need to use instring and mid commands in order to define portions of each CSV "row" to update individual fields in Notes. Other than those general recommendations, you would be well served to do a search in the Notes Gold Forum on Notes.Net by ".col". Below is one of the more useful examples I found on the site.
Good luck,
Simon
Unfortunately, the .col method for importing data is outside of my experience. My recommendation is that you define the delimiter most likely ";" or "," if the file is a comma separated values file. Then you will need to use instring and mid commands in order to define portions of each CSV "row" to update individual fields in Notes. Other than those general recommendations, you would be well served to do a search in the Notes Gold Forum on Notes.Net by ".col". Below is one of the more useful examples I found on the site.
Good luck,
Simon
- Thread starter
- #9
- Status
Similar threads
- Locked
- Question