PDB Converter

Download Current Version – 4.1.4

Copyright 2000-2002 by Michael J. Verive

All Rights Reserved

 

Please note – this documentation in its current form has grown from a simple text file to a long, difficult to follow document.  I apologize for this, and will be working on streamlining and organizing the documentation.  Please bear with me!

 

PDB Converter is a Windows 95/98/98SE/Me/NT/2000/XP application that can create “NSBasic for Palm™”-compatible PDB files interactively (by direct data entry into its spreadsheet) or by converting existing text files (including tab-delimited files), quoted and non-quoted comma separated values (CSV) files, and Excel 4.0 and 5.0 worksheets.  PDB Converter offers powerful spreadsheet editing features, and produces keyed and non-keyed database (PDB) files directly readable by applications written using NSBasic for Palm™.  Once loaded into the Palm™ device, all of the usual NSBasic™ functions used for manipulation of databases can be used on these files.  Any other program that uses PDB files created using PalmDB.DLL should be able to read and write PDB files created by PDB Converter.  Conversely, PDB Converter can take these PDB databases and convert them to Excel 4 or 5 worksheets, quoted or non-quoted CSV files, tab-delimited or simple text files, HTML tables, and complete HTML web pages with embedded tables.

 

PDB Converter can directly read Excel 4/5, tab-delimited, and quoted/non-quoted CSV text files (and also COD, DOC, HTM, HTML, INI, and PRJ files if pure text).  The Formula One control used for worksheet manipulation uses a subset of Excel functions, and will read most non-protected Excel 4/5 files, although not all Excel functions are supported.  Note that although Formula One allows the creation of spreadsheets with graphics and other objects, only fields that contain alphanumeric data can be converted by PDB Converter.  In the event that PDB Converter or the Formula One Worksheet Designer cannot read your spreadsheet, you can load it into Excel, and use the clipboard copy and paste functions to place it into the Formula One Worksheet Designer.  Exiting the Worksheet Designer places the spreadsheet into PDB Converter, which can then create the desired NSBasic™ compatible PDB files.

 

PDB Converter can read HanDBase PDB files as well as NSBasic™ PDB files with string data, and will fill the spreadsheet with the records and their individual fields.  Once in the spreadsheet, this data can be manipulated with the powerful Formula One Worksheet Designer.  The database can then be written as NSBasic-compatible PDB databases, or as Excel, CSV, or text files.

 

The Formula One control used in PDB Converter has a limit of 255 characters per field, and can accommodate spreadsheets up to 16384 rows and 256 columns (equivalent to 16384 records of 256 fields).  Spreadsheet data can also be rotated (swapped) such that rows become columns, and columns become rows.  Because of the 256 column limit, this swapping can only be used on spreadsheets (or selected spreadsheet sections) of up to 256 rows x 256 columns. 

 

Although PDB files are unaffected, rows in the spreadsheet can be 0 to 409 points high.  Columns width is limited to the 0-255 characters.

 

Operating Modes

PDB Converter’s interactive mode now allows you to have control over many options including recalculation (auto or manual), the use of fixed rows and columns, automatic filling of row or column contents, and a “Transaction Manager” that allows you to make temporary changes to your data, return (rollback) to the previous state, or commit the changes to the current spreadsheet view.

 

PDB Converter accepts command-line filenames, and determines the data type and direction based on the number of arguments, and the extensions.  If only one command-line argument is present, PDB Converter will read the file as follows:

 

Filename and extension

Read Mode

Filename.pdb

Read as PDB database of records containing null (chr(0)) -terminated strings, (or as HanDBase databases if HanDBase format).  The Creator ID, PDB type, and PDB Name will be read from the PDB header.

Filename.txt

Read as tab(chr(9)) -delimited text files

Files ending in .cod, .doc, .htm, .html, .ini, .log, and .prj

Read as tab(chr(9)) -delimited text files

Filename.xls

Read as Excel 5.0 worksheet

Filename.csv

Read as quoted/non-quoted strings delimited by commas

 

If two command-line arguments are present, the first file will be the file read, with the second file being the file written.  Files are written using the same extension/type rules as above, except HTM and HTML files (see the next paragraph).  

 

Command-line arguments:

PDB Converter supports additional command-line arguments to alter the format of the file being written.  This table gives the arguments and their general descriptions (the switches will be discussed in more detail following the table):

 

Argument

(may be upper or lower case)

Description

/cfg:

Specifies an alternate configuration file for PDB information

/user:

Specifies the user for HotSyncing the PDB database created

/name:

Specifies the name used when writing a PDB database

/type:

Specifies the database type used when writing a PDB database

/crID:

Specifies the Creator ID used when writing a PDB database

/embq

Specifies that the incoming CSV data contains embedded quotes (not quoted CSV, where quotes are delimiters; the quotes are part of the data)

/fl

Writes PDB as records with fixed-length fields

/ht

Writes HTML table as output

/nq

Writes CSV as non-quoted data

/sort

When reading one database and writing another, sort the data before writing (sorts in ascending order based on the first field)

/srlf

Writes PDB database as single long record, single field, with line feeds added after each record in the original database/spreadsheet

/srnlf

Same as /srlf, but writes data withoug added line feeds

/srmf

Writes PDB database as single record of many fields

 

