Batch loading from WP Table

[Return to Macros Page]

//---------------------------------------------------------
// Sample macro to demonstrate batch loading an ODBC
// data table from a WordPerfect table
// The macro loads the contents of the first table in
// the current document to the ODBC table.
// © Geoff Lane 1997
//---------------------------------------------------------
PROCEDURE PROTOTYPE LoadStrings(&FirstName; &LastName; 
	&Street; &Address1; &Address2; &City; &County;
	&PostCode; &Country; &BusTel; &HomeTel; &Fax; &email)
PROCEDURE PROTOTYPE FormatStrings(&FirstName; &LastName; 
	&Street; &Address1; &Address2; &City; &County;
	&PostCode; &Country; &BusTel; &HomeTel; &Fax; &email)
PROCEDURE PROTOTYPE InsertOneRecord(FirstName; LastName; 
	Street; Address1; Address2; City; County;
	PostCode; Country; BusTel; HomeTel; Fax; email)

  // initialize variables
	FirstName := ""
	LastName := ""
	Street := ""
	Address1 := ""
	Address2 := ""
	City := ""
	County := ""
	PostCode := ""
	Country := ""
	BusTel := ""
	HomeTel := ""
	Fax := ""
	email := ""

  // Display is turned on so you can see the effect
  // of the macro, turn it off for increased performance
Display(On!)
  //-----------------------------------------------

  // Save the window number of this document and create
  // a scrap document from which to execute the SQL.
  // This avoids WP corrupting the source data table.
SourceDoc := ?DocNumber
FileNew ()
ScrapDoc := ?DocNumber

  // Position the insertion point in the last cell of the 
  // first row of the first table in the current document
SwitchDoc(SourceDoc)
PosDocVeryTop()
TablePosNextTable ()
PosTableRowEnd()

  // Send the data, one row at a time, to ODBC.
  // Stopping condition is ?TableLastCell
Finished := ?TableLastCell
WHILE (Finished = FALSE)
  LoadStrings(&FirstName; &LastName; 
	&Street; &Address1; &Address2; &City; &County;
	&PostCode; &Country; &BusTel; &HomeTel; &Fax; &email)
  FormatStrings(&FirstName; &LastName; 
	&Street; &Address1; &Address2; &City; &County;
	&PostCode; &Country; &BusTel; &HomeTel; &Fax; &email)
    // Position insertion point to the scrap document
    // this stops WP from corrupting the source table
  SwitchDoc(ScrapDoc)
  InsertOneRecord(FirstName; LastName; 
	Street; Address1; Address2; City; County;
	PostCode; Country; BusTel; HomeTel; Fax; email)
  SwitchDoc(SourceDoc)
  Finished := ?TableLastCell
ENDWHILE

 // Close the scrap doc without saving
SwitchDoc(ScrapDoc)
Close(Save: No!)
SwitchDoc(SourceDoc)

QUIT
//End of Macro Processing
//-----------------------------//--------------------------
//-----------------------------//--------------------------


 //--------------------------------------------
 // NAME:    LoadStrings
 // PURPOSE: Load the contents of the current
 //          row in the document table into
 //          the string variables, ready for
 //          formatting
 //--------------------------------------------
PROCEDURE LoadStrings(&FirstName; &LastName; 
	&Street; &Address1; &Address2; &City; &County;
	&PostCode; &Country; &BusTel; &HomeTel; &Fax; &email)
   PosTableRowBegin()
   PosTableCellDown()
   SelectLineEnd()
  FirstName := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  LastName := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  Street := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  Address1 := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  Address2 := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  City := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  County := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  PostCode := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  Country := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  BusTel := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  HomeTel := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  Fax := ?SelectedText
   SelectOff()
   PosTableCellNext()
   SelectLineEnd()
  email := ?SelectedText
   SelectOff()
EndProc
//--------[ EndProc LoadStrings ]--------------


 //--------------------------------------------
 // NAME:    FormatStrings
 // PURPOSE: Add single quotes to each side of
 //          each string, or replace the string
 //          with 'NULL' if empty
 //--------------------------------------------
PROCEDURE FormatStrings(&FirstName; &LastName; 
	&Street; &Address1; &Address2; &City; &County;
	&PostCode; &Country; &BusTel; &HomeTel; &Fax; &email)
  IF (StrLen(FirstName) = 0) 
	FirstName := "NULL" 
  ELSE 
	FirstName := "'" + FirstName + "'"
  ENDIF
  LastName := "'" + LastName + "'"
  IF (StrLen(Street) = 0)
	Street := "NULL"
  ELSE
	Street := "'" + Street + "'"
  ENDIF
  IF (StrLen(Address1) = 0)
	Address1 := "NULL"
  ELSE
	Address1 := "'" + Address1 + "'"
  ENDIF
  IF (StrLen(Address2) = 0)
	Address2 := "NULL"
  ELSE
	Address2 := "'" + Address2 + "'"
  ENDIF
  IF (StrLen(City) = 0)
	City := "NULL"
  ELSE
	City := "'" + City + "'"
  ENDIF
  IF (StrLen(County) = 0)
	County := "NULL"
  ELSE
	County := "'" + County + "'"
  ENDIF
  IF (StrLen(PostCode) = 0)
	PostCode := "NULL"
  ELSE
	PostCode := "'" + PostCode + "'"
  ENDIF
  IF (StrLen(Country) = 0)
	Country := "NULL"
  ELSE
	Country := "'" + Country + "'"
  ENDIF
  IF (StrLen(BusTel) = 0)
	BusTel := "NULL"
  ELSE
	BusTel := "'" + BusTel + "'"
  ENDIF
  IF (StrLen(HomeTel) = 0)
	HomeTel := "NULL"
  ELSE
	HomeTel := "'" + HomeTel + "'"
  ENDIF
  IF (StrLen(Fax) = 0)
	Fax := "NULL"
  ELSE
	Fax := "'" + Fax + "'"
  ENDIF
  IF (StrLen(Email) = 0)
	Email := "NULL"
  ELSE
	Email := "'" + Email + "'"
  ENDIF
EndProc
//---------[ EndProc FormatStrings ]-----------

//---------------------------------------------
// NAME:    InsertOneRecord
// PURPOSE: To insert a single record into the
//          ODBC data table
//---------------------------------------------
PROCEDURE InsertOneRecord(FirstName; LastName; 
	Street; Address1; Address2; City; County;
	PostCode; Country; BusTel; HomeTel; 
	Fax; email)
  MyQuery := "INSERT INTO Contact (" +
	"FirstName, LastName, Street, Address1, Address2, " +
	"City, County, PostCode, Country, BusinessTelNo, " +
	"HomeTelNo, FaxNo, email) VALUES ("
  MyQuery := MyQuery +  FirstName + ", " +
	LastName + ", " + Street + ", " +
	Address1 + ", " + Address2 + ", " +
	City + ", " + County + ", " +
	PostCode + ", " + Country + ", " +
	BusTel + ", " + HomeTel + ", " +
	Fax + ", " + email + ")"

    //Now process as in the basic macro
  ImportSetSource (SourceType: ODBC!)
  ImportSetDestination (DestinationType: WPTable!)
  ImportSetIncludeNames (State: On!)
  ImportSetDataSource (Source: "MyData")
  ImportSetSQLQuery (SQLQuery: MyQuery)
  ImportDoImport()
EndProc
//-------[ EndProc InsertOneRecord ]------------