Palm synching redux

[jlockley]

Now I really need to import my Paradox information into Palm. Research indicates that MobileDB and HandDBase are the leading programs. Does anyone here have any preferences from the standpoint of Paradox? Other? I believe I can set up a conduit (one time setup) with a secondary program Desktop2MobileDB. The whole project would go for about $60 - However, I also noticed a paper in TheDBCommunity which indicated that P9 (I have 8) had a direct synchronization tool. I am guessing that this is for the Corel Address Book rather than Pdx, correct?

Thanks and Happy New Year to all.

 

[Langley]

If I remember right, PilotDB (freeware) has a conversion tool for Excel, a format which Paradox can export. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Thanks. I am trying HandDB, which imports easily from CSV files. It is painfully slow, by the way.

I have learned a trick to simplify ascii transfer, which comes in quite handy at times like this: Very few programs offer the option of keeping headings, and I haven't yet found a way to export field names as a first indexed record, which means that most importing programs will either prompt for the use of the first record entries for field names or use them automatically.

entering aaaaaaname or 111name in the first indexed field and the field names in all other fields automatically supplies the correct field names in the transfered records.

 

[Langley]

FYI - this is not really documented, but you can use the exportSpreadSheet function to get the first record as fieldnames.

exportSpreadsheet("mystuff.db", "mystuff.csv", True)

This creates a CSV file which you could rename to a TXT file (if necessary).

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

You're a whiz, but remember you are talking to a slow learner here. If I get what you are saying, I could put a button on the standard form which would carry out this function? How much coding is involved? That would be extraordinary.

 

[Langley]

You bet. Try sticking this in a pushbutton. You only need the variable and the rename stuff if you have to rename the file to have a TXT extension. Otherwise it's just the one line. Of course, you might want to do a little error trapping.

var
 fs fileSystem
endvar
;
exportSpreadsheet(":myAlias:mystuff.db", ":myAlias:mystuff.csv", True)
;
fs.rename(":myAlias:mystuff.csv",":myAlias:mystuff.txt"

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Done! I never thought of it. What I think I am going to do is set up a form to handle exports for all of my files, which I occasionally do. Great idea. Thanks.

 

[Langley]

Glad I was able to help. Good luck! Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Up to a point this works wonderuflly, but I am running into one problem. One of the databases contains about 500 fields. It loses about 300. The entries of some of them appear to show up in other records. I am looking for comas and quotation marks in the database in the assumption that these might be the cause, but could there be another?



method pushButton(var eventInfo Event)

var
fs fileSystem
endvar
;
exportSpreadsheet(":ExchangealmSfpfs.db", ":Exchange:SFPFS.csv", True)


endMethod

The same thing works perfectly on the database into which I entered everything myself.

 

[Langley]

Sorry, I don't have a clue. I've never created a table with that many fields. My assumption is the same as yours, data problems. To troubleshoot, try querying it into a small (2 or 3 record chunk) and see if the answer table will export properly. If not, you have less data to look through and if it does you know you are on the right track. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Right. I'm trying, alternatively, to remove the comas and other "characters" by sql or query, but the learning curve is pretty steep.

 

[Langley]

Take a look at the breakApart() procedure. It can be helpful when trying to normalize stuff. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Yep. Doing that, but I borrowed a script with a bit that doesn't make sense. Working on it.

 

[jlockley]

More regarding exportSpreadsheet: This particular database has numerous empty fields at the end of a number of records. What seems to be happening is that it is filling them up from the end. If it a set of seven or eight empty fields with a record with only four full ones, it will then stack up from the next. curious.

 

[jlockley]

That was it. It had nothing to do with the commas. It was backfilling the empty spaces with the next available data. Adding "X" to all empty spaces produced the full set.

 

[Langley]

Great, but is that an acceptable workaround? Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

Well, thanks. You've corrupted me. I have three clients waiting and half a dozen unemployed chefs, and frankly all I can think about is push buttons. In the various wondrous web sites (DBCommunity, etc) is there perchance a sheet of "really cool things you can do with pushbuttons to keep from doing your day job"? I have this one form I've been filling with toys. - Run the query, run the script. Bingo, pull it into palm.

This is fun. Not profitable, but fun. I should go call the Starwars people who need a chef, but I am fixed on buttons. I think I need coffee. And an Object Pal book.

 

[jlockley]

Barely, but looking at the data in the various fields, the points where they broke did not contain commas or other characters which would indicate a reason for the information to be concatinated to the previous record. The commas (now gone, thanks to you) don't seem to have an effect aside from splitting the names when files were moved/concatinated/whatever.

Here's what happened.

I've got two records:

Aidells, Bruce - 415 444 4444 - Aidell's Sausage - xxx Mission - San Frandisco - CA - empty field - empty field - empty field - etc.(dashes indicate new fields)

And: Ainsly, Cynthia - Mother's Cookies - 444 555 5555 - empty field - empty field - empty field, etc
This is obviously the record following Aidells.

The exported and re-imported informations gives me a single record:

Aidells, Bruce - 415 444 4444 - Aidell's Sausage - xxx Mission - San Frandisco - CA - Ainsly - Cynthia - Mother's Cookies - 444 555 5555 - et, "Ainsly" now listed in the cell phone field.

If the commas separating first and last name are removed, the resulting record reads
Aidells, Bruce - 415 444 4444 - Aidell's Sausage - xxx Mission - San Francisco - CA - Ainsly Cynthia - Mother's Cookies - 444 555 5555 - etc-etc.

I can't figure it. The same script run on a test database I put together with only the first third of the fields filled exported record for record accurately. I can't find an indicator in the database. Go figure.

 

[Langley]

Maybe there is a CR/LF problem. Do an export to ASCII and take a look at the data before the import. Maybe that will give some clue as to what's happening. It almost sounds like the empty fields are being ignored, also. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[jlockley]

CRLF?