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.

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”.



VBA code

Import:

Option Explicit



Sub ImportCSV()



'------------------------------------------

'Imports a CSV file to an excel worksheet.

'By Christos Samaras

'https://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



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.

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

'https://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



How to use it

The short video below demonstrates the two macros in use.