The argument “/embq” is used when the incoming CSV file uses quotes not as delimiters, but as part of the data.  The argument “/nq” can be used after the two file name command-line arguments to cause non-quoted CSV files to be written.  If PDB databases are being written, a third command-line argument “/fl” can be included to tell PDB Converter to create databases of records of fixed-length fields (fields padded with spaces to equal the length of the longest field.  The default is to write all fields with the actual length of data in the field).  Data in records containing fixed-length fields can be quickly accessed using the dbPosition command with the record number and offset calculated from the lengths of the fields.  When converting PDB’s with fixed-length (padded) fields to variable-length fields, be sure to use “Trim” in PDB Converter to strip away leading and trailing spaces.

 

With the addition of Web Page and HTML Table output options, an additional third command-line argument “/ht” has been added.  As of version 3.1, PDB Converter can output its data as HTML tables (for inclusion in web pages) or as complete web pages with embedded tables.  Since PDB Converter can also read HTML files as text to allow simple editing, the program needs to know whether HTML files are being written as straight text from a previously read HTML file, or whether the spreadsheet data is to be written as a complete web page.  Using the third command-line argument “/ht” when the output file is an HTM or HTML file tells PDB Converter to write its spreadsheet data as a web page with embedded table.  Omitting the “/ht” argument forces PDB Converter to write the spreadsheet row-by-row as simple text.

 

Note that PDB Converter will read the Creator ID, PDB type, and PDB Name from the PDB header when PDB files are read.  However, this data will not be known for non-PDB files, so PDB Converter supplies default values for these parameters.  You can supply your own parameters for use as defaults, and can save them in your own custom configuration files (default is “pdbcvrt.ini”).  These parameters will be important when PDB databases are written, especially in command-line mode.  An additional command line parameter, “/cfg:” can be used to tell PDB Converter to use a different file than “pdbcvrt.ini” for the Creator ID, PDB Type, and PDB Name when writing PDB databases in command line mode.  For instance, this statement:

 

pdbcvt.exe test.csv test.pdb /fl /cfg:test.cfg

 

Will tell PDB Converter to read the file “test.csv”, then write the PDB database “test.pdb” as fixed length fields, using the Creator ID, PDB Type, and PDB Name from the file “test.cfg”.

 

If you want to specify the PDB name, database type, and/or CreatorID on the command line, you can use the /name:, /type:, and /crID: parameters:

pdbcvt.exe test.csv test.pdb /name:testdb /type:data /crID:tst0

 

This will create the PDB database with the name “testdb”, type “data”, and CreatorID “tst0”.  Note: these parameters cannot have any embedded spaces.  If your PDB name, type, or Creator ID require embedded spaces, you will need to create configuration files (save defaults in PDB Converter with the various information) for the PDB file, and use the /cfg: parameter mentioned above.

 

 

New with 4.0 is the ability to install PDB databases to a Palm Device after they are created with PDB Converter.  To allow automatic installation of PDB’s to a device for a specific user, a new command-line parameter, “/user:” has been added:

 

pdbcvt.exe test.csv test.pdb /user:John Doe

 

This will tell PDB Converter to create the database “test.pdb” from the CSV file “test.csv”, and will install the test.pdb database for the user John Doe.  The database isn’t actually installed to the device at this time, but will be transferred at the next HotSync.  The username (in this case John Doe) must exactly match one of the installed users on the Windows system running PDB Converter.  This name is case-sensitive, so “john doe” or “JOHN DOE” won’t match.  Note: the HotSync user name parameter, if used, must be the last parameter on the command line.

 

PDB Converter can create PDB’s as single records with many fields (one for each row:column item in the database), or a single record and a single field, concatenating all text from all rows and columns into a single, long string, with or without line feeds after each record in the original database.  To use these features in command-line mode, there are three parameters, “/SRMF”, “/SRLF” and “/SRNLF”:

 

pdbcvt.exe  test.csv test.pdb /SRMF

 

This will read the test.csv file, and write the test.pdb database with a single record of many fields.

 

pdbcvt.exe test.csv test.pdb /SRLF

 

This creates the test.pdb database with a single record, single field, with line feeds added after the text from each individual record in the original database.

 

pdbcvt.exe test.csv test.pdb /SRNLF

 

This creates the same test.pdb single record/single field database, but without the additional line feed character after the individual records from the original database.

 

pdbcvt.exe test.csv test.pdb /sort

 

This creates the test.pdb database from the test.csv file, but sorts the rows in ascending order according to the contents of the first column.

 

 

 

 

Examples:

 

pdbcvt story.pdb – read the file story.pdb for interactive editing

 

pdbcvt story.pdb book.xls – read the file story.pdb and write the file book.xls (without user intervention)

 

pdbcvt story.pdb book.csv – read the file story.pdb and write the file book.csv as quoted CSV strings

 

pdbcvt story.pdb book.csv /nq – read the file story.pdb and write the file book.csv as non-quoted CSV strings

 

pdbcvt story.csv book.pdb /fl – read the file story.csv and write the file book.pdb as records of fixed-length fields

 

pdbcvt story.csv book.pdb /fl /user:John Doe – read the file story.csv and write the file book.pdb as records of fixed-length fields, then install the book.pdb into John Doe’s Palm device at next HotSync

 

