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 |
[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 |
|
|
mm dd yy |
|
02 03 94 |
mmm d, yy |
|
|
mmmm d, yyyy |
|
|
d mmmm yyyy |
|
|
hh"h" mm"m" |
|
01h 32m |
h.mm AM/PM |
|
2.56 PM |
hhmm "hours" |
|
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, |
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, |
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 |
|
|
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