Since I just got a hold of a Brother QL-700 label printer (My Dymo labeller died), I had to write a small program to convert my address book to a CSV file that the Brother P-Touch Address book could import, so here it is.
I already provided very basic label support for the Dymo in this program (no size detection, just a straight print with template per user with whatever label size that is in the thing at the time), I am looking to add the Brother labeller support at some stage.
Code: Select all
;==============================================================================================================================================================================
; Use the SQLite database backend
;==============================================================================================================================================================================
UseSQLiteDatabase() ;
;==============================================================================================================================================================================
; My personal constants
;==============================================================================================================================================================================
#Basename = "Address Book " ; Base name for ini and dataabse
#Version = "v0.00 beta" ; Program version
#Program = #Basename + #Version ; Copyright string
;==============================================================================================================================================================================
; Program data structure for where contents change
;==============================================================================================================================================================================
Structure ProgramData
CurrentRecord.s ; Currently selected record
; Database details
DatabaseName.s ; Local database name
DatabaseDirectory.s ; Local database directory
DatabaseHandle.i ; Opened database handle
; Directories
CurrentDirectory.s ; Current working directory
; File stuff
CSVOutputFile.s ; CSV data output file handle
EndStructure ;
;==============================================================================================================================================================================
; Address database fields (Might change)
;==============================================================================================================================================================================
Structure AddressData ;
Title.s ; Titlename 0 ; Addresses
FirstName.s ; Firstname 1 ; Addresses
MiddleName.s ; Middlename 2 ; Addresses
LastName.s ; Lastname 3 ; Addresses
Suffix.s ; 4 ; Addresses
Company.s ; Business (Or Medical) 5 ; Addresses
Department.s ; 6 ; Addresses
Office.s ; 7 ; Addresses
JobTitle.s ; 8 ; Addresses
Street.s ; Pobox + Street + Suburb 9 ; Addresses
City.s ; City 10 ; Addresses
County.s ; State 11 ; Addresses
PostCode.s ; Postcode 12 ; Addresses
Country.s ; Country 13 ; Addresses
Phone.s ; Work phone Or Home phone) 14 ; Contacts
Mobile.s ; Work mobile Or Home mobile) 15 ; Contacts
Fax.s ; Work fax Or Home fax) 16 ; Contacts
Email.s ; Work email Or Home email) 17 ; Contacts
URL.s ; Work web page Or Home web page) 18 ; Contacts
PrintCount.s ; 0 19 ; Other
LastDate.s ; 10/06/2016 10:20:37 PM 20 ; Other
Custom1.s ; 21 ; Other
Custom2.s ; 22 ; Other
Custom3.s ; 23 ; Other
Custom4.s ; 24 ; Other
Notes.s ; Comment 25 ; Addresses
EndStructure
;==============================================================================================================================================================================
; Contact data fields
;==============================================================================================================================================================================
Structure ContactData ;
contactid.s ;
contacttype.s ;
contactdetails.s ;
recordid.s ;
EndStructure
;==============================================================================================================================================================================
; Global definitions
;==============================================================================================================================================================================
Global Program.ProgramData ; All my program variables
Global Address.AddressData ; Basic address data
Global Contact.ContactData ; Numbers associated with an address
;==============================================================================================================================================================================
; Get working directory variables set up
;==============================================================================================================================================================================
Program\CurrentDirectory = GetCurrentDirectory() ;
Program\DatabaseDirectory = Program\CurrentDirectory + "Database\" ;
;==============================================================================================================================================================================
; Create your working file names
;==============================================================================================================================================================================
Program\DatabaseName = Program\DatabaseDirectory + ReplaceString(#Basename, " ", "_") + #Version + ".sqlite"
Program\CSVOutputFile = Program\CurrentDirectory + ReplaceString(#Basename, " ", "_") + #Version + ".csv"
;==============================================================================================================================================================================
; Kill double quotes in strings for display purposes
;==============================================================================================================================================================================
Procedure.s KillQuote(Instring.s)
ProcedureReturn ReplaceString(Instring.s, "''", "'", 1, 1)
EndProcedure
;==============================================================================================================================================================================
; Open or create the stystem database and create any needed tables
;==============================================================================================================================================================================
Procedure OpenSystemDatabase()
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
Protected.s DatabaseUpdate.s
Protected.i FileHandle.i
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
FileHandle.i = OpenFile(#PB_Any, Program\DatabaseName)
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
If FileHandle.i <> 0
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
CloseFile(FileHandle.i)
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, "", "", #PB_Database_SQLite)
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
If Program\DatabaseHandle
;--------------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------------
Else
Debug "There was a serious problem attempting to connect to " + #BaseName + #Version + " system database. Could be corrupt or open by some other process."
EndIf
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
Else
Debug "Could not open or create raw database file."
EndIf
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
EndProcedure
;==============================================================================================================================================================================
;
;==============================================================================================================================================================================
Procedure ExportToBrotherLabeller()
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
NewList AddresRecordId.s()
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
DatabaseQuery.s = "SELECT Recordid FROM Addresses"
If DatabaseQuery(Program\DatabaseHandle, DatabaseQuery.s)
While NextDatabaseRow(Program\DatabaseHandle)
AddElement(AddresRecordId.s())
AddresRecordId.s() = GetDatabaseString(Program\DatabaseHandle, 0)
Wend
FinishDatabaseQuery(Program\DatabaseHandle)
EndIf
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
If ListSize(AddresRecordId.s())
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
CSVOutputFileId.i = CreateFile(#PB_Any, Program\CSVOutputFile)
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
If CSVOutputFileId.i <> 0
;--------------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------------
WriteString(CSVOutputFileId.i, "Title,First Name,Middle Name,Last Name,Suffix,Company,")
WriteString(CSVOutputFileId.i, "Department,Office,Job Title,Street,City,County,Post Code,")
WriteString(CSVOutputFileId.i, "Country,Phone,Mobile,Fax,E-mail,URL,Print Count,Last Date,")
WriteStringN(CSVOutputFileId.i, "Custom1,Custom2,Custom3,Custom4,Notes")
;--------------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------------
ForEach AddresRecordId.s()
;------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------
DatabaseQuery.s = "SELECT Titlename, Firstname, Middlename, Lastname, Business, "
DatabaseQuery.s + "Medical, Pobox, Street, Suburb, City, State, Postcode, Country, "
DatabaseQuery.s + "Comment "
DatabaseQuery.s + "FROM Addresses "
DatabaseQuery.s + "WHERE Recordid = '" + AddresRecordId.s() + "'"
;------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------
If DatabaseQuery(Program\DatabaseHandle, DatabaseQuery.s) <> 0
;----------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------
While NextDatabaseRow(Program\DatabaseHandle) <> 0
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
Title.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 0))
Firstname.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 1))
Middlename.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 2))
Lastname.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 3))
Business.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 4))
Medical.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 5))
Pobox.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 6))
Street.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 7))
Suburb.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 8))
City.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 9))
State.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 10))
Postcode.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 11))
Country.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 12))
Comment.s = KillQuote(GetDatabaseString(Program\DatabaseHandle, 13))
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
Address\Title.s = Title.s
Address\FirstName.s = Firstname.s
Address\MiddleName.s = Middlename.s
Address\LastName.s = Lastname.s
Address\Suffix.s = ""
Address\Company.s = Trim(Medical.s + " " + Business.s)
Address\Department.s = ""
Address\Office.s = ""
Address\JobTitle.s = ""
Address\Street.s = Pobox.s + " " + Street.s + " " + Suburb.s
Address\Street.s = Trim(Address\Street.s, ",")
Address\Street.s = Trim(Address\Street.s, " ")
Address\Street.s = ReplaceString(Address\Street.s, " ", " ")
Address\City.s = City.s
Address\County.s = State.s
Address\PostCode.s = Postcode.s
Address\Country.s = Country.s
Address\Notes.s = Comment.s
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
Wend
;----------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------
FinishDatabaseQuery(Program\DatabaseHandle)
;----------------------------------------------------------------------------------------
; Get the contact numbers for this record: #Gadget_Address_Numbers
;----------------------------------------------------------------------------------------
DatabaseQuery.s = "SELECT * FROM Contacts "
DatabaseQuery.s + "WHERE Recordid = '" + AddresRecordId.s() + "'"
;----------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------
If DatabaseQuery(Program\DatabaseHandle, DatabaseQuery.s) <> 0
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
While NextDatabaseRow(Program\DatabaseHandle) <> 0
;------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------
Contactid.s = GetDatabaseString(Program\DatabaseHandle, 0) ; AutoIncrement field
Contacttype.s = GetDatabaseString(Program\DatabaseHandle, 1) ; Heading for type of contact data
Contactdetails.s = GetDatabaseString(Program\DatabaseHandle, 2) ; Type of contact data
;------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------
Select ContactType.s
Case "Home phone" : HomePhone.s = Contactdetails.s
Case "Home mobile" : HomeMobile.s = Contactdetails.s
Case "Home fax" : HomeFax.s = Contactdetails.s
Case "Home email" : HomeEmail.s = Contactdetails.s
Case "Home web page" : HomeWeb.s = Contactdetails.s
Case "Work phone" : WorkPhone.s = Contactdetails.s
Case "Work fax" : WorkFax.s = Contactdetails.s
Case "Work mobile" : WorkMobile.s = Contactdetails.s
Case "Work email" : WorkEmail.s = Contactdetails.s
Case "Work web page" : WorkWeb.s = Contactdetails.s
EndSelect
;------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------
If HomePhone.s <> ""
Address\Phone = HomePhone.s
Else
Address\Phone = WorkPhone.s
EndIf
If HomeMobile.s <> ""
Address\Mobile = HomeMobile.s
Else
Address\Mobile = WorkMobile.s
EndIf
If HomeFax.s <> ""
Address\Fax = HomeFax.s
Else
Address\Fax = WorkFax.s
EndIf
If HomeEmail.s <> ""
Address\Email = HomeEmail.s
Else
Address\Email = WorkEmail.s
EndIf
If HomeWeb.s <> ""
Address\URL = HomeWeb.s
Else
Address\URL = WorkWeb.s
EndIf
;------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------
Wend
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
FinishDatabaseQuery(Program\DatabaseHandle)
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
Address\PrintCount.s = "0"
Address\LastDate.s = "10/06/2016 10:20:37 PM"
Address\Custom1.s = ""
Address\Custom2.s = ""
Address\Custom3.s = ""
Address\Custom4.s = ""
;--------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------
Else
; Debug "Could not get the contact details for the current address"
EndIf
;----------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------
Else
; Debug "Could not get the current address data"
EndIf
;------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------
WriteString(CSVOutputFileId.i, Address\Title.s + ",")
WriteString(CSVOutputFileId.i, Address\FirstName.s + ",")
WriteString(CSVOutputFileId.i, Address\MiddleName.s + ",")
WriteString(CSVOutputFileId.i, Address\LastName.s + ",")
WriteString(CSVOutputFileId.i, Address\Suffix.s + ",")
WriteString(CSVOutputFileId.i, Address\Company.s + ",") ; : Debug Address\Company
WriteString(CSVOutputFileId.i, Address\Department.s + ",")
WriteString(CSVOutputFileId.i, Address\Office.s + ",")
WriteString(CSVOutputFileId.i, Address\JobTitle.s + ",")
WriteString(CSVOutputFileId.i, Address\Street.s + ",")
WriteString(CSVOutputFileId.i, Address\City.s + ",")
WriteString(CSVOutputFileId.i, Address\County.s + ",")
WriteString(CSVOutputFileId.i, Address\PostCode.s + ",")
WriteString(CSVOutputFileId.i, Address\Country.s + ",")
WriteString(CSVOutputFileId.i, Address\Phone + ",") ; : Debug Address\Phone
WriteString(CSVOutputFileId.i, Address\Mobile + ",")
WriteString(CSVOutputFileId.i, Address\Fax + ",")
WriteString(CSVOutputFileId.i, Address\Email + ",")
WriteString(CSVOutputFileId.i, Address\URL + ",")
WriteString(CSVOutputFileId.i, Address\PrintCount.s + ",")
WriteString(CSVOutputFileId.i, Address\LastDate.s + ",")
WriteString(CSVOutputFileId.i, Address\Custom1.s + ",")
WriteString(CSVOutputFileId.i, Address\Custom2.s + ",")
WriteString(CSVOutputFileId.i, Address\Custom3.s + ",")
WriteString(CSVOutputFileId.i, Address\Custom4.s + ",")
WriteStringN(CSVOutputFileId.i, Address\Notes.s)
;------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------
ClearStructure(@Address, AddressData)
;------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------
Title.s = ""
Firstname.s = ""
Middlename.s = ""
Lastname.s = ""
Medical.s = ""
Business.s = ""
Pobox.s = ""
Street.s = ""
Suburb.s = ""
City.s = ""
State.s = ""
Postcode.s = ""
Country.s = ""
Comment.s = ""
HomePhone.s = ""
HomeMobile.s = ""
HomeFax.s = ""
HomeEmail.s = ""
HomeWeb.s = ""
WorkPhone.s = ""
WorkFax.s = ""
WorkMobile.s = ""
WorkEmail.s = ""
WorkWeb.s = ""
;------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------
Next ; AddresRecordId.s()
;--------------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------------
CloseFile(CSVOutputFileId.i)
;--------------------------------------------------------------------------------------------
;
;--------------------------------------------------------------------------------------------
Else
Debug "Could not create the CSV output file"
EndIf
;----------------------------------------------------------------------------------------------
;
;----------------------------------------------------------------------------------------------
Else
Debug "Nothing in the list to process"
EndIf
;------------------------------------------------------------------------------------------------
;
;------------------------------------------------------------------------------------------------
EndProcedure
;==============================================================================================================================================================================
;
;==============================================================================================================================================================================
OpenSystemDatabase()
ExportToBrotherLabeller()