• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

shahin

Active Member
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.
 
Last edited:

shahin

Active Member
@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

Excel Ninja
Staff member
The code below takes this CSV file
upload_2018-3-23_8-39-54.png
and makes
upload_2018-3-23_8-40-14.png

Try this code:

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
 
Last edited:

Marc L

Excel Ninja
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

Active Member
@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

Excel Ninja
Staff member
refer to the CSVFileLoc = below

Code:
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

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

Code:
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
 
Top