pdbcvt story.pdb book.htm /ht – read the file story.pdb and write a web page with the data from story.pdb as an embedded table

 

pdbcvt story.pdb book.tbl – read the file story.pdb and write an HTML table with the data from story.pdb.  This table can then be included in web pages.

 

As shown above, PDB Converter looks for spaces between the command-line arguments (other than the user name) to determine the number of arguments present.  Because of this, embedded spaces in filenames will be misinterpreted as separators, and PDB Converter will not give the desired results.  Therefore, do not use file names with embedded spaces when running PDB Converter in command-line mode.  This also applies to files being loaded into PDB Converter by drag-and-drop onto a desktop shortcut.  However, when run in interactive mode, PDB Converter can read and write databases with spaces in their filenames.

 

Spreadsheet to database conversions

Data in PDB Converter is written to that each row corresponds to a record, and each column a field within the record.  Therefore, the contents of B6 become the 6th record, 2nd field.  Non-delimited text files, HTM, INI, and other simple text files are typically read as records of single fields. 

 

Sorting records by key for NSBasic

Databases in NSBasic can be written and read using keyed and non-keyed access methods.  When records are accessed by a string key (the first field in each record), the PDB must be in key-sorted order for the records to be accessible.  PDB Converter can sort string data by ASCII values for the string characters.  Unfortunately, the PalmOS routine used by NSBasic uses a different, somewhat arbitrary appearing scheme.

 

The following table contains the characters and ASCII codes in the order the PalmOS uses for determining the record order based on string keys in keyed databases.  Note that many of the ASCII values don’t have characters displayable using Palm Font 0 (the default font for most Palm apps).  This sort order is absolutely non-intuitive, and my search of the PalmOS documentation didn’t explain just how this sort order came about. 

 

Palm Sort Order (read columns down, then go to next column).  Characters marked with * are nondisplayable characters.

Char*

ASCII

Char

ASCII

Char

ASCII

Char

ASCII

Char

ASCII

(nd)

0

%

37

¥

165

C

67

ò

242

(nd)

1

&

38

(nd)

128

ç

231

Ò

210

(nd)

2

(

40

§

167

Ç

199

ô

244

(nd)

3

)

41

©

169

d

100

Ô

212

(nd)

4

*

42

¬

172

D

68

ö

246

(nd)

5

,

44

®

174

ð

240

Ö

214

(nd)

6

.

46

°

176

Ð

208

õ

245

(nd)

7

/

47

µ

181

e

101

Õ

213

(nd)

8

:

58

182

E

69

ø

248

(nd)

14

;

59

·

183

é

233

Ø

216

(nd)

15

?

63

134

É

201

œ

156

(nd)

16

@

64

135

è

232

Œ

140

(nd)

17

[

91

149

È

200

p

112

(nd)

18

\

92

133

ê

234

P

80

(nd)

19

]

93

137

Ê

202

q

113

(nd)

20

^

94

0

48

ë

235

Q

81

(nd)

21

ˆ

136

¼

188

Ë

203

r

114

(nd)

22

_

95

½

189

f

102

R

82

(nd)

23

