Search code examples
excelvbacsvfile-read

VBA Copy and paste data skipping the first row


I have 40 different excel data files (data1.csv ~ data40.csv) with the same format. I am trying to generate a graph based on all the data read from those 40 files. My idea is, create a new temporary .csv file (tempOut.csv) into which I copy and paste all data read from each file in order one by one, and in the end generate a graph using the data in that temporary file.

The problem is that, each file has the title row like (ID, name, val1, val2,,,,). When I copy data from each file, I need to remove this row, or skip this row at all and copy, and then paste it in the temporary .csv file, EXCEPT FOR the first file.

How could this be achieved using the code I wrote? Here is my code:

Dim thisFile As String   'input file name
Dim outFile As String    'output file name
Dim dataRead As String   'containing data copied and pasted
outFile = "tempOut.csv"
Open outFile For Output As #1
For i = 1 To 40
  thisFile = "C:\datafolder\data" + CStr(i) + ".csv"
  Open thisFile For Input As #2
  Do Until EOF(2)         'read until the end of the file
    Line Input #2, dataRead
    Print #1, dataRead
  Loop
  Close #2
Next i
Close #1

This code does create a new file, and copy and paste all data from each file in order. However, each time, it copy and pastes the title row too. I have added if statement such that when i = 1, it reads everything. But I am quite not sure how to copy skipping the first row, from the 2nd file read till the last file.

Can anyone please help me with this? Thank you in advance.

Add: For example, the data1.csv looks like
|ID|Name|Val1|Val2| ...
|0 |aaaa| 1 | 2 | ...
|1 |bbbb| 3 | 4 | ...
|2 |cccc| 5 | 6 | ...

and data2.csv looks like
|ID|Name|Val1|Val2| ...
|3 |dddd| 7 | 8 | ...
|4 |eeee| 9 | 9 | ...
|5 |ffff| 7 | 5 | ...

Then, the combined tempOut.csv should look like
|ID|Name|Val1|Val2| ...
|0 |aaaa| 1 | 2 | ...
|1 |bbbb| 3 | 4 | ...
|2 |cccc| 5 | 6 | ...
|3 |dddd| 7 | 8 | ...
|4 |eeee| 9 | 9 | ...
|5 |ffff| 7 | 5 | ...


Solution

  • You will need to keep track of the first file that exists and where you are in a file, so add variables "j" and "k" like in the code below:

       Dim thisFile As String   'input file name
       Dim outFile As String    'output file name
       Dim dataRead As String   'containing data copied and pasted
    
       outFile = "tempOut.csv"
       Open outFile For Output As #1
    
       k = 0
    
       For i = 1 To 40
         thisFile = "C:\datafolder\data" + CStr(i) + ".csv"
         Open thisFile For Input As #2
         j = 0
    
         Do Until EOF(2)         'read until the end of the file
           If k = 0 Then k = i
           j = j + 1
           Line Input #2, dataRead
    
           If i = k Or (i > k And j > 1) Then
              Print #1, dataRead
           End If
         Loop
    
         Close #2
       Next i
    
       Close #1