Link to home
Start Free TrialLog in
Avatar of Jerry L
Jerry LFlag for United States of America

asked on

Excel VB Script or Macro Needed to Convert Two Rows of Text (Gmail vCard VCF Syntax) Into One Row (Samsung vCard VCF Syntax)

SYSTEM SPECIFICATIONS

MS Windows 7
MS Office (Excel) 2013
Samsung Galaxy S4 Smartphone

BACKGROUND

After much research, I have found a viable solution to the problem of converting a vCard VCF file to CSV and back again to VCF.

You can see the discussion of that solution in my previous EE Question https://www.experts-exchange.com/questions/28439303/Convert-vCard-vcf-File-to-CSV-and-Back-for-Samsung-S4.html?anchorAnswerId=40269244#a40269244

Two problems remain, however. When the CSV file is re-formatted as VCF, the syntax is not compatible with my device.

QUESTION

Can you help me write a script for Excel that will perform the following.

1. Take the single-line syntax from the Samsung syntax and convert it to two-line Gmail syntax.
2. Take the two-line syntax from Gmail and convert it to single-line Samsung syntax.

For example, take a line such as this...
TEL;TYPE=X-Membership:8004326348

Open in new window

and convert it to this...
item2.TEL:8004326348
item2.X-ABLabel:Membership

Open in new window

NOTE: The VCF file is a simple text file. I will copy and paste the contents into an Excel spreadsheet in a single COLUMN. Each statement (line) of the VCF file will be stored in one ROW of that COLUMN. The converted results should be stored in a separate spreadsheet tab in the workbook called, RSLTS, or something similar. The original text should not be modified.

SAMSUNG

NOTE: The device generates vCard version 2.1. This needs to be converted to v3.0 syntax.

BEGIN:VCARD
VERSION:2.1
N:Fitness;24;Hour;;
FN:24 Hour Fitness
TEL;:5559359000
TEL;TYPE=Local-Number:TEL;:5559359064
TEL;X-Membership:8004326348
TEL;HOME:6509359064
TEL;X-Membership:8004326348
END:VCARD

Convert to this...

BEGIN:VCARD
VERSION:3.0
FN:24 Hour Fitness
N:Fitness;24;Hour;;
TEL;TYPE=X-DEFAULT:5559359000
TEL;TYPE=X-Local-Number:5559359064
TEL;TYPE=X-Membership:8004326348
END:VCARD

GMAIL

(The above version from Samsung export, even when adjusted by hand for v3.0, did not import correctly into gmail. So I fixed by hand the fields in gmail and exported to get the following. This syntax, however, does not import correctly into the phone. The phone number imports, but the custom type description does not.)

BEGIN:VCARD
VERSION:3.0
FN:24 Hour Fitness
N:Fitness;24;Hour;;
item1.TEL:5559359000
item1.X-ABLabel:DEFAULT
item2.TEL:5559359064
item2.X-ABLabel:Local-Number
item3.TEL:8004326348
item3.X-ABLabel:Membership
END:VCARD

CONDITIONS

NOTE: Some of this sounds much more complicated, at first glance, than it really is. I can provide more documentation and discussion if needed.

The contacts.vcf file will contain one or more individual vcards. Each vcard is defined between a BEGIN:VCARD and an END:VCARD statement. Each vcard may contain zero or more custom field types. By the term "field type" I am referring to an expression such as "TEL;TYPE=" or "item2.TEL:". Some vcards will not contain any custom field types and do not need to be converted.

It may be best to write one script for each type of conversion. One called, VCF_SamsungToGmail, and another called, VCF_GmailToSamsung. The one for Samsung will need to convert first to vCard v3.0.

Statements that need conversion will include different fields.
TEL and ADR are two of the most important fields.
TEL;TYPE=WORK:5558882222 is an example of a statement that DOES NOT need to be converted. Notice there is no "X-" string (in the Samsung VCF file). The Gmail VCF file will contain the identical statement (there will be NO "item1.TEL" syntax). Standard types are common to both syntaxes, such as HOME and WORK. I can provide more detail as needed.
Avatar of aikimark
aikimark
Flag of United States of America image


