Open CSV file via VBA performance

Question

Obviously  this question has been asked many times  The normal procedure    Workbooks Open  ActiveWorkbook Path  amp    Test csv    won t parse the CSV correctly  having many rows in one cell   Thanks to Lernkurve  I can use his function to get it right  Opening 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 right parsing

User · Answer

I have the same issue  I m not able to open a CSV file in Excel  I ve found a solution that worked for me in this question Opening a file in excel via Workbooks OpenText  That question helped me to figure out a code that works for me  The code looks more or less like this   Private Sub OpenCSVFile filename as String      Dim datasourceFilename As String     Dim currentPath As String      datasourceFilename        amp  filename  amp    csv      currentPath   ActiveWorkbook Path      Workbooks OpenText Filename  currentPath  amp  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 least  it helped me to know about lots of parameters I can use with Workbooks OpenText method

User · Answer

This function reads a CSV file of 15MB and copies its content into a sheet in about 3 secs  What is probably taking a lot of time in your code is the fact that you copy data cell by cell instead of putting the whole content at once   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   lt  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  lt  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  lt  gt     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

User · Answer

This may help you  also it depends how your CSV file is formated    Open your excel sheet  amp  go to menu Data   Import External Data   Import Data  Choose your CSV file  Original data type  choose Fixed width  then Next  It will autmaticall delimit your columns  then  you may check the splitted columns in Data preview panel  Then Finish  amp  see    Note  you may also go with Delimited as Original data type   In that case  you need to key-in your delimiting character   HTH

User · Answer

Workbooks Open does work too   Workbooks Open ActiveWorkbook Path  amp    Temp csv   Local  True  this works is needed because i use Excel in germany and excel does use     to separate  csv by default because i use an english installation of windows  even if you use the code below excel forces the     separator   Workbooks Open ActiveWorkbook Path  amp    Test csv       6               and Workbooks Open ActiveWorkbook Path  amp    Temp csv       4  variants of this do not work     why do they even have the delimiter parameter if it is blocked by the Local parameter    this makes no sense at all  but now it works

User · Answer

Sometimes all the solutions with Workbooks open is not working no matter how many parameters are set  For me  the fastest solution was to change the List separator in Region  amp  language settings  Region window   Additional settings      List separator   If csv is not opening in proper way You probly have set      as a list separator  Just change it to      and everything is solved  Just the easiest way when  everything is against You   P

User · Answer

Have you tried the import text function

[excel] Open CSV file via VBA (performance)

Examples related to excel

Examples related to vba