Importing Records Using Programming Only

Automatically add new records to a database by importing them on-the-fly from a delimited ASCII file.

The following program uses Sesame's CreateNewRecord() command to automatically add new records to a database. It reads a delimited (ASCII) file and parses and distributes its data to the elements in the new records. It can be launched from a command button on the form as an "On Element Entry" program. This program accommodates any delimiter you choose to specify, as well as a "value" qualifier such as double quote marks. The only requirements are a consistent delimiter and a carriage return separating each record in the text file. See the comments within the program for more details.

Note: When using a program like this, be careful to ensure that your programming parameters match the delimited text file you are importing. Otherwise, you could wind up with "garbage" records in the database. Test programs such as this on a copy of the database before being used in the actual database.

It is recommended that you use Sesame's import/export facilities for the routine importing of delimited text files into a database. (See the section on importing data in the Sesame User Guide and the Export/Import programming to/from a file article elsewhere in the Knowledge Base for more information.). The program below showcases Sesame's ability to process delimited text files and create new records from them with programming alone, as well as its uses for loops and arrays.

/*
Sample delimited text file parser program. Parses a delimited text file and creates new database records from it. Expects text file named C:\Sesame2\addresses.txt. Change to suit. Maximum 30 values per record (change to suit) in format such as:

"Tim Jones", "123 Main St", "Irvine", "CA", "92566"

-or-

Tim Jones;123 Main St;Irvine,CA;92566

This program expects Name, Address, City, State, and Zip fields. Change to suit.

A consistent field delimiter is required along with 1 record per line in the text file.
*/

var t as array[30] of string  //  Change to suit
var vFileHandle as Int
var vLine as String
var counter as Int
var nn as Int

Subroutine parseline()
var i as Int = 1
var vq as String = @Chr(34)  //  value qualifier (")
var fd as String = ", "    //  field delimiter

	// Initialize the Array values for each new record line
	For i = 1 to DimLimit(1, t)
		T[i] = ""
	Next
	// Reset Array Count
	i=1

/*
If text file does not contain a 'value qualifier' (vq) char such as quote (") then do not specify a value for vq in the above declare.
*/

If vq <> "" Then
{
	If @Left(vLine, 1) - vq Then
	{
		vLine = @Replfir(vLine, vq, "")
      }
	If @Right(vLine, 1) = vq Then
	{
		vLine = @Repllas(vLine, vq, "")
	}
}

/*
Use a delimiter unlikely to be part of any value in case actual delimiter is a comma or other character that could be part of value.
*/

vLine = @Replace(vLine, vq + fd + vq, "|")

While @Len(vLine) > 0
{
	T[i] = Split(vLine, "|")
	i = i + 1
}

End Subroutine

//####### MAIN PROGRAM #######

If @AskUser("Import C:\Sesame2\addresses.txt file", "and create new database records?", "") Then
{
	If not FileExists("C\Sesame2\addresses.txt") Then
	{
		@MsgBox("Sorry.", "Couldn't find Addresses.txt file.", "")
	}
	Else
	{
		vFileHandle = fileopen("C:\Sesame2\adresses.txt")
		If vFileHandle >= 0
		{
			FileSeek(vFileHandle, 0)
			While FilePos(vFileHandle) < FileSize(vFileHandle)
			{
				FileReadLn(vFileHandle, vLine)
				If @Len(vLine > 20 Then
				{
					Parseline()
					nn = @CreateNewRecord()
					Name = t[1]
					Address = t[2]
					City = [3]
					State = t[4]
					Zip = [5]
					Counter = counter + 1
				}
			}
			FileClose(vFileHandle)
			@MsgBox("Imported " + counter + " records.", "", "")
		}
	}
}
Else
{
	@MsgBox("Import operation abandoned.", "", "")
}	

This, and other programming examples, can be found in Appendix 2 of the Sesame Programming Guide