TEL;X-Membership:8004326348
TEL;HOME:6509359064
TEL;X-Membership:8004326348

What happened to the home entry in the output?
Are there really duplicate entries, such as membership?
Avatar of Jerry L

ASKER

Duplicates can be handled by Gmail which has a utility to merge duplicates. However, before I can import into Gmail, the duplicates must be treated like any other statement.

I'm compiling a sample vCard file for testing.
Avatar of Jerry L

ASKER

Here is a link to some notes I took while researching: http://www.jerryleventer.com/notes-on-vcard-file-specifications/

I have attached three vCard sample files to use for testing.

1. Sample-001a_Samsung-Export_RAW.vcf
This is what my phone exports from the contact list.

2. Sample-001b_Samsung-Export_FIXED.vcf
This is what the first Excel conversion script should create when run on 001a.

3. Sample-001c_Samsung-Export_Converted-to-Gmail-Format .vcf
This is what the second Excel conversion script should create when run on sample 001b.

For the first run, it may be easier to omit the vCard statements that are repeated, and add them later.

My ability to write VBScript from scratch is limited; however, if you write the subroutines without any fancy obfuscation, and with clear and meaningful variable names, I will be able to understand the code and make changes if and when I need to, such as adding a handler for a Property or Type Value not included in the sample files.

NOTE: I had to change the file extension from .vcf to .txt in order to attach the files to this post.

Sample-001a-Samsung-Export-RAW.txt
Sample-001b-Samsung-Export-FIXED.txt
Sample-001c-Samsung-Export-Converted-to-
Why an Excel script?
Avatar of Jerry L

ASKER

Would you rather write it in Perl? It's been a while, but I much more experienced with that than VB. I suppose PHP would also work, but having a Perl executable would be more convenient than having to set up Apache or upload to a website.

By the way, link to my notes has been changed to: http://www.jerryleventer.com/notes-on-vcard-vcf-file-syntax-specifications/
Where did the DEFAULT go?
Your comment makes it seem as though you have already written something to do your data transformation.  Is this the case or did you do those sample transformations manually?
I asked about Excel because I wanted to know if there was something you specifically needed to do to the data in a worksheet setting.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jerry L

ASKER

ANSWER TO YOUR QUESTION
I performed those transformations manually, yes. And I see your point about using Excel. Reading the file directly and creating a new file for output works just fine: No need to paste the vCard data into a spreadsheet. Also, DEFAULT is not needed.

NEXT STEP NEEDED
I have imported the transformed data to Gmail, merged duplicates, and exported to a file. I have attached that new file to this post.

Sample-001d_Gmail-Export.txt is in Gmail syntax and now needs to be transformed to Samsung format.

Sample-001e_Gmail-Export_Transformed.txt shows what the result of the conversion script should look like. I performed this conversion by hand.

Sample-001d-Gmail-Export.txt
Sample-001e-Gmail-Export-Transformed.txt
What is the purpose of gmail in this problem?
Please describe the steps you took to do your manual conversion.
Avatar of Jerry L

ASKER

WHY USE GMAIL

If you recall in my prior posted question, Q_28439303, I was looking for a way to convert from VCF to CSV and back so I could edit the data directly in Excel. Gmail provides this functionality (as aikimark pointed out in that post).

Another fortuitous benefit of Gmail is that it performs a reliable merging of duplicate vCard elements.
thank you for that explanation.

Now, please detail the data transformations required.
If you just need to consolidate the item# lines, then use the following regex pattern
(item\d).(.*?):(.*?)\r\n\1(?:.X-ABLabel:)(.*?)\r\n

Open in new window

and do a regex replace() against the entire vcard file text
strData = oRE.Replace(strData, "$2;TYPE=$4:$3")

Open in new window


If you have Notepad++ (or similarly sophisticated text editor), you don't even need to do any coding.
Avatar of Jerry L

ASKER

TRANSFORMATION RULES
Regarding the data transformation rules, I apologize about that. I was going to attach the the graphical diff comparison between before and after files, but neglected to do so.  For reference, I have attached that file now to this post. (Be sure to save it to your hard drive before clicking on it or it will only appear as text in the browser.)

NEXT STEP

I am attempting to modify your original VBA script to work with the regular expressions you provided. I'll let you know if I have any questions about getting it to work.

See: Scooter Software's Beyond Compare (http://www.scootersoftware.com/moreinfo.php?zz=gallery)
Transformation-Graphical-Comparison.html
Changed line 68 of the VBScript code to:
oTS.Close

Open in new window


According to your file comparison output, I was correct in my assumption that the data transformation was combining the item# lines.
Avatar of Jerry L

ASKER

Can you write the second VBA script using the first one (above) as the template and the regex you provided? The Replace command and all the arguments are rather tedious to deconstruct. Thanks.
What have you written so far?
Avatar of Jerry L

ASKER

I have not written anything, yet. Although I did eliminate some variable declarations that were not needed, and I renamed other variables slightly so their meaning would be more clear to me. (Not that yours were bad. On the contrary, I see exactly what your convention is.) There are other lines of code that can be removed, of course.

But, in order for me to rewrite the VBA code, I will have to understand the exchange of values more intimately. I would create a single entry, or perhaps two-entry vCard file. Then I would watch the variables and step through the program.
You only need to open the file, readall the contents into a string variable, and close the input file.
Use the code snippet I supplied earlier to invoke the oRE.Replace() function
Open an output file, write the now altered text, and close the output file.
Avatar of Jerry L

ASKER

Are you saying that the ForEach code does not need to be modified? Or that it should be eliminated entirely? That can't be since you need to step through the vCard statements as stored in strData and strCard.
As far as I can see, there is no need to iterate the matches, just do one replace operation and output the string.
Avatar of Jerry L

ASKER

Here's the code I came up with, and a sample vCard. However, the output file is empty.

It looks like this statement, strData = Input(LOF(1), #1), reads the entire file into the variable. Then, when you execute the Replace command, the regex should be tested against all lines of the file. However, if it is only testing against the FIRST line of the file, then there needs to be a FOR EACH loop as in the first script.

Option Explicit

Sub Transform_Gmail2Samsung_EE_Q_28503959()
    Dim oRE As Object
    Dim strData As String
    
    Const cInFile As String = "C:\Temp\003d-Input_Gmail-Export.vcf"
    Const cOutFile As String = "C:\Temp\003e-Output_Gmail-Export_TRANSFORMED.vcf"
    
    Open cInFile For Input As #1
    strData = Input(LOF(1), #1)
    Close #1
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "((item\d).(.*?):(.*?)\r\n\1(?:.X-ABLabel:)(.*?)\r\n)"
    
    Open cOutFile For Output As #1
    
    If oRE.test(strData) Then
        oRE.Execute (strData)
        strData = oRE.Replace(strData, "$2;TYPE=$4:$3")
        Print #1, strData
    End If
    
    Close #1
    
End Sub

Open in new window

BEGIN:VCARD
VERSION:3.0
FN:Best Buy
N:Best Buy;;;;
item1.TEL:5553211918
item1.X-ABLabel:East-PA
item2.TEL:5559420201
item2.X-ABLabel:Milpitas
item3.TEL:5559030591
item3.X-ABLabel:Mt-View
item4.TEL:5552416040
item4.X-ABLabel:Santana-Row
item5.ADR:;;2460 E Charleston Rd;Mountain View;CA;94043;
item5.X-ABLabel:Mt-View
NOTE:M-F 6am - 3pm\nChat & Phone\nMember 42\nSantana Row\: 280 to Winchester Blvd.
END:VCARD

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jerry L

ASKER

Other than missing an X-, that does it. Thanks for all your help!
good catch.  I assume you took care of that.