[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 879
  • Last Modified:

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 http://www.experts-exchange.com/Hardware/Personal_Electronics/Cell_Phones/Q_28439303.html#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.
0
WizeOwl
Asked:
WizeOwl
  • 16
  • 11
2 Solutions
 
aikimarkCommented:

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?
0
 
WizeOwlAuthor Commented:
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.
0
 
WizeOwlAuthor Commented:
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-
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
aikimarkCommented:
Why an Excel script?
0
 
WizeOwlAuthor Commented:
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/
0
 
aikimarkCommented:
Where did the DEFAULT go?
0
 
aikimarkCommented:
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?
0
 
aikimarkCommented:
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.
0
 
aikimarkCommented:
In the future, when posting questions, it is easier for the experts if you include the rules for your transformations.  Otherwise, the experts have to infer the rules.

Here is the VBA code that does the data transforms.
Option Explicit

Sub Q_28503959()
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim lngSM As Long
    Dim oRE2 As Object
    Dim oMatches2 As Object
    Dim oM2 As Object
    Dim oSM2 As Object
    Dim strData As String
    Dim strCard As String
    Dim lngItemNum As Long
    
    Const cInFile As String = "C:\Users\Mark\Downloads\Sample-001a-Samsung-Export-RAW.txt"
    Const cOutFile As String = "C:\Users\Mark\Downloads\Sample-001a-Samsung-Export-Transformed.txt"
    
    Open cInFile For Input As #1
    strData = Input(LOF(1), #1)
    Close #1
    'change the version
    strData = Replace(strData, "VERSION:2.1", "VERSION:3.0")
    'change the preferred email
    strData = Replace(strData, "EMAIL;PREF:", "EMAIL;PREF=1:")
    
    'format all the intrinsic telephone items
    Set oRE2 = CreateObject("vbscript.regexp")
    oRE2.Global = True
    oRE2.Pattern = "(TEL;|EMAIL;)(CELL|HOME|WORK|)(:.*?)"
    strData = oRE2.Replace(strData, "$1TYPE=$2$3")
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(BEGIN:VCARD(?:\s|\S)*?END:VCARD)"
    
    'format all the X- items
    Set oRE2 = CreateObject("vbscript.regexp")
    oRE2.Global = True
    oRE2.Pattern = "(?:(TEL|ADR|URL)(;X-)(.*?)(:.*?))\r\n"
    
    Open cOutFile For Output As #1

    If oRE.test(strData) Then
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            strCard = oM
            lngItemNum = 0
            If oRE2.test(strCard) Then
                Set oMatches2 = oRE2.Execute(strCard)
                For Each oM2 In oMatches2
                    lngItemNum = lngItemNum + 1
                    With oM2
                        strCard = Replace(strCard, oM2, "ITEM" & lngItemNum & "." & _
                                        .submatches(0) & .submatches(3) & vbCrLf & _
                                        "ITEM" & lngItemNum & "." & "X-ABLabel:" & _
                                        Replace(.submatches(2), " ", "-") & vbCrLf, , 1)
                    End With
                Next
            End If
            Print #1, strCard
        Next
        Close
    End If
    
End Sub

Open in new window


And here is the VBScript version of the transforming code.
Option Explicit


    Dim oRE
    Dim oMatches
    Dim oM
    Dim oSM
    Dim lngSM
    Dim oRE2
    Dim oMatches2
    Dim oM2
    Dim oSM2
    Dim strData
    Dim strCard
    Dim lngItemNum
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim oFS, oTS
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Const cInFile = "C:\Users\Mark\Downloads\Sample-001a-Samsung-Export-RAW.txt"
    Const cOutFile = "C:\Users\Mark\Downloads\Sample-001a-Samsung-Export-Transformed.txt"
    
    Set oTS = oFS.OpenTextFile(cInFile, ForReading,TristateFalse)
    strData = oTS.ReadAll
    oTS.Close

    'change the version
    strData = Replace(strData, "VERSION:2.1", "VERSION:3.0")
    'change the preferred email
    strData = Replace(strData, "EMAIL;PREF:", "EMAIL;PREF=1:")
    
    'format all the intrinsic telephone items
    Set oRE2 = CreateObject("vbscript.regexp")
    oRE2.Global = True
    oRE2.Pattern = "(TEL;|EMAIL;)(CELL|HOME|WORK|)(:.*?)"
    strData = oRE2.Replace(strData, "$1TYPE=$2$3")
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(BEGIN:VCARD(?:\s|\S)*?END:VCARD)"
    
    'format all the X- items
    Set oRE2 = CreateObject("vbscript.regexp")
    oRE2.Global = True
    oRE2.Pattern = "(?:(TEL|ADR|URL)(;X-)(.*?)(:.*?))\r\n"
    
    Set oTS = oFS.OpenTextFile(cOutFile, ForWriting, TristateFalse)

    If oRE.test(strData) Then
        Set oMatches = oRE.Execute(strData)
        For Each oM In oMatches
            strCard = oM
            lngItemNum = 0
            If oRE2.test(strCard) Then
                Set oMatches2 = oRE2.Execute(strCard)
                For Each oM2 In oMatches2
                    lngItemNum = lngItemNum + 1
                    With oM2
                        strCard = Replace(strCard, oM2, "ITEM" & lngItemNum & "." & _
                                        .submatches(0) & .submatches(3) & vbCrLf & _
                                        "ITEM" & lngItemNum & "." & "X-ABLabel:" & _
                                        Replace(.submatches(2), " ", "-") & vbCrLf, , 1)
                    End With
                Next
            End If
            oTS.Write strCard
        Next
        oTS.Close
    End If
    

Open in new window

0
 
WizeOwlAuthor Commented:
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
0
 
aikimarkCommented:
What is the purpose of gmail in this problem?
0
 
aikimarkCommented:
Please describe the steps you took to do your manual conversion.
0
 
WizeOwlAuthor Commented:
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.
0
 
aikimarkCommented:
thank you for that explanation.

Now, please detail the data transformations required.
0
 
aikimarkCommented:
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.
0
 
WizeOwlAuthor Commented:
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
0
 
aikimarkCommented:
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.
0
 
WizeOwlAuthor Commented:
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.
0
 
aikimarkCommented:
What have you written so far?
0
 
WizeOwlAuthor Commented:
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.
0
 
aikimarkCommented:
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.
0
 
WizeOwlAuthor Commented:
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.
0
 
aikimarkCommented:
As far as I can see, there is no need to iterate the matches, just do one replace operation and output the string.
0
 
WizeOwlAuthor Commented:
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

0
 
aikimarkCommented:
Really close.  The pattern should be:
(item\d).(.*?):(.*?\r\n)\1.X-ABLabel:(.*?)\r\n

Open in new window


Almost right on the code, too.  There was an extra Execute() method you didn't need
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
        strData = oRE.Replace(strData, "$2;TYPE=$4:$3")
        Print #1, strData
    End If
    
    Close #1
    
End Sub

Open in new window

Which produces the following output, using your sample input.
BEGIN:VCARD
VERSION:3.0
FN:Best Buy
N:Best Buy;;;;
TEL;TYPE=East-PA:5553211918
TEL;TYPE=Milpitas:5559420201
TEL;TYPE=Mt-View:5559030591
TEL;TYPE=Santana-Row:5552416040
ADR;TYPE=Mt-View:;;2460 E Charleston Rd;Mountain View;CA;94043;
NOTE:M-F 6am - 3pm\nChat & Phone\nMember 42\nSantana Row\: 280 to Winchester Blvd.
END:VCARD

Open in new window

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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 16
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now