How to Open VCF File in Excel (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful program. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we need to import data into Excel worksheets from VCF files. But, we can’t open the VCF files directly in Excel. We have to go through some steps before the VCF file information appears in the Excel worksheet. This article will show you 2 simple methods to open VCF file in Excel.


How to Open VCF File in Excel: 2 Simple Methods

VCF is the short form for Virtual Contact File. People also know it as vCard. VCF files are virtual storage of business contact information. The file format is text-based and can store Name, Address, Phone Number, Email info, etc. However, Excel can’t open them directly as it lacks built-in support for .vcf formats. In this article, we’ll show you how you can open VCF files in Excel by following some procedures. To illustrate, we’ll use a sample dataset as an example. For instance, the below dataset is in a VCF file. The dataset contains info about names, addresses, phone numbers, and emails. Now, go through the methods for performing the operation.

how to open vcf file in excel


1. Open VCF File in Excel Through Text Import Wizard

In our first method, we’ll open the VCF file through the Text Import Wizard. The Text Import Wizard is a feature in Excel that helps to import data from text file formats into worksheets. This feature analyzes the text file first. We can also specify the delimiter we have used in the text files so that we can get the data in excel in an accurate way. As the VCF files are text-based, we can definitely use the Text Import Wizard for opening VCF files in Excel. Therefore, follow the steps below to perform the task.

STEPS:

  • Firstly, we’ll open the .vcf file using excel.
  • For this reason, click File > Open.
  • As a result, the Open dialog box will pop out.
  • Select the desired VCF file.
  • Then, press Open.

Open VCF File in Excel Through Text Import Wizard

  • Consequently, the Text Import Wizard will appear.
  • In step 1, select Delimited and press Next.

  • Subsequently, in step 2, choose your delimiter.
  • Here, we choose Tab and colon (:) by checking Other.
  • Afterward, click Next.

  • In step 3, you can change your column data formats.
  • Lastly, click Finish.

  • Hence, the Excel worksheet will be populated with the VCF file data.
  • See the below picture which is our outcome.

Text Import Wizard output of VCF file in excel


2. Use Power Query Editor to Import VCF File in Excel

Moreover, we can use the Excel power query editor to import VCF file data. The Power Query editor is useful when importing data into Excel worksheets. We can also transform various data into our desired format before loading them into worksheets. So, learn the following steps to carry out the operation.

STEPS:

  • First, go to the Data tab.
  • Then, in the Get & Transform Data section, click From Text/CSV.

Use Power Query Editor to Import VCF File in Excel

  • As a result, the Import Data dialog box will pop out.
  • Select the desired VCF file and press Import.

  • Consequently, another dialog box will appear containing the file.
  • Look at the below figure to understand it clearly.
  • After that, double-click the file to open it.

  • Notice that all the values are in a single column which is not desired.
  • To modify it, select the entire column.
  • Next, under the Transform tab, click Text Column > Split Column > By Delimiter.

  • A new dialog box will emerge.
  • Afterward, choose Tab as the delimiter.
  • You can choose your own.
  • Press OK.

  • Lastly, go to the Home tab and click Close & Load.

  • Thus, you’ll see the VCF data in the Excel worksheet.
  • In this way, we can easily open the VCF files in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Open VCF file in Excel using the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

<< Go Back to Import vCard to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

2 Comments
  1. I am able to import vcf file into excel as shown above. I want each record to be a row , sothat the whole vcf file becomes an excel worksheet which I can modify column by column. How can that be done ?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Jul 2, 2023 at 3:58 PM

      Hello ANAND,

      Thanks for reaching out and posting your interesting issue. The requirement you mentioned can be done with the help of an Excel VBA code. The code contains two sub-procedures named ImportVCFRecordWise and ExtractData. All you have to do is to run the ImportVCFRecordWise procedure to achieve your goal.

      Excel VBA Code:

      
      Sub ImportVCFRecordWise()
      
          Dim wb As Workbook
          Dim ws, wsTemp As Worksheet
          Dim filePath As String
          Dim fileDialog As fileDialog
          
          Set wsTemp = Sheets.Add(After:=ActiveSheet)
          wsTemp.Name = "Temp"
          
          Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
          
          fileDialog.Filters.Clear
          fileDialog.Filters.Add "VCF Files", "*.vcf"
          
          If fileDialog.Show = -1 Then
              filePath = fileDialog.SelectedItems(1)
              Set wb = Workbooks.Open(Filename:=filePath)
              With wb.Sheets(1)
                  .UsedRange.Copy wsTemp.Range("A1")
              End With
              wb.Close SaveChanges:=False
          End If
          
          Call ExtractData
          
          For Each ws In ThisWorkbook.Sheets
              If ws.Name = "Temp" Then
                  Application.DisplayAlerts = False
                  ws.Delete
                  Application.DisplayAlerts = True
                  Exit For
              End If
          Next ws
          
          Set fileDialog = Nothing
      
      End Sub
      
      Sub ExtractData()
      
          Dim ws As Worksheet
          Dim outputRow As Long
          Dim lastRow As Long
          Dim outputSheet As Worksheet
          
          Set ws = ThisWorkbook.Sheets("Temp")
          
          lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
          
          Set outputSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
          
          outputSheet.Name = "Output"
          outputSheet.Range("A1").Value = ws.Range("A2").Value
          outputSheet.Range("B1").Value = ws.Range("A3").Value
          outputSheet.Range("C1").Value = ws.Range("A4").Value
          outputSheet.Range("D1").Value = ws.Range("A5").Value
          outputSheet.Range("E1").Value = ws.Range("A6").Value
          outputSheet.Range("F1").Value = ws.Range("A7").Value
          outputSheet.Range("G1").Value = ws.Range("A8").Value
          
          outputRow = 2
          
          Dim vcardData As Variant
          vcardData = ws.Range("A1:B" & lastRow).Value
          
          Dim i As Long
          For i = 1 To UBound(vcardData, 1)
              If vcardData(i, 1) = "BEGIN" And vcardData(i, 2) = "VCARD" Then
                  outputSheet.Range("A" & outputRow).Value = vcardData(i + 1, 2)
                  outputSheet.Range("B" & outputRow).Value = vcardData(i + 2, 2)
                  outputSheet.Range("C" & outputRow).Value = vcardData(i + 3, 2)
                  outputSheet.Range("D" & outputRow).Value = vcardData(i + 4, 2)
                  outputSheet.Range("E" & outputRow).Value = vcardData(i + 5, 2)
                  outputSheet.Range("F" & outputRow).Value = vcardData(i + 6, 2)
                  outputSheet.Range("G" & outputRow).Value = vcardData(i + 7, 2)
                  outputRow = outputRow + 1
              End If
          Next i
          
          outputSheet.UsedRange.EntireColumn.AutoFit
      
      End Sub
      

      Solution Workbook: Download the Workbook used to solve the issue.
      DOWNLOAD WORKBOOK

      VCF File: The VCF file contains some raw data. I am using the data described in this article to be more specific.

      VCF File Contents

      Steps:

      Press Alt+F11 >> insert the mentioned code >> press F5 or click on Run.

      VBA Editor window

      Choose the intended VCF file >> click on OK.

      Select VCF file using file dialog

      As a result, we will see an output like the following one.

      Final Output importing VCF file

      Things to Keep in Mind:

    • 1. You have to run only the ImportVCFRecordWise procedure.
    • 2. You must delete the Output sheet if it exists before running the code.
    • This concept will assist you in reaching your goal. Good luck!

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo