Page 1 of 2 12 LastLast
Results 1 to 20 of 35
  1.    #1  
    Here is a fairly easy way to export/backup your palm profile contacts.
    It does basically the same thing as this program: http://forums.precentral.net/web-os-...ontacts-3.html but the developer of it took it off because he does not want to support it.
    All of this information is available on the wiki here, I just wrote simpler, easier to understand instructions on how to perform this.


    The first thing you need to do is copy the following code into notepad, and save it to your desktop as 'script.pl' (no quotes). Just delete the .txt extension when saving it.

    Code:
    #!/usr/bin/perl
    use strict;
    
    # copy of /var/luna/data/dbdata/PalmDatabase.db3 
    my $sqlite = 'sqlite3.exe';
    my $db = 'PalmDatabase.db3';
    
    
    my $contacts = {};
    my @label = ('Home', 'Work', 'Other', 'Mobile');
    
    my $header = q(Name,Given Name,Additional Name,Family Name,Yomi Name,Given Name Yomi,Additional Name Yomi,Family Name Yomi,Name Prefix,Name Suffix,Initials,Nickname,Short Name,Maiden Name,Birthday,Gender,Location,Billing Information,Directory Server,Mileage,Occupation,Hobby,Sensitivity,Priority,Subject,Notes,Group Membership,E-mail 1 - Type,E-mail 1 - Value,E-mail 2 - Type,E-mail 2 - Value,E-mail 3 - Type,E-mail 3 - Value,Phone 1 - Type,Phone 1 - Value,Phone 2 - Type,Phone 2 - Value,Phone 3 - Type,Phone 3 - Value);
    
    my $sql = q{"SELECT com_palm_pim_Contact.firstName, com_palm_pim_Contact.lastName, com_palm_pim_ContactPoint.type, com_palm_pim_ContactPoint.value, com_palm_pim_ContactPoint.label, com_palm_pim_Contact.com_palm_pim_Person_id FROM com_palm_pim_Contact INNER JOIN com_palm_pim_ContactPoint ON com_palm_pim_Contact.id = com_palm_pim_ContactPoint.com_palm_pim_Contact_contactPts_id WHERE syncSource = 'local' AND (com_palm_pim_ContactPoint.type = 'EMAIL' OR com_palm_pim_ContactPoint.type = 'PHONE') ORDER BY com_palm_pim_Contact.firstName, com_palm_pim_Contact.lastName, com_palm_pim_ContactPoint.type ASC;"};
    
    my $output = `$sqlite $db $sql`;
    
    open(CSV, ">palm_pre_contacts.csv");
    open(SQLDEL, ">delete_contacts.sql");
    open(SQLSEL, ">select_contacts.sql");
    
    print SQLDEL ".load /usr/lib/sqlite3_palm_extension.so\n";
    
    print CSV "$header\n";
    
    my @lines = split(/\n/, $output);
    
    foreach my $line (@lines) {
    	#my @parts = split(/\|/, $line);
    	my ($first, $last, $type, $value, $label, $pid) = split(/\|/, $line);
    	
    
    	if ($first && $first ne '') {
    		if (!($contacts->{$pid})) {
    			$contacts->{$pid} = {
    				'first' => $first,
    				'last' => $last,
    				'email' => [],
    				'phone' => []
    			}
    		}
    
    		if ($type eq 'EMAIL') {
    			push(@{$contacts->{$pid}->{email}}, {
    				label => $label[$label],
    				email => $value
    			});
    		}
    		elsif ($type eq 'PHONE') {
    			$value =~ s/[^\d]//g;
    			push(@{$contacts->{$pid}->{phone}}, {
    				label => $label[$label],
    				phone => $value
    			});
    		}
    
    		print SQLDEL "DELETE FROM com_palm_pim_Person WHERE id=$pid;\n";
    		print SQLSEL "SELECT id, firstName, lastName from com_palm_pim_Person WHERE id=$pid;\n";
    	}
    }
    
    while ( my ($pid, $value) = each(%$contacts) ) {
    	my @line = ();
    
    	$line[0] = $value->{'first'} . ' ' . $value->{'last'};
    	$line[1] = $value->{'first'};
    	$line[3] = $value->{'last'};
    	$line[26] = '* My Contacts';
    	$line[27] = $value->{'email'}->[0]->{label};
    	$line[28] = $value->{'email'}->[0]->{email};
    	$line[29] = $value->{'email'}->[1]->{label};
    	$line[30] = $value->{'email'}->[1]->{email};
    	$line[31] = $value->{'email'}->[2]->{label};
    	$line[32] = $value->{'email'}->[2]->{email};
    	$line[33] = $value->{'phone'}->[0]->{label};
    	$line[34] = $value->{'phone'}->[0]->{phone};
    	$line[35] = $value->{'phone'}->[1]->{label};
    	$line[36] = $value->{'phone'}->[1]->{phone};
    	$line[37] = $value->{'phone'}->[2]->{label};
    	$line[38] = $value->{'phone'}->[2]->{phone};
    
    	print CSV join(',', @line) . "\n";
    }
    
    close(CSV);
    close(SQLDEL);
    close(SQLSEL);

    Next, you need to download and install ActivePerl, which you can find here, to run the script you just saved.

    Next, you need to download this file and extract the contents (sqlite3.exe) to your desktop.

    Copy your PalmDatabase.db3 file to your desktop as well. For information on how to get this file, look here.

    You should now have the following 3 files on your desktop:
    1. sqlite3.exe
    2. script.pl
    3. PalmDatabase.db3

    You should also have ActivePerl installed.

    Just double click on script.pl and 3 files should appear on your desktop.
    The one you are interested in is 'palm_pre_contacts.csv'. The other two, you can just delete.

    Now this file can be imported into pretty much any address book, including gmail, Yahoo!, outlook, or Thunderbird!

    I hope these instructions are useful!!

    All credit goes to those members who added the information in the webosinternals.org wiki!


    EDIT: After uploading contacts, you may have duplicate contacts (eg. one in google, one in palm profile). To remedy this, the easiest way is to turn off backup and partial reset.
    Another way is to copy the file generated by the script, 'delete_contacts.sql', to the phone and running it from a command line.
    Last edited by benjio; 12/01/2009 at 07:01 PM.
  2. MaxLOL's Avatar
    Posts
    607 Posts
    Global Posts
    655 Global Posts
    #2  
    I will try to do this when I get off of work. I appreciate your help!
  3. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #3  
    does this also get the call log?
  4. #4  
    Got my contacts... perfect timing! Worked like a charm! Thanks!
  5.    #5  
    Quote Originally Posted by jlehrer View Post
    does this also get the call log?
    Nope, Just the contacts from your palm profile (no other contact accounts eg, facebook, gmail). And it only exports the first 3 phone numbers and the first 3 email addresses per contact.
  6. #6  
    thanks. i just today searched for a way to backup my contacts to Outlook, so this came at a perfect time.

    Only problem I see is that the phone numbers are listed by type and not always in the same order. If a person just has a mobile number that would be phone 1, but if a person has a work and a mobile, then work is phone 1 and mobile is phone 2. So you can't easily map these when importing them into Outlook unfortunately.

    Would be nice if your Perl script could arrange the phone numbers in a predefined order (work, mobile, home) for instance. If a person only has a mobile then the first one would just be blank.
    Last edited by onlinespending; 11/25/2009 at 04:24 PM.
  7. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #7  
    Has anybody found a way to pull the call log off the phone? I previously used a very effective little program to do this with my 755p. It was helpful when I did my client billing.
  8. #8  
    This needs to be stickied. Worked perfect. Thanks benjio.
    Also, benjio, is there any way you could write a script to download everything for all contacts, or at least Facebook and Palm profile. I have a lot of phone numbers from facebook I would like to backup.

    Thanks in advance for anything you can do!
  9. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #9  
    Following the instructions, I get a resulting CSV file that contains column headings, but no records. In looking at the original DB3 file, I can see that the contacts are in that file, which is about 11 mbytes on my system. However, the CSV file is only 500 kb, and contains no records. I wonder what I'm doing wrong...
  10.    #10  
    Quote Originally Posted by uraloony View Post
    This needs to be stickied. Worked perfect. Thanks benjio.
    Also, benjio, is there any way you could write a script to download everything for all contacts, or at least Facebook and Palm profile. I have a lot of phone numbers from facebook I would like to backup.

    Thanks in advance for anything you can do!
    I would, but I didn't write this script, nor do I have any idea how to modify it. I got the script from the wiki page linked at the top of the post. The script originally pulled all of the contacts, regardless of the source, but was recently modified by another member to only get the palm profile contacts. I know this should be possible for someone with some very basic perl programming knowledge.
  11.    #11  
    Quote Originally Posted by jlehrer View Post
    Following the instructions, I get a resulting CSV file that contains column headings, but no records. In looking at the original DB3 file, I can see that the contacts are in that file, which is about 11 mbytes on my system. However, the CSV file is only 500 kb, and contains no records. I wonder what I'm doing wrong...
    I had this very same problem when trying to get this to work myself! It seems the problem with mine was that the 'sqlite3.exe' file was incorrectly named or was the incorrect file altogether. I would just recommend double checking that this file, and your 'PalmDatabase.db3' files are both correctly named and that all 3 files are in the same folder.
  12. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #12  
    Benjio, I checked as you suggested and everything seems right. But I get the same results, just a CSV file with column headers. I tried randomly renaming both the sqlite and the PalmDatabase file, and still got the same result, which tells me that the files are either in the wrong place, or the script is looking in the wrong place for them.

    Interestingly, the script that's posted on the Wiki has one line that's different from the script posted above:

    my $sqlite = './sqlite3-3.6.16.bin';

    The directions on the Wiki say you need an executable binary, but when I click the link to d/l the sqlite file, it extracts to an .exe file on my Windows computer.

    I'm not sure what to try next, but I thank you for your help so far.
  13. gAnDo's Avatar
    Posts
    46 Posts
    Global Posts
    47 Global Posts
    #13  
    Cheers Worked great!
    Now if only I could work out how to get the contacts off my sim so they could be exported...

    edit: Ok I worked out how to do it by changing the SQL in the .pl file

    Code:
    #!/usr/bin/perl
    use strict;
    
    # copy of /var/luna/data/dbdata/PalmDatabase.db3 
    my $sqlite = 'sqlite3.exe';
    my $db = 'PalmDatabase.db3';
    
    my $contacts = {};
    my @label = ('Home', 'Work', 'Other', 'Mobile');
    
    my $header = q(Name,Given Name,Additional Name,Family Name,Yomi Name,Given Name Yomi,Additional Name Yomi,Family Name Yomi,Name Prefix,Name Suffix,Initials,Nickname,Short Name,Maiden Name,Birthday,Gender,Location,Billing Information,Directory Server,Mileage,Occupation,Hobby,Sensitivity,Priority,Subject,Notes,Group Membership,E-mail 1 - Type,E-mail 1 - Value,E-mail 2 - Type,E-mail 2 - Value,E-mail 3 - Type,E-mail 3 - Value,Phone 1 - Type,Phone 1 - Value,Phone 2 - Type,Phone 2 - Value,Phone 3 - Type,Phone 3 - Value);
    
    my $sql = q{"SELECT 
    	com_palm_pim_Contact.firstName, 
    	com_palm_pim_Contact.lastName, 
    	com_palm_pim_ContactPoint.type, 
    	com_palm_pim_ContactPoint.value, 
    	com_palm_pim_ContactPoint.label, 
    	com_palm_pim_Contact.com_palm_pim_Person_id 
    FROM 
    	com_palm_pim_Contact 
    INNER JOIN 
    	com_palm_pim_ContactPoint 
    ON 
    	com_palm_pim_Contact.id = com_palm_pim_ContactPoint.com_palm_pim_Contact_contactPts_id 
    WHERE 
    	(com_palm_pim_ContactPoint.type = 'EMAIL' OR com_palm_pim_ContactPoint.type = 'PHONE') 
    ORDER BY 
    	com_palm_pim_Contact.firstName, 
    	com_palm_pim_Contact.lastName, 
    	com_palm_pim_ContactPoint.type 
    ASC;"};
    
    my $output = `$sqlite $db $sql`;
    
    open(CSV, ">palm_pre_contacts.csv");
    open(SQLDEL, ">delete_contacts.sql");
    open(SQLSEL, ">select_contacts.sql");
    
    print SQLDEL ".load /usr/lib/sqlite3_palm_extension.so\n";
    
    print CSV "$header\n";
    
    my @lines = split(/\n/, $output);
    
    foreach my $line (@lines) {
    	#my @parts = split(/\|/, $line);
    	my ($first, $last, $type, $value, $label, $pid) = split(/\|/, $line);
    	
    
    	if ($first && $first ne '') {
    		if (!($contacts->{$pid})) {
    			$contacts->{$pid} = {
    				'first' => $first,
    				'last' => $last,
    				'email' => [],
    				'phone' => []
    			}
    		}
    
    		if ($type eq 'EMAIL') {
    			push(@{$contacts->{$pid}->{email}}, {
    				label => $label[$label],
    				email => $value
    			});
    		}
    		elsif ($type eq 'PHONE') {
    			$value =~ s/[^\d]//g;
    			push(@{$contacts->{$pid}->{phone}}, {
    				label => $label[$label],
    				phone => $value
    			});
    		}
    
    		print SQLDEL "DELETE FROM com_palm_pim_Person WHERE id=$pid;\n";
    		print SQLSEL "SELECT id, firstName, lastName from com_palm_pim_Person WHERE id=$pid;\n";
    	}
    }
    
    while ( my ($pid, $value) = each(%$contacts) ) {
    	my @line = ();
    
    	$line[0] = $value->{'first'} . ' ' . $value->{'last'};
    	$line[1] = $value->{'first'};
    	$line[3] = $value->{'last'};
    	$line[26] = '* My Contacts';
    	$line[27] = $value->{'email'}->[0]->{label};
    	$line[28] = $value->{'email'}->[0]->{email};
    	$line[29] = $value->{'email'}->[1]->{label};
    	$line[30] = $value->{'email'}->[1]->{email};
    	$line[31] = $value->{'email'}->[2]->{label};
    	$line[32] = $value->{'email'}->[2]->{email};
    	$line[33] = $value->{'phone'}->[0]->{label};
    	$line[34] = $value->{'phone'}->[0]->{phone};
    	$line[35] = $value->{'phone'}->[1]->{label};
    	$line[36] = $value->{'phone'}->[1]->{phone};
    	$line[37] = $value->{'phone'}->[2]->{label};
    	$line[38] = $value->{'phone'}->[2]->{phone};
    
    	print CSV join(',', @line) . "\n";
    }
    
    close(CSV);
    close(SQLDEL);
    close(SQLSEL);
    Last edited by gAnDo; 11/30/2009 at 07:07 AM.
  14.    #14  
    Quote Originally Posted by jlehrer View Post
    Benjio, I checked as you suggested and everything seems right. But I get the same results, just a CSV file with column headers. I tried randomly renaming both the sqlite and the PalmDatabase file, and still got the same result, which tells me that the files are either in the wrong place, or the script is looking in the wrong place for them.

    Interestingly, the script that's posted on the Wiki has one line that's different from the script posted above:

    my $sqlite = './sqlite3-3.6.16.bin';

    The directions on the Wiki say you need an executable binary, but when I click the link to d/l the sqlite file, it extracts to an .exe file on my Windows computer.

    I'm not sure what to try next, but I thank you for your help so far.
    Yes, that line and the line right below it are both different from the original script. In the original script, those lines needed to point to their respective files. In windows, an .exe is an executable binary, and the .bin extension is used as an example because with a little modification, the script should also work on mac and linux as well. But in windows, this is the correct file.

    Maybe try modifying the two lines to point to the respective files in their directories. eg "C:\Documents and Settings\User\Desktop\sqlite3.exe"

    I tried this myself with no luck, but it is honestly the only thing left I can think of to do!
  15. gAnDo's Avatar
    Posts
    46 Posts
    Global Posts
    47 Global Posts
    #15  
    The original script only exports the Palm profile contacts but the script I posted above will (or should!) export ALL contacts. Possibly jlehrer doesnt have any Palm profile contacts, only SIM/Facebook etc.
  16. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #16  
    Hi, guys, thanks again for your advice. I have about 3,000 contacts in my Palm Profile, mostly from sync with Google (originally from Outlook), as well as FB and LinkedIn. I opened the DB3 file in Excel and I could see there is a lot of data in there.

    I think the problem is with sqlite3.exe on my computer. I tried changing the name of the file, and the script executed anyway, producing the same results, even though it couldn't find the sqlite file. (Created three spreadsheets, one with column headers, but no data.)

    I wonder if the version I have, 3.6.20, is not the right version.

    When I double click sqlite3.exe, it opens a CMD window, identifies itself and gives me a sqlite> prompt waiting for my command. This tells me the executable file is capable of executing something.

    I tried, as gando suggested, using the full path to the file in the script, but that produced the same result.

    Please note that this is not "mission critical" for me. I have multiple copies of all my contacts. I'd just like to figure out how this works in case of emergency.
  17. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #17  
    Hey, I spoke too soon. gAnDo, your version of the script works!! Thanks much. I'll have to examine it carefully to see how it differs from the original.
  18. swtoombs's Avatar
    Posts
    45 Posts
    Global Posts
    53 Global Posts
    #18  
    For some reason it exports the contacts but not the numbers or information. Any suggestions?
  19. swtoombs's Avatar
    Posts
    45 Posts
    Global Posts
    53 Global Posts
    #19  
    So the numbers import into the excel file but when I import it into outlook they're not there. The e-mail addresses don't transfer over either.
  20. jlehrer's Avatar
    Posts
    55 Posts
    Global Posts
    57 Global Posts
    #20  
    For me, the phone numbers showed up in scientific notation. Select the column and change the number format to "phone" or whatever the option is in Excel, and they'll look like phone numbers.
Page 1 of 2 12 LastLast

Posting Permissions