Open CSV file via VBA (performance)

Question

Obviously, this question has been asked many times. Normal procedure:

Workbooks.Open(ActiveWorkbook.Path & "\Test.csv")

will not parse CSV correctly (having many rows in one cell)

Thanks to Lernkurve, I can use its function to do it right: Opening a semicolon delimited CSV file

 Sub ImportCSVFile(filepath As String) Dim line As String Dim arrayOfElements Dim linenumber As Integer Dim elementnumber As Integer Dim element As Variant linenumber = 0 elementnumber = 0 Open filepath For Input As #1 ' Open file for input Do While Not EOF(1) ' Loop until end of file linenumber = linenumber + 1 Line Input #1, line arrayOfElements = Split(line, ";") elementnumber = 0 For Each element In arrayOfElements elementnumber = elementnumber + 1 Cells(linenumber, elementnumber).Value = element Next Loop Close #1 ' Close file. End Sub 

This, however, is not fast (I have files with thousands of columns), and my question is:

Is there any native way to open CSV files in Excel with the right parsing?

Answer 1

Workbooks.Open works too.

Workbooks.Open ActiveWorkbook.Path & "\Temp.csv", Local:=True

this works / is required because I use Excel in Germany, and excel uses "," to separate the .csv by default, because I use the English window setting. even if you use the code below excel, separate the delimiter ",".

Workbooks.Open ActiveWorkbook.Path & "\Test.csv", , , 6, , , , , ";"

and Workbooks.Open ActiveWorkbook.Path & "\Temp.csv", , , 4 + options do not work (!)

why do they even have a separator parameter if it is locked by a local parameter ?! it makes no sense. but now it works.

Answer 2

This function reads a 15 MB CSV file and copies its contents to a sheet after about 3 seconds. What probably takes a lot of time in your code is the fact that you are copying a data cell by cell, rather than immediately placing all the content.

 Option Explicit Public Sub test() copyDataFromCsvFileToSheet "C:\temp\test.csv", ",", "Sheet1" End Sub Private Sub copyDataFromCsvFileToSheet(parFileName As String, parDelimiter As String, parSheetName As String) Dim data As Variant data = getDataFromFile(parFileName, parDelimiter) If Not isArrayEmpty(data) Then With Sheets(parSheetName) .Cells.ClearContents .Cells(1, 1).Resize(UBound(data, 1), UBound(data, 2)) = data End With End If End Sub Public Function isArrayEmpty(parArray As Variant) As Boolean 'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase) If IsArray(parArray) = False Then isArrayEmpty = True On Error Resume Next If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False End Function Private Function getDataFromFile(parFileName As String, parDelimiter As String, Optional parExcludeCharacter As String = "") As Variant 'parFileName is supposed to be a delimited file (csv...) 'parDelimiter is the delimiter, "," for example in a comma delimited file 'Returns an empty array if file is empty or can't be opened 'number of columns based on the line with the largest number of columns, not on the first line 'parExcludeCharacter: sometimes csv files have quotes around strings: "XXX" - if parExcludeCharacter = """" then removes the quotes Dim locLinesList() As Variant Dim locData As Variant Dim i As Long Dim j As Long Dim locNumRows As Long Dim locNumCols As Long Dim fso As Variant Dim ts As Variant Const REDIM_STEP = 10000 Set fso = CreateObject("Scripting.FileSystemObject") On Error GoTo error_open_file Set ts = fso.OpenTextFile(parFileName) On Error GoTo unhandled_error 'Counts the number of lines and the largest number of columns ReDim locLinesList(1 To 1) As Variant i = 0 Do While Not ts.AtEndOfStream If i Mod REDIM_STEP = 0 Then ReDim Preserve locLinesList(1 To UBound(locLinesList, 1) + REDIM_STEP) As Variant End If locLinesList(i + 1) = Split(ts.ReadLine, parDelimiter) j = UBound(locLinesList(i + 1), 1) 'number of columns If locNumCols < j Then locNumCols = j i = i + 1 Loop ts.Close locNumRows = i If locNumRows = 0 Then Exit Function 'Empty file ReDim locData(1 To locNumRows, 1 To locNumCols + 1) As Variant 'Copies the file into an array If parExcludeCharacter <> "" Then For i = 1 To locNumRows For j = 0 To UBound(locLinesList(i), 1) If Left(locLinesList(i)(j), 1) = parExcludeCharacter Then If Right(locLinesList(i)(j), 1) = parExcludeCharacter Then locLinesList(i)(j) = Mid(locLinesList(i)(j), 2, Len(locLinesList(i)(j)) - 2) 'If locTempArray = "", Mid returns "" Else locLinesList(i)(j) = Right(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1) End If ElseIf Right(locLinesList(i)(j), 1) = parExcludeCharacter Then locLinesList(i)(j) = Left(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1) End If locData(i, j + 1) = locLinesList(i)(j) Next j Next i Else For i = 1 To locNumRows For j = 0 To UBound(locLinesList(i), 1) locData(i, j + 1) = locLinesList(i)(j) Next j Next i End If getDataFromFile = locData Exit Function error_open_file: 'returns empty variant unhandled_error: 'returns empty variant End Function 

Answer 3

You tried to import a text function .

Answer 4

It may help you, it also depends on how your CSV file is formed.

• Open your Excel worksheet and go to Data > Import External Data > Import Data .
• Select the CSV file.
• Initial data type: select Fixed width , then Next .
• This will automatically split your columns . then you can check the split columns in the Data preview panel.
• Then Finish and see.

Note. You can also switch from Delimited as the source data type. In this case, you need to enter your separator character.

NTN!

Answer 5

I have the same problem, I can not open the CSV file in Excel. I found a solution that worked for me in this question Opening a file in excel via Workbooks.OpenText

This question helped me figure out the code that works for me. The code looks something like this:

 Private Sub OpenCSVFile(filename as String) Dim datasourceFilename As String Dim currentPath As String datasourceFilename = "\" & filename & ".csv" currentPath = ActiveWorkbook.Path Workbooks.OpenText Filename:=currentPath & datasourceFilename, _ Origin:=xlWindows, _ StartRow:=1, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=True, _ Space:=False, _ Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1)), _ DecimalSeparator:=".", _ ThousandsSeparator:=",", _ TrailingMinusNumbers:=True End Sub 

At the very least, it helped me learn about the many options that I can use with the Workbooks.OpenText method.