Excel VBA Import CSV into Excel using Workbooks.OpenText Method

This Excel VBA tutorial explains how to import CSV into Excel automatically using Workbooks.OpenText Method. You may select different delimiters such as Tab, semicolon, comma, space.

You may also want to read:

Import Chinese CSV to Excel

Excel VBA convert CSV to Excel

Excel VBA Import CSV into Excel using Workbooks.OpenText Method

In Excel workbook, you can manually import a CSV file into Excel (Data > From Text / CSV). However, you have to select some options in advance such as delimiter. In order to import CSV into Excel automatically, you may use Workbooks.Open Text Method.

Syntax of Workbooks.Open Text Method

Workbooks.OpenText(FileName, Origin , StartRow , DataType , TextQualifier , ConsecutiveDelimiter , Tab , Semicolon , Comma , Space , Other , OtherChar , FieldInfo , TextVisualLayout , DecimalSeparator , ThousandsSeparator , TrailingMinusNumbers , Local)

Name	Required/Optional	Data type	Description
FileName	Required	String	Specifies the file name of the text file to be opened and parsed.
Origin	Optional	Variant	Specifies the origin of the text file. Can be one of the following xlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. Additionally, this could be an integer representing the code page number of the desired code page. For example, “1256” would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.
StartRow	Optional	Variant	The row number at which to start parsing text. The default value is 1.
DataType	Optional	Variant	Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file. Name Value Description xlDelimited 1 Default. Indicates that the file is delimited by delimiter characters. xlFixedWidth 2 Indicates that the data in the file is arranged in columns of fixed widths.
TextQualifier	Optional	Variant	Name Value Description xlTextQualifierDoubleQuote 1 Double quotation mark (“). xlTextQualifierNone -4142 No delimiter. xlTextQualifierSingleQuote 2 Single quotation mark (‘).
ConsecutiveDelimiter	Optional	Variant	True to have consecutive delimiters considered one delimiter. The default is False.
Tab	Optional	Variant	True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.
Semicolon	Optional	Variant	True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.
Comma	Optional	Variant	True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.
Space	Optional	Variant	True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.
Other	Optional	Variant	True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.
OtherChar	Optional	Variant	(required if Other is True). Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.
FieldInfo	Optional	Variant	An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed. Name Value Description xlDMYFormat 4 DMY date format. xlDYMFormat 7 DYM date format. xlEMDFormat 10 EMD date format. xlGeneralFormat 1 General. xlMDYFormat 3 MDY date format. xlMYDFormat 6 MYD date format. xlSkipColumn 9 Column is not parsed. xlTextFormat 2 Text. xlYDMFormat 8 YDM date format. xlYMDFormat 5 YMD date format.
TextVisualLayout	Optional	Variant	The visual layout of the text.
DecimalSeparator	Optional	Variant	The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.
ThousandsSeparator	Optional	Variant	The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.
TrailingMinusNumbers	Optional	Variant	Specify True if numbers with a minus character at the end should be treated as negative numbers. If False or omitted, numbers with a minus character at the end are treated as text.
Local	Optional	Variant	Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

 

Example – Import CSV into Excel using Workbooks.OpenText Method

Suppose we have a staff list as below in csv file, in which the delimiter is comma with double quotation around text that contains comma (job title). Uur goal is import CSV into Excel and delimit the data automatically.

In the VBA code, for the case of a mix of double quotation and no double  quotation, we can skip the TextQualifier argument. We only have to identify the file path and delimiter as below.

Public Sub OpenCsvFile()
  .OpenText Filename:="C:\Users\WYMAN\Desktop\staff list.csv", DataType:=xlDelimited, comma:=True
 End Sub

Create a new workbook, press ALT+F11 to insert the above procedure and then execute the procedure. The CSV file will open in Excel and the data is delimited properly.

 

Note that OpenText Method only opens the CSV in Excel but it is not importing the data into the current workbook.

To do so, we can add some codes to copy the worksheet over to the current workboook .

Public Sub OpenCsvFile()
    Application.ScreenUpdating = False  
    Workbooks.OpenText Filename:="C:\Users\WYMAN\Desktop\staff list.csv", DataType:=xlDelimited, comma:=True 
    With ActiveWorkbook
        .ActiveSheet.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
        .Close
    End With   
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

Execute the above procedure, now the delimited csv is added to the current workbook in a new worksheet.

 

Outbound References

https://docs.microsoft.com/zh-tw/office/vba/api/Excel.Workbooks.OpenText