`

96

¾

190

F

70

s

115

(nd)

24

{

123

1

49

ƒ

131

S

83

(nd)

25

|

124

¹

185

g

103

š

154

(nd)

26

}

125

2

50

G

71

Š

138

(nd)

27

~

126

²

178

h

104

ß

223

(nd)

28

¡

161

3

51

H

72

t

116

(nd)

29

¦

166

³

179

i

105

T

84

(nd)

30

¨

168

4

52

I

73

þ

254

­(nd)

­31

¯

175

5

53

í

237

Þ

222

(nd)

127

´

180

6

54

Í

205

153

(nd)

129

¸

184

7

55

ì

236

u

117

(nd)

141

¿

191

8

56

Ì

204

U

85

(nd)

142

˜

152

9

57

î

238

ú

250

(nd)

143

145

a

97

Î

206

Ú

218

(nd)

144

146

A

65

ï

239

ù

249

(nd)

157

130

ª

170

Ï

207

Ù

217

(nd)

158

147

á

225

j

106

û

251

'

39

148

Á

193

J

74

Û

219

-

45

132

à

224

k

107

ü

252

(nd)

­173

139

À

192

K

75

Ü

220

(nd)

150

155

â

226

l

108

v

118

(nd)

151

+

43

Â

194

L

76

V

86

(space)

32

<

60

ä

228

m

109

w

119

(nd)

160

=

61

Ä

196

M

77

W

87

(nd)

9

>

62

ã

227

n

110

x

120

(nd)

10

±

177

Ã

195

N

78

X

88

(nd)

11

«

171

å

229

ñ

241

y

121

(nd)

12

»

187

Å

197

Ñ

209

Y

89

(nd)

13

×

215

æ

230

o

111

ý

253

!

33

÷

247

Æ

198

O

79

Ý

221

"

34

¢

162

b

98

(nd)

186

ÿ

255

#

35

£

163

B

66

ó

243

Ÿ

159

$

36

¤

164

c

99

Ó

211

z

122

 

The most important take-home message is that if you are preparing non-Palm software to sort data prior to syncing with the Palm for use in NSBasic keyed databases, use the ASCII number order as shown, or NSBasic applications may not find all records using keyed access methods.

 

The thought of implementing this sort order in code gives me a headache (it's best to use some form of lookup table). 

 

Encryption/Decryption

Starting with version 3.2, PDB Converter added simple text encryption/decryption using a substitution encryption scheme.  Two methods are supported: default, and custom.  When encrypting with either method, characters in the original text are compared to characters in a source string.  If the characters exist in the source string, the corresponding characters in the target string are written to the PDB Converter spreadsheet.  Decryption uses the same strings to recover the original text.  The default strings are:

 

source "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

target "ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba"

 

With custom encryption/decryption, you supply the source and target strings.  As with default strings, characters that exist in the source string are replaced with characters in the source string.  Characters that don’t exist in the source string will be copied directly to the spreadsheet without conversion.  Because of this, both strings should be as complete as possible, and must be of the same length. 

 

For stronger encryption, PDB Converter 3.4 added standard DES encryption/decryption, incorporating a user-supplied 8 character key.  Because the strings produced by this encryption method can be longer than the original text by up to 9 characters, keep this in mind when encrypting or decrypting data in PDB Converter, and keep your fields no longer than 246 characters (246+9=255, the limit for fields in PDB Converter).  Additionally, CSV files are read and parsed to remove extra LF and CR (chr 10 and 13) characters.  Since encrypted files can contain embedded LF, CR, commas, and other quotation marks, it is possible that CSV files saved after encryption will not be read back correctly.  PDB databases should be unaffected by this conversion, although PDB’s saved as fixed length fields may have additional spaces added to the fields, which will be incorrectly converted during decryption.  Because of this, save encrypted PDB’s with records of variable length fields.  For NSBasic DES routines, see the BitsNBytes.ZIP file for the documentation and sample files.  Additionally, a DLL and sample code are included for use Visual Basic/Visual C++ (this DLL was used in PDB Converter).

 

AutoFill

Added in PDB Converter 3.3.6 are custom Auto Fills.  For instance, if AllowAutoFill is selected (as it is by default), entering data like “January,” “Monday,” “North,” etc., and moving to an adjacent cell (down or to the right) will cause the next data in the series to automatically be entered (in this case, “February,” Tuesday,” and “South.”).  The following table (produced in PDB Converter), shows the built-in AutoFill lists.  Support will be added in the future for user-defined lists.

 

AutoFill Lists

January

Monday

Jan

Mon

A

a

North

Bottom

Left

enero

lunes

Winter

February

Tuesday

Feb

Tue

B

b

South

Middle

Middle

febrero

martes

Spring

March

Wednesday

Mar

Wed

C

c

East

Top

Right

marzo

miercoles

Summer

April

Thursday

Apr

Thu

D

d

West

 

 

abril

jueves

Fall

May

Friday

May

Fri

E

e

 

 

 

mayo

viernes

 

June

Saturday

Jun

Sat

F

f

 

 

 

junio

sabado

 

July

Sunday

Jul

Sun

G

g

 

 

 

julio

domingo

 

August

 

Aug

Mon

H

h

 

 

 

agosto

 

 

September

 

Sep

Tue

I

i

 

 

 

septiembre

 

 

October

 

Oct

Wed

J

j

 

 

 

octubre

 

 

November

 

Nov

Thu

K

k

 

 

 

noviembre

 

 

December

 

Dec

Fri

L

l

 

 

 

diciembre

 

 

 

 

 

 

M

m

 

 

 

 

 

 

 

 

 

 

N

n

 

 

 

 

 

 

 

 

 

 

O

o

 

 

 

 

 

 

 

 

 

 

P

p

 

 

 

 

 

 

 

 

 

 

Q

q

 

 

 

 

 

 

 

 

 

 

R

r

 

 

 

 

 

 

 

 

 

 

S

s

 

 

 

 

 

 

 

 

 

 

T

t

 

 

 

 

 

 

 

 

 

 

U

u

 

 

 

 

 

 

 

 

 

 

V

v

 

 

 

 

 

 

 

 

 

 

W

w

 

 

 

 

 

 

 

 

 

 

X

x

 

 

 

 

 

 

 

 

 

 

Y

y

 

 

 

 

 

 

 

 

 

 

Z

z

 

 

 

 

 

 

                                                                                                                                   

Although not listed in the table above, Italian lists (for the months of the year, days of the week, seasons, and map directions) and the Greek alphabet are also provided.

 

Validating Data

New in 3.5 is the ability to restrict data entry by the use of “Validation Rules”.  To restrict a range of cells for data entry, drag the mouse to select the range desired, then choose {Data} > {Validation Rules}.  You then enter a rule and the text to be displayed in case the data does not match the rule.  For instance, select the range A1:B10, and set the rule “A1>500” and text “Data entered must be greater than 500” (without the quotation marks).  This would restrict not only A1, but all cells in the range to values > 500, popping up an error message if the user entered a value < 500.

 

NSBasic/Palm Code Modules

Version 3.6 of PDB Converter adds the ability to produce code modules based on the information in the current database.  These code modules can be added to your NSBasic/Palm project like any other code module, and contain code for the most common database operations on existing databases (open database, close database, read first record, read next record, read previous record, read last record, find record by key, read record by key, insert keyed record, and update keyed record).  You can specify the first row to be used as field names, or generic names can be used for the individual record fields.  All field data are assumed to be string types (by default for PDB Converter databases).  Global variables are defined in the “Open Database” subroutine, and the other functions assume that the database has already been opened.  Failure to open databases prior to record access will result in errors (and probably program crashes as well).  The ability to produce code modules was added to help beginning NSBasic/Palm programmers get started.  Once you get comfortable with databases functions, you will probably want to create your own routines.

 

Compatibility issues

PDB Converter reads/writes only standard NSBasic PDB databases of string data, and only supports Excel version 4 or 5 single, non-protected worksheets.  PDB’s written using PDB Converter can be read using NSBasic’s keyed (using version 1.09beta2 or greater of the NSBasic runtime) and non-keyed database access methods.  The strings written to PDB files by PDB Converter are null-terminated (the PalmOS standard), so the length of the string will be one greater than the actual length of the string data. 

 

Data from text, PDB, and CSV files are read as strings, without conversion.  Because of the way Excel files are read and written by the embedded Formula One control, date and time values are converted to numeric values.  You can use the {Format} > {Number} option to change the date and time values to the desired format, followed by {Format} > {Convert to text as displayed}.  Similarly, if your data contains numbers that should have leading zeros (such as ZIP codes), you can use the {Format} > {Number} option with a user-defined mask to force the numbers to be converted to strings (although for 5-digit ZIP codes there is a new ZIP code format option, {Format} > {ZIP Code (5 digit)}).  The following table gives the formatting characters and their functions.

 

Format Symbol

Description

General

Displays the number in General format.

0

Digit placeholder. If the number contains fewer digits than the format contains placeholders, the number is padded with 0's. If there are more digits to the right of the decimal than there are placeholders, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits are retained.

#

Digit placeholder. This placeholder functions the same as the 0 placeholder except the number is not padded with 0's if the number contains fewer digits than the format contains placeholders.

?

Digit placeholder. This placeholder functions the same as the 0 placeholder except that spaces are used to pad the digits.

. (period)

Decimal point. Determines how many digits (0's or #'s) are displayed on either side of the decimal point. If the format contains only #'s left of the decimal point, numbers less than 1 begin with a decimal point. If the format contains 0’s left of the decimal point, numbers less than 1 begin with a 0 left of the decimal point.

%

Displays the number as a percentage. The number is multiplied by 100 and the % character is appended.

, (comma)

Thousands separator. If the format contains commas separated by #'s or 0's, the number is displayed with commas separating thousands. A comma following a placeholder scales the number by a thousand. For example, the format 0, scales the number by 1000 (e.g., 10,000 would be displayed as 10).

E- E+ e- e+

Displays the number as scientific notation. If the format contains a scientific notation symbol to the left of a 0 or # placeholder, the number is displayed in scientific notation and an E or an e is added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. E- and e- place a minus sign by negative exponents. E+ and e+ place a minus sign by negative exponents and a plus sign by positive exponents.

$ - + / ( ) : space

Displays that character. To display a character other than those listed, precede the character with a back slash (\) or enclose the character in double quotation marks (" "). You can also use the slash (/) for fraction formats.

\

Displays the next character. The backslash is not displayed. You can also display a character or string of characters by surrounding the characters with double quotation marks (" ").

 

The backslash is inserted automatically for the following characters:

! ^ & ` (left quote) ' (right quote) ~ { } = < >

* (asterisk)

Repeats the next character until the width of the column is filled. You cannot have more than one asterisk in each format section.

_ (underline)

Skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, you can include the format _) for positive numbers to skip the width of a parenthesis.

"text"

Displays the text inside the quotation marks.

@

Text placeholder. If there is text in the cell, the text replaces the @ format character.

m

Month number. Displays the month as digits without leading zeros (e.g., 1-12). Can also represent minutes when used with h or hh formats.

mm

Month number. Displays the month as digits with leading zeros (e.g., 01-12). Can also represent minutes when used with the h or hh formats.

mmm

Month abbreviation. Displays the month as an abbreviation (e.g., Jan-Dec).

mmmm

Month name. Displays the month as a full name (e.g., January-December).

d

Day number. Displays the day as digits with no leading zero (e.g., 1-2).

dd

Day number. Displays the day as digits with leading zeros (e.g., 01-02).

ddd

Day abbreviation. Displays the day as an abbreviation (e.g., Sun-Sat).

dddd

Day name. Displays the day as a full name (e.g., Sunday-Saturday).

yy

Year number. Displays the year as a two-digit number (e.g., 00-99).

yyyy

Year number. Displays the year as a four-digit number (e.g., 1900-2078).

h

Hour number. Displays the hour as a number without leading zeros (1-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.

hh

Hour number. Displays the hour as a number with leading zeros (01-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.

m

Minute number. Displays the minute as a number without leading zeros (0-59). The m format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.

mm

Minute number. Displays the minute as a number with leading zeros (00-59). The mm format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.

s

Second number. Displays the second as a number without leading zeros (0-59).

ss

Second number. Displays the second as a number with leading zeros (00-59).

AM/PM am/pm A/P a/p

12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, or a for times between midnight and noon; displays PM, pm, P, or p for times from noon until midnight.

[h]

Outputs total number of hours

[m]

Outputs total number of minutes

[s]

Outputs total number of seconds

s.0, s.00, s.000, ss.0, ss.00, ss.000

Outputs fractional part of second.

[BLACK]

Displays cell text in black.

[BLUE]

Displays cell text in blue.

[CYAN]

Displays cell text in cyan.

[GREEN]

Displays cell text in green.

[MAGENTA]

Displays cell text in magenta.

[RED]

Displays cell text in red.                                                               

[WHITE]

Displays cell text in white.

[YELLOW]

Displays cell text in yellow.

[COLOR n]

Displays cell text using the corresponding color in the color palette. n is a color in the color palette.

[conditional value]

Each format can have as many as four sections - one each for positive numbers, negative numbers, zeros, and text. Using the conditional value brackets ([ ]), you can designate a different condition for each section. For example, you might want positive numbers displayed in black, negative numbers in red, and zeros in blue. The following string formats a number for these conditions:[>0] [BLACK]General; [<0] [RED]General; [BLUE]General

 

 

The following table shows some examples of custom number formats and numbers displayed using the custom formats.

 

Format

Cell Data

Display

#.##

123.456

123.46

 

0.2

.2

#.0#

123.456

123.46

 

123

123.0

#,##0"CR";#,##0"DR";0

1234.567

1,235CR

 

0

0

 

-123.45

123DR

#,

10000

10

"Sales="0.0

123.45

Sales=123.5

 

-123.45

-Sales=123.5

"X="0.0;"x="-0.0

-12.34

x=-12.3

$* #,##0.00;$* -#,##0.00

1234.567

$ 1,234.57

 

-12.34

$ -12.34

000-00-0000

123456789

123-45-6789

"Cust. No." 0000

1234

Cust. No. 1234

;;;

Anything

(Not Displayed)

"The End"

123.45

The End

 

-123.45

-The End

m-d-yy

2/3/94

2-3-94

mm dd yy

2/3/94

02 03 94

mmm d, yy

2/3/94

Feb 3, 94

mmmm d, yyyy

2/3/94

February 3, 1994

d mmmm yyyy

2/3/94

3 February 1994

hh"h" mm"m"

1:32 AM

01h 32m

h.mm AM/PM

14:56

2.56 PM

hhmm "hours"

3:15

0315 hours

 

PDB Converter 3.3 added support for user-defined format strings, including all of the above formats, and Microsoft Visual Basic’s “predefined named format strings”.  The following tables list these named format strings and their descriptions:

 

Predefined numeric formats:

Format name

Description

General Number

Display number with no thousand separator.

Currency

Display number with thousand separator, if appropriate; display two digits to the right of the decimal separator. Output is based on system locale settings.

Fixed

Display at least one digit to the left and two digits to the right of the decimal separator.

Standard

Display number with thousand separator, at least one digit to the left and two digits to the right of the decimal separator.

Percent

Display number multiplied by 100 with a percent sign (%) appended to the right; always display two digits to the right of the decimal separator.

Scientific

Use standard scientific notation.

Yes/No

Display No if number is 0; otherwise, display Yes.

True/False

Display False if number is 0; otherwise, display True.

On/Off

Display Off if number is 0; otherwise, display On.


Predefined date and time formats:

Format Name

Description

General Date

Display a date and/or time. For real numbers, display a date and time, for example, 4/3/93 05:34 PM.If there is no fractional part, display only a date, for example, 4/3/93. If there is no integer part, display time only, for example, 05:34 PM. Date display is determined by your system settings.

Long Date

Display a date according to your system's long date format.

Medium Date

Display a date using the medium date format appropriate for the language version of the host application.

Short Date

Display a date using your system's short date format.

Long Time

Display a time using your system's long time format; includes hours, minutes, seconds.

Medium Time

Display time in 12-hour format using hours and minutes and the AM/PM designator.

Short Time

Display a time using the 24-hour format, for example, 17:45.

 

Palm database considerations

The PDB Name must be 1 to 31 characters long, and the Creator ID and PDB Type must use 4 alphanumeric characters from ASCII 33-127.  The PDB Name and Creator ID are case-sensitive, so be sure that your calling program uses the appropriate case for each. 

 

CSV information

CSV files read and written using PDB Converter, whether they are CSV files for the PC or PDB’s written in quoted CSV format use a very simple CSV format: the string fields are enclosed in quotes, and separated by commas.  Embedded control characters are not expanded or supported, nor are embedded carriage returns or line feeds.  Non-quoted CSV strings use the comma as the only delimiter, so embedded commas will cause data errors.

 


'*******************

'Sample NSBasic Code

'09/14/2001

'For databases written using PDB Converter

'

'sample code for illustration only - you will need to make any changes to reflect your data

'*******************

'Variable declaration - use Globals as needed for access in any code segment of your application

Dim dbfh as Database ' set aside a variable for the database file handle

Dim sDBName as String ' the internal name, up to 31 characters

Dim sCreatorID as String ' the 4-character, case-sensitive creator ID

Dim sDBKey as String ' for keyed access, the first field in the record

Dim sField1 as String ' the first field after the key (for keyed access), or the first field (non-keyed)

'dimension additional fields as needed

Dim errorcode as Integer ' use to return the error code for database access statements

Dim CardNo as Integer ' Palm cautions against using a literal number, and suggests a variable instead

Dim NumRecs as Integer ' number of records in database

 

'Sample statements for assigning database access variables

sDBName="TestData" ' internal database name

sCreatorID="Test" ' Palm recommends using the Creator ID of the app being used to access the data

CardNo=0 ' Currently NSBasic only supports card 0

 

'creating a database

errorcode=dbCreate(dbfh,sDBName,CardNo,sCreatorID)

 

'opening a database

errorcode=dbOpen(dbfh,sDBName,CardNo)

 

'closing database

errorcode=dbClose(dbfh)

 

'erasing a database from memory

errorcode=dbErase(dbfh) ' database must be closed, and it must have been opened at least once

 

'get number of records

NumRecs=dbGetNoRecs(dbfh)

 

'keyed access

errorcode=dbFind(dbfh,sDBKey) ' does record exist with given key? (if yes, errorcode=0)

errorcode=dbRead(dbfh,sDBKey,sField1) ' read record with given key - use up to as many fields as there are in your record.  If errorcode=0, success!  If errorcode=2, key not found, and next key and data returned

errorcode=dbReset(dbfh) ' use to reset file pointer to start of file - use before dbReadNext if you are trying to read records from the beginning of the database

errorcode=dbReadNext(dbfh,sDBKey,sField1) ' read next physical record

errorcode=dbReadPrev(dbfh,sDBKey,sField1) ' read previous physical record

errorcode=dbInsert(dbfh,sDBKey,sField1) ' insert new record with unique key

errorcode=dbUpdate(dbfh,sDBKey,sField1) ' update record with existing key

errorcode=dbDelete(dbfh,sDBKey) ' delete record with existing key

 

'non-keyed access

Dim RecNum as integer ' use to point to desired record

Dim FieldOffset as integer ' offset in bytes from start of record for the specified field

errorcode=dbPosition(dbfh,RecNum,FieldOffset) ' set file pointer to desired record and field.  Use FieldOffset of 0 for beginning of record

errorcode=dbGet(dbfh,sField1) ' read the current variable(s) after using dbPosition

errorcode=dbPut(dbfh,sField1) ' write the variable(s) at the current position in the database

 

'Note: when reading or writing data, you can access more than one variable in each statement:

'errorcode=dbRead(dbfh,sDBKey,sField1,sField2,sField3,...)

'this is effective for dbRead, dbReadNext, dbReadPrev, dbInsert, dbUpdate, dbPut, and dbGet

 

‘Substitution encryption/decryption using default strings:

Sub Encrypt(InputString as String, OutputString as String)

Dim CharPointer as Integer

Dim TempString as String

Dim TestChar as String

Dim sourcestring as String

Dim targetstring as String

sourcestring = "1234567890 aBcDeFgHiJkLmNoPqRsTuVwXyZAbCdEfGhIjKlMnOpQrStUvWxYz"

targetstring = "zyxwvutsrqponmlkjihgfedcba1234567890 ZYXWVUTSRQPONMLKJIHGFEDCBA"

TempString=""

For CharPointer=1 to len(InputString)

testchar=mid(inputstring,charpointer,1)

If instr(1,sourcestring,testchar,0)=0 Then

   TempString=TempString+testchar

Else

   TempString=TempString+mid(targetstring, instr(1,sourcestring,testchar,0),1)

End If

Next

OutputString=TempString

End Sub

Sub Decrypt(InputString as String, OutputString as String)

Dim CharPointer as Integer

Dim TempString as String

Dim TestChar as String

Dim sourcestring as String

Dim targetstring as String

targetstring = "1234567890 aBcDeFgHiJkLmNoPqRsTuVwXyZAbCdEfGhIjKlMnOpQrStUvWxYz"

sourcestring = "zyxwvutsrqponmlkjihgfedcba1234567890 ZYXWVUTSRQPONMLKJIHGFEDCBA"

TempString=""

For CharPointer=1 to len(InputString)

   testchar=mid(inputstring,charpointer,1)

   If instr(1,sourcestring,testchar,0)=0 Then

      TempString=TempString+testchar

   Else

      TempString=TempString+mid(targetstring, instr(1,sourcestring,testchar,0),1)

   End If

Next

OutputString=TempString

End Sub

'*******************


 

Revision History for released versions.

 

 

4.1.4

Changed tabbed-text file writing routine.  Old routine did not always write tabs correctly in command-line mode.

 

 

4.1.3

Added support for non-quoted CSV data with embedded quotes.

 

 

4.1.2

Fixed bug with setting PDB name, type, and creator id from command line.  Also fixed problem with parameters being converted to all upper case.

 

 

4.1.1

Added the missing encdec.dll (used for DES encryption/decryption) back into the archive on 5/20/2002.  Sorry for leaving it out!!!

 

 

4.1.0

Modified PDB writing routines to eliminate the extra null (chr(0)) character that was previously added after records.  PDB reading routine modified to accommodate records with or without the extra null character.  This modification will be “invisible” for most users, and results in slightly smaller database files. 

 

 

4.0.8

Added /name:, /type:, /crID:, and /sort command line options

 

 

4.0.7

Added ability to modify name of configuration file during save

 

 

4.0.6

Code optimization for CSV and PDB writing routines

 

 

4.0.4

Added /SRMF command-line argument, minor bug fix

 

 

4.0.3

Added /SRLF and /SRNLF command-line arguments

 

 

4.0.2

Added searching of fields for text, and swapping rows and columns.  Minor code optimization and consolidation.

 

 

 

4.0.1

Modified CSV reading to allow parsing of CSV files using a line feed (chr(10)) as record delimiter (previously used carriage return (chr(13)) as only valid record delimiter).

 

 

 

4.0.0

Added ability to install PDB to user device after creation (thanks to Jaime Ramirez for code!) in both interactive and command-line mode.  Rewrite of CSV parsing and worksheet initialization, tightened memory requirements.

 

 

3.9.1

Removed “buggy” file SaveAs dialog (duplicated existing functions).  Exiting with unsaved, changed data now allows save to Excel worksheet for most efficient disk use.  Other PC formats available by opening the Worksheet Designer (“Options” > “Worksheet Designer”), then selecting “Write” from the “File” menu.

 

 

 

3.9.0

Code optimization, documentation update.  Added generic PDB Reader.  Format now can pad cells (left or right) with spaces or user character.

 

 

3.8.6

Fixed problem running command-line mode without ini file in current directory.

 

 

3.8.5

Reverted to compiling under Windows 98SE.  Versions compiled using Windows XP installed NT DLL’s on earlier systems.

 

 

3.8.3

Code optimization, addition of Greek alphabet to AutoFill.

 

 

3.8.0

Fixed problem reading CSV files with unequal numbers of fields/record (rewrote CSV parsing routine).  Fixed cancel errors with certain dialogs.

 

 

3.7.7

Fixed problem with initializing new workbook and cancel not exiting file open/save dialogs.  Improved program speed by removing unnecessary second status line display.  Refreshed screen after file dialogs to “clean up” display.  Now uses PDB Name as default filename in file dialogs.

 

 

3.7

Improved command-line parser, added support for multiple configuration files for the Creator ID, PDB Type, and PDB Name during command-line mode processing.

 

 

3.6.12

Fixed problem with code module not naming fields correctly.  Minor additional optimization, removal of debug-only code.

 

 

3.6.3

Fixed errors with saving ID, name, and type.  Screen now not shown in command-line mode, improving speed with less desktop “clutter”.

 

 

3.6

Added ability to write NSBasic code modules

 

 

3.5.2

Added direct link to web site to check for (and optionally download) updates.

 

 

3.5

Added edit bar, additional formatting options, optimized several code sections.  Added ability to restrict data entry using validation rules.

 

 

 

3.4

Added DES encryption/decryption.

 

 

3.3.7

Fixed menu items, and added support for additional AutoFill lists, transfer of data as tab-delimited text blocks, simpler numeric formatting for common formats.

 

 

3.3

Fixed “Convert to text as displayed” when used on cells containing formulas.  Added support for Microsoft VB predefined named format strings.

 

 

3.2

Added encryption/decryption using default and custom substitution ciphers.

 

 

3.1

Added ability to write HTML tables and web pages, added “Save As...” feature.  Documentation update.

 

 

3.0.3

Fixed problem writing non-quoted CSV files, reading HanDBase files in command-line mode.  Rewrite of several code sections to remove extraneous and orphaned code.  Revision of documentation.

 

 

 

3.0

 Rewrite of code for many sections, added “transaction” maintenance to allow changes to be made but not committed immediately (can be undone), several options to enhance speed and flexibility while working in spreadsheet view.

 

 

2.9.11

 Added “Convert to text as displayed” format option to change numeric values to their string equivalents (especially useful for dates and times).

 

 

2.9.7

 Added special 5 digit ZIP Code formatting.

 

 

2.9

 Additional status reporting, formatting, IDE updating.  Documentation modified to include information on formatting.

 

 

2.82

 Additional code tweaking.  Sample projects with databases added for demonstration of keyed and non-keyed database access using NSBasic/Palm.

 

 

2.81

 Minor code tweaking.  Added sample NSBasic code.

 

 

2.8

 Added ability to read HanDBase PDB databases.

 

 

2.7

 Added ability to insert/delete rows/columns directly, also now includes option to trim leading and trailing spaces from all cells (for converting fixed-length fields to variable-length).

 

 

2.6

 Added support for creating PDB databases with records of either variable-length or fixed-length fields.

 

 

2.5

 Added support for both standard text and tab-delimited text files.

 

 

2.4.1

 Fixed problem reading HTM and HTML files.

 

 

2.4

 Added support for NSBasic .COD and .PRJ files, as well as files ending with the three letter extensions: DOC, HTM, HTML, INI, and LOG.  All of these file types will be read/written as tab-delimited text files, with the same 255 character limit for each line.

 

 

2.3.0.1

 Added improved error reporting.

 

 

2.3

 Fixed problem with not saving Creator ID, PDB Type, and PDB Name defaults for automatic conversions, other bug fixes and minor speed/cosmetic improvements.

 

 

2.2

 Added ability to save records as individual records or single, long records.

 

 

2.1

 Improved and streamlined menu system, added quoted CSV support.

 

 

2.0

 Added support for CSV files.

 

 

1.0

 Initial release, supported PDB, Excel and text conversions.

 

I hope you find this tool useful and easy to use.  If you have any questions, I can be reached by email at mail@mverive.com.

 

Michael J. Verive

www.mverive.com