How to read data from a csv file and write the same in the active worksheet using macro?

[shahin]

Is it possible to read data from a csv file using macro and write the data in a sheet within the workbook containing the macro? Let's assume the "*.csv" file contains some names in it's column B, as in "Henry", "Mac","John","Kevin","Bob". I would like to write those names in column "A" in the active worksheet using macro. I know it doesn't sound that good to seek help like this but I really don't have any idea to move on. Thanks in advance for taking a look into it.

 

[Chihiro]

See thread below for one example.
https://chandoo.org/forum/threads/ado-connection-csv-issue-with-mixed-data-type.34097/#post-203000

Or easier, use Macro recorder for process of importing CSV file via Data ->From Text/CSV tool to get the base code.

 

[shahin]

@sir chihiro, This is the first time your provided link seems to have threatened me severely. The way I wish to do looks like a bit difficult to achieve. Trying the macro recorder now. I'm very weak in this recording thing as well.

 

[Hui]

The code below takes this CSV file

and makes


Try this code:

Sub Import_CSV()

'Get the CSV File
Dim CSVFileLoc As Variant
CSVFileLoc = Application.GetOpenFilename()

Dim sWhole As String
Dim v As Variant, x As Variant
Dim rw As Integer

'Import the whole file into an array
Open CSVFileLoc For Input As #1
    sWhole = Input$(LOF(1), 1)
Close #1

'Shift the string to a Variant array
v = Split(sWhole, vbNewLine)
rw = 1

'Loop through the array
For i = LBound(v, 1) To UBound(v, 1)
  'Split the line to a new variant array
  x = Split(v(i), ",")
  'Loop through the new array
  For j = LBound(x, 1) To UBound(x, 1)
    'Debug.Print i, j, x(j)
    Cells(rw, 1).Value = Trim(x(j))
    rw = rw + 1
  Next j
Next i

End Sub

If this doean't help you please post a typical file you want to import

 

[Marc L]

Hi !

Is it possible to read data from a csv file using macro and write the data in a sheet within the workbook

Easy beginner ways : Workbooks.Open & OpenText methods
directly with right parameters or in some special case,
do not forget TextToColumns method !

To directly import to a worksheet : via a QueryTable
(aka Import from file in Excel menu so easy via Macro Recorder)
or read text file lines in variable via Input # or Line Input #
or Input function …

For non Windows encoded characters, use ActiveX ADODB.Stream …
​

 

[shahin]

@sir Hui, your suggested script works like magic. However, the only thing (while the script is running) I needed to do manually is pick the csv file. Is there any way I can get the same action and result without manual intervention? Thanks a lot.

 

[Hui]

refer to the CSVFileLoc = below

Sub Import_CSV()

'Get the CSV File
Dim CSVFileLoc As Variant
CSVFileLoc = "c:\my directory\myfile name.csv" 'Edit this line as appropriate

Dim sWhole As String
Dim v As Variant, x As Variant
Dim rw As Integer

'Import the whole file into an array
Open CSVFileLoc For Input As #1
    sWhole = Input$(LOF(1), 1)
Close #1

'Shift the string to a Variant array
v = Split(sWhole, vbNewLine)
rw = 1

'Loop through the array
For i = LBound(v, 1) To UBound(v, 1)
  'Split the line to a new variant array
x = Split(v(i), ",")
  'Loop through the new array
For j = LBound(x, 1) To UBound(x, 1)
    'Debug.Print i, j, x(j)
  Cells(rw, 1).Value = Trim(x(j))
    rw = rw + 1
  Next j
Next i

End Sub

 

[shahin]

Wow!!! It did the trick. This is what I was looking for. Thanks a lot sir Hui.

 

[shahin]

If i wish to stick to the magnetic portion of your provided code, the below portion should also work.

Sub Import_CSV()
    Dim CSVFileLoc As Variant: CSVFileLoc = "C:\Users\WCS\Desktop\Book1.csv"
    Dim sWhole As String, v As Variant, I&, n&
   
    Open CSVFileLoc For Input As #1
        sWhole = Input$(LOF(1), 1)
    Close #1
   
    v = Split(sWhole, vbNewLine)
    I = UBound(v)
   
    For n = 0 To I - 1
        Cells(n + 1, 1) = v(n)
    Next n
End Sub