(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Importing data in a particular manner
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: [1] 2 3  Send Topic Send Topic Print Print
Very Hot Topic (More than 25 Replies) Importing data in a particular manner (Read 4428 times)
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Importing data in a particular manner
Feb 13th, 2008 at 8:27pm
Print Post Print Post  
I have an app with 3500 records. I have an ascii file of 1000 records that I'd like to import. Both the app and the ascii file share a unique LE (policy number). I'd like to import the 1000 records and have them "go" where they should based on the shared policy number. Looking for a way to automate this...otherwise...yours truly will have the task of manually parsing through this!!
Any help appreciated...
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #1 - Feb 13th, 2008 at 8:29pm
Print Post Print Post  
Do you want to add subrecords to existing parents, or do you want to update existing records with info from the text file?
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Importing data in a particular manner
Reply #2 - Feb 13th, 2008 at 8:30pm
Print Post Print Post  
I would like to update exisitng records with the text file but only for those records that share the same policy number...?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #3 - Feb 13th, 2008 at 8:35pm
Print Post Print Post  
Cliff wrote on Feb 13th, 2008 at 8:30pm:
I would like to update exisitng records with the text file but only for those records that share the same policy number...?


You'll need to use File I/O to read in each line, parse it, find the matching record and update the fields you want. Are you updating every field, or just a few?
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Importing data in a particular manner
Reply #4 - Feb 13th, 2008 at 8:43pm
Print Post Print Post  
The app has about 700 LE's. 100 of these have just been added to the form to accomodate the data in the ascii file... The way it appears to me is that the data from the ascii file would be appended to the end of the existing form ...?
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #5 - Feb 13th, 2008 at 8:52pm
Print Post Print Post  
How is the ASCII file delimited?
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Importing data in a particular manner
Reply #6 - Feb 13th, 2008 at 9:41pm
Print Post Print Post  
It is comma delimited...tx...
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #7 - Feb 13th, 2008 at 9:49pm
Print Post Print Post  
Cliff wrote on Feb 13th, 2008 at 9:41pm:
It is comma delimited...tx...

Are there quotes around the data? Can I see a couple lines of the file?
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Importing data in a particular manner
Reply #8 - Feb 13th, 2008 at 10:57pm
Print Post Print Post  
Here are a few lines from the import (ascii) file...(it ain't pretty!!)
"50C0003335",01,"06/04/2004","06/04/2005","MICHAEL BAY MARINE",98," "," ","CORPORATION","NON-APPLICABLE","8,9",1000000,175," ", ," ", ," "," ", ," ", , , ," ", , ," ", ," ", ," ", , ," ", , ," ", , ," ", , ," ", , ," ", , ," ", , ," ", ," ",175,"MD","IF ANY","1.0","1.0",74," "," "," "," ", ," "," "," "," ", ,74, ," "," ", , ," "," ", , ," "," ", ,0,10,"101", ," ", ," ", ," ","101","CA0001 03 06","CA9903 03 06","CA2117 12 93","CA2171 01 88","CA0170 10 01","CA0215 11 00","CA2113 04 01","CA2219 01 02","PA0216 11 00"," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ", ," "," "
"50C0002167",04,"04/12/2006","04/12/2007","SAILCRAFT",20," "," ","CORPORATION","NAS-02167","1",1000000,1895," ", ," ", ," "," ", ," ", , , ," ", , ," ", ," ", ,"5",1000000,252," ", , ," ", , ,"7",1000,228," ", , ,"7",1000,818," ", , ," ", ," ",3193,"NC","IF ANY","1.0","1.0",74," "," "," "," ", ," "," "," "," ", ,74, ," "," ", , ," "," ", , ," "," ", ,0,10,"101", ," ", ," ", ," ","101","CA0001 03 06","CA9903 03 06","CA2117 12 93","CA2171 01 88","CA0126 03 01","CA2116 07 04"," "," "," "," "," "," "," "," "," "," "," "," ","2000 CHEVROLET","2003 CHEVROLET","2006 CHEVROLET SILVERADO"," "," "," "," "," "," "," "," "," "," ", ," "," "
"50C0004324",00,"05/30/2006","05/30/2007","BROAD CREEK MARINA LLC",10,"708 HARBOR ROAD","WANCHESE,NC 27981","CORPORATION","NAS-04324","8,9",1000000,175," ", ," ", ," "," ", ," ", , , ," ", , ," ", ," ", ," ", , ," ", , ," ", , ," ", , ," ", , ," ", , ," ", , ," ", ," ",175,"NORTH CAROLINA","IF ANY","1.0","1.0",74," "," "," "," ", ," "," "," "," ", ,74, ," "," ", , ," "," ", , ," "," ", , ,10,"101", ," ", ," ", ," ","101","CA0001 03 06","CA9903 07 97","CA2117 12 93","CA2171 01 88","CA9916 12 93","CA0126 03 01","CA2116 07 04"," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ", ," "," "
"50C000737",10,"06/17/2006","06/17/2007","DAVID J WOOD",20," "," "," ","NAS-00737","1",1000000,1507,"2", ,"PEDESTRIAN", ," "," ", ," ", , , ," ", , ," ", ," ", ,"5",1000000,118," ", , ," ", , ,"7",250,111," ", , ,"7",500,422," ", , ," ", ," ",2158,"NJ","IF ANY","1.0","1.0",80," "," "," "," ", ," "," "," "," ", ,80, ," "," ", , ," "," ", , ," "," ", ,0,10,"111", ," ", ," ", ," ","111","CA0001 03 06","CA9903 03 06","CA2117 12 93","CA2171 01 88","CA0184 09 95","CA0188 03 99","CA2114 01 04","CA2230 11 04","CA2231 02 97"," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ", ," "," "
"50C0002518",04,"12/10/2005","12/10/2006","GLASS MASTERS  BOATWORKS",20," "," ","CORPORATION","NAS-02518","8,9",300000,145," ", ," ", ," "," ", ," ", , , ," ", , ," ", ," ", ," ", , ," ", , ," ", , ," ", , ," ", , ," ", , ," ", , ," ", ," ",145,"MD","IF ANY","1.0","1.0",61," "," "," "," ", ," "," "," "," ", ,61, ," "," ", , ," "," ", , ," "," ", ,0,10,"84", ," ", ," ", ," ","84","CA0001 03 06","CA9903 03 06","CA2117 12 93","CA2171 01 88","CA0170 10 01","CA0215 11 00","CA2113 04 01","CA2219 01 02","PA0216 11 00"," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," "," ", ," "," "
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #9 - Feb 13th, 2008 at 11:05pm
Print Post Print Post  
What you want to do is use ASCII Database (on the File menu) to make a database out of this file. Then you can run a mass update on it that uses XResultSet to find the matching record in the target database and update it. Easy as pie!

I don't have time to write the code right now, but if nobody gets to it before I do, I'll do it for you tomorrow.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Importing data in a particular manner
Reply #10 - Feb 14th, 2008 at 12:09pm
Print Post Print Post  
Thanks Hammer! I get a kick out of your description "Easy as pie!" you are talking to someone that burns toast...in a figurative manner of course...  Wink
Again...many thanks...
Cliff
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #11 - Feb 14th, 2008 at 3:13pm
Print Post Print Post  
OK Cliff, here's what you do.

Back up your database.

Make a database out of your text file
Using ASCII database lets us deal with all those quotes and commas for you.

1. Add the following line to the top of your text file. This will make sure you don't lose the first record.
Code
Select All
#"ASCII0000","ASCII0001","ASCII0002","ASCII0003","ASCII0004","ASCII0005","ASCII0006","ASCII0007","ASCII0008","ASCII0009","ASCII0010","ASCII0011","ASCII0012","ASCII0013","ASCII0014","ASCII0015","ASCII0016","ASCII0017","ASCII0018","ASCII0019","ASCII0020","ASCII0021","ASCII0022","ASCII0023","ASCII0024","ASCII0025","ASCII0026","ASCII0027","ASCII0028","ASCII0029","ASCII0030","ASCII0031","ASCII0032","ASCII0033","ASCII0034","ASCII0035","ASCII0036","ASCII0037","ASCII0038","ASCII0039","ASCII0040","ASCII0041","ASCII0042","ASCII0043","ASCII0044","ASCII0045","ASCII0046","ASCII0047","ASCII0048","ASCII0049","ASCII0050","ASCII0051","ASCII0052","ASCII0053","ASCII0054","ASCII0055","ASCII0056","ASCII0057","ASCII0058","ASCII0059","ASCII0060","ASCII0061","ASCII0062","ASCII0063","ASCII0064","ASCII0065","ASCII0066","ASCII0067","ASCII0068","ASCII0069","ASCII0070","ASCII0071","ASCII0072","ASCII0073","ASCII0074","ASCII0075","ASCII0076","ASCII0077","ASCII0078","ASCII0079","ASCII0080","ASCII0081","ASCII0082","ASCII0083","ASCII0084","ASCII0085","ASCII0086","ASCII0087","ASCII0088","ASCII0089","ASCII0090","ASCII0091","ASCII0092","ASCII0093","ASCII0094","ASCII0095","ASCII0096","ASCII0097","ASCII0098","ASCII0099","ASCII0100","ASCII0101","ASCII0102","ASCII0103","ASCII0104","ASCII0105","ASCII0106","ASCII0107","ASCII0108","ASCII0109","ASCII0110","ASCII0111","ASCII0112","ASCII0113","ASCII0114","ASCII0115","ASCII0116","ASCII0117","ASCII0118","ASCII0119","ASCII0120","ASCII0121","ASCII0122","ASCII0123","ASCII0124","ASCII0125","ASCII0126","ASCII0127"


2. From the Sesame File menu, choose ASCII Database. Select your text file. Give the new database a name.

Sesame will create a database out of your text file. There may be an extra blank record at the end. If so, just delete it.

Use Mass Update to update your real database
You can now run a Mass Update on the records in the temporary database to update the real database. Like so:

Code
Select All
#include "..\Sesame2\sbasic_include.sbas"

var vRS as Int

	// Open a result set with the matching record in the target database
	vRS = @XResultSetSearch("Target.db", "Target", SEARCH_MODE_AND, SEARCH_SYNTAX_QA, "!Key=" + ASCII0000)
	If vRS > -1
	{
		// Make sure there is exactly one matching record
		If @XResultSetTotal(vRS) = 1
		{
			// Update the values
			XResultSetValue(vRS, "Info1", ASCII0001)
			XResultSetValue(vRS, "Info2", ASCII0002)
			XResultSetValue(vRS, "Info3", ASCII0003)
			XResultSetValue(vRS, "Info4", ASCII0004)
			XResultSetValue(vRS, "Info5", ASCII0005)
		}
		Else
		{
			WriteLn("Error: There is not exactly one matching record for " + ASCII0000)
		}

		// Close the result set
		XResultSetClose(vRS)
	}
	Else
	{
		WriteLn("Error: Unable to open result set for " + ASCII0000)
	}
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Importing data in a particular manner
Reply #12 - Feb 14th, 2008 at 5:55pm
Print Post Print Post  
I really do thank you very much...this is such a help to me as I was facing the "opportunity" to manually parse this data twice...once for testing and then again for production...

Again...thank you!

Cliff
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing data in a particular manner
Reply #13 - Feb 14th, 2008 at 6:54pm
Print Post Print Post  
Cliff wrote on Feb 14th, 2008 at 5:55pm:
I really do thank you very much...this is such a help to me as I was facing the "opportunity" to manually parse this data twice...once for testing and then again for production...


Manually parse? Perish the thought!  Smiley
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Amor
Full Member
Members
***
Offline


No personal text

Posts: 366
Location: Germany
Joined: Feb 7th, 2004
Re: Importing data in a particular manner
Reply #14 - Feb 15th, 2008 at 8:37am
Print Post Print Post  
Hello Erika,

that is fantastically and brilliantly at the same time.
Thanks for these contribution.

Dr.. Belhareth
  
Dr. med. Amor Belhareth&&Medizin Labor &&Germany
Back to top
 
IP Logged
 
Page Index Toggle Pages: [1] 2 3 
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge