Jerry L
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...
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; :555935906 4
TEL;X-Membership:800432634 8
TEL;HOME:6509359064
TEL;X-Membership:800432634 8
END:VCARD
Convert to this...
BEGIN:VCARD
VERSION:3.0
FN:24 Hour Fitness
N:Fitness;24;Hour;;
TEL;TYPE=X-DEFAULT:5559359 000
TEL;TYPE=X-Local-Number:55 59359064
TEL;TYPE=X-Membership:8004 326348
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-Numb er
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.
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
and convert it to this...
item2.TEL:8004326348
item2.X-ABLabel:Membership
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;
TEL;X-Membership:800432634
TEL;HOME:6509359064
TEL;X-Membership:800432634
END:VCARD
Convert to this...
BEGIN:VCARD
VERSION:3.0
FN:24 Hour Fitness
N:Fitness;24;Hour;;
TEL;TYPE=X-DEFAULT:5559359
TEL;TYPE=X-Local-Number:55
TEL;TYPE=X-Membership:8004
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-Numb
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.
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.
I'm compiling a sample vCard file for testing.
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-
I have attached three vCard sample files to use for testing.
1. Sample-001a_Samsung-Export
This is what my phone exports from the contact list.
2. Sample-001b_Samsung-Export
This is what the first Excel conversion script should create when run on 001a.
3. Sample-001c_Samsung-Export
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?
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/
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.t xt is in Gmail syntax and now needs to be transformed to Samsung format.
Sample-001e_Gmail-Export_T ransformed .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
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.t
Sample-001e_Gmail-Export_T
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.
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.
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.
Now, please detail the data transformations required.
If you just need to consolidate the item# lines, then use the following regex pattern
If you have Notepad++ (or similarly sophisticated text editor), you don't even need to do any coding.
(item\d).(.*?):(.*?)\r\n\1(?:.X-ABLabel:)(.*?)\r\n
and do a regex replace() against the entire vcard file textstrData = oRE.Replace(strData, "$2;TYPE=$4:$3")
If you have Notepad++ (or similarly sophisticated text editor), you don't even need to do any coding.
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
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:
According to your file comparison output, I was correct in my assumption that the data transformation was combining the item# lines.
oTS.Close
According to your file comparison output, I was correct in my assumption that the data transformation was combining the item# lines.
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?
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.
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.
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.
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.
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.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Other than missing an X-, that does it. Thanks for all your help!
good catch. I assume you took care of that.
What happened to the home entry in the output?
Are there really duplicate entries, such as membership?