Import & Export CSV Files ~ My Engineering World

About 10 days ago a blog reader asked me to write a VBA code to manipulate a CSV file. His problem solved, but, unintentionally, he pushed me to complete an idea that I had some months ago: to write some VBA code for importing and exporting CSV files. CSV stands for “Comma Separated Values” and copying from Wikipedia: “A comma-separated values (CSV) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields”.

With this VBA code you will be able to import a CSV file to a worksheet and you can export a selected range of cells to a CSV file. The code has the advantage of working with various delimited characters. So, apart from comma (“,”) you can use semicolon (“;”), hyphen (“-“), pipe character (“|”) etc.

VBA code

Import: Use the following lines of code in order to import a CSV file to a worksheet of your choice. A file dialog will help you find the CSV file that must be imported.

Option Explicit

Sub ImportCSV()
    
    '------------------------------------------
    'Imports a CSV file to an excel worksheet.
    'By Christos Samaras
    'http://www.myengineeringworld.net
    '-----------------------------------------
    
    Dim shtImport   As Worksheet
    Dim strDelChar  As String
    Dim strFileName As String
    Dim lRow        As Long
    Dim lCol        As Long
    Dim strText     As String
    Dim strChar     As String * 1
    Dim vntData     As Variant
    Dim lCharCount  As Long
         
    'Change the name "Import" according to your sheet name.
    Set shtImport = Sheets("Import")
    
    'Change the delimited character "," according to your own needs.
    'NOTE: Due to the code structure only one character is accepted
    '      for delimited character (example "%", "-", "|" etc.).
    strDelChar = ","
    
    'Show the file dialog and select a CSV file.
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Select a CSV file!"
        .Filters.Clear
        .Filters.Add "Comma Separated Values", "*.csv"
        .Show
            If .SelectedItems.Count = 0 Then
                MsgBox "You did't select a text file!", vbExclamation, "Canceled"
                Exit Sub
            Else
                strFileName = .SelectedItems(1)
            End If
    End With
    
    Application.ScreenUpdating = False
    
    'Check if the selected file is CSV file.
    If UCase(Right(strFileName, 3)) <> "CSV" Then
        MsgBox "The file you select is not a CSV file!", vbCritical, "Error!"
        Exit Sub
    End If
    
    'Open the CSV file.
    On Error Resume Next
    Open strFileName For Input As #1
    
    'Check if the file was opened.
    If Err <> 0 Then
        MsgBox "File not found: " & strFileName, vbCritical, "Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Initializing the variables.
    lRow = 0
    lCol = 0
    strText = ""
    
    'Activate the A1 cell on Import sheet.
    shtImport.Activate
    Range("A1").Activate
    
    'Loop through all lines of CSV file and import the data values to the active sheet.
    Do Until EOF(1)
    
        'Pass the line data to a variable.
        Line Input #1, vntData
        
        'Loop through all characters of the variable.
        For lCharCount = 1 To Len(vntData)
            
            'Examine each character separately.
            strChar = Mid(vntData, lCharCount, 1)
            
            'If reach the delimited character write the value to a cell.
            If strChar = strDelChar Then
                ActiveCell.Offset(lRow, lCol) = strText
                lCol = lCol + 1
                strText = ""
            
            'If reach the end of the line write the (last) value to a cell and
            'then reset the strText variable.
            ElseIf lCharCount = Len(vntData) Then
                If strChar <> Chr(34) Then strText = strText & strChar
                ActiveCell.Offset(lRow, lCol) = strText
                strText = ""
                
            'In any other case concatenate the character with the strText variable.
            ElseIf strChar <> Chr(34) Then
                strText = strText & strChar
            End If
        Next lCharCount
        
        'Reset the variables for the next line of data.
        lCol = 0
        lRow = lRow + 1
        
    Loop
    
    'Close the CSV file.
    Close #1
    
    Application.ScreenUpdating = True
    
    'Inform the user about the process.
    MsgBox "The file " & strFileName & " was successfully imported on sheet " & _
    shtImport.Name & "!", vbInformation, "Done"
    
End Sub

Export: Select a range of cells and the following code will create a CSV file with the selected values. Note, that the exported CSV file will be created in the same folder with the workbook that contains the code.

Option Explicit

Sub ExportToCSV()
    
    '----------------------------------------
    'Exports a selected range to a CSV file.
    'By Christos Samaras
    'http://www.myengineeringworld.net
    '----------------------------------------
        
    Dim strFileName     As String
    Dim strDelChar      As String
    Dim rngExportData   As Range
    Dim lNumRows        As Long
    Dim lNumCols        As Long
    Dim lRow            As Long
    Dim lCol            As Long
    Dim vntTemp         As Variant
    Dim vntData         As Variant
    
    'Change the name "Exported Results.csv" according to your own needs.
    'You can also directly set the full path of the file, for example:
    'strFileName = "D:\Results.csv"
    strFileName = ThisWorkbook.Path & "\" & "Exported Results.csv"
            
    'Change the delimited character "," according to your own needs.
    'NOTE: Although you can use more than one character as delimited
    '      character(s) (example "-|-", I suggested you to use only one
    '      (example "%", "-", "|" etc.) in order to be able to use the ImportCSV macro.
    strDelChar = ","
    
    'Assign the selected range to a range variable.
    Set rngExportData = Application.Intersect(Selection, ActiveSheet.UsedRange)
    
    'Check if the selected range is empty.
    If rngExportData Is Nothing Then
        MsgBox "The export range is nothing!", vbCritical, "Error"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    'Find the number of rows and columns.
    lNumRows = rngExportData.Rows.Count
    lNumCols = rngExportData.Columns.Count
        
    'Open the CSV file.
    On Error Resume Next
    Open strFileName For Output As #1
    
    'Check if the file was opened.
    If Err <> 0 Then
        MsgBox "File not found: " & strFileName, vbCritical, "Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Loop through all the rows and columns of selected range.
    For lRow = 1 To lNumRows
    
        'Use a temporary variable to created the delimited format.
        vntTemp = Empty
        
        If lNumCols > 1 Then
            
            'There must be no delimited before the first value of each column.
            vntData = rngExportData.Cells(lRow, 1).Value
            vntTemp = vntTemp & vntData
            
            'After the second column the delimited character is used.
            For lCol = 2 To lNumCols
                vntData = rngExportData.Cells(lRow, lCol).Value
                vntTemp = vntTemp & strDelChar & vntData
            Next lCol
        Else
            
            'If there is only one column, no delimited is used.
            vntData = rngExportData.Cells(lRow, 1).Value
            vntTemp = vntTemp & vntData
        End If
        
        'Write the data from each row to  the file.
        Print #1, vntTemp
        
    Next lRow
        
    'Close the CSV file.
    Close #1
    
    Application.ScreenUpdating = True
    
    'Inform the user about the process.
    MsgBox "The file " & strFileName & " was successfully created!", vbInformation, "Done"
    
End Sub