Macro to Copy a Specific Column Values From Different csv Files into a Single Worksheet

[Ay Sticky]

Good day experts, I need to fetch values from an identified column (DistanceToNext), across numerous csv files into a single worksheet. These csv files are indexed with numbers. The first csv file indexed 001 is to be pasted in first column of the single worksheet. The second csv indexed 002 to be pasted in second column of the single sheet and so on. All files with samples can be obtained in the link below, including a text file describing the whole task because the tasks seems more than what I have explained here.

New folder

Shared with Dropbox

www.dropbox.com

 

[Marc L]

Hi,​

​

according to your private conversation after taking a glance to your zip attachment I can't reproduce your result​

'cause there is only 72 rows in your worksheet but in the source csv text files thousands data rows exist !​

And it seems there is no logic link between these text files and your result worksheet,​

maybe the reason why you did not receive any help …​

​

So if you had the weird idea to share a worksheet result without the original text files the better is you link a new attachment​

but this time with the relative source text files according to the expected result worksheet​

and with a good enough elaboration of the need in order there is nothin' to guess.​

 

[Ay Sticky]

Thank you for your response and offering me the chance to explain more. And I'll do this using a result worksheet demo, assuming we have the source csv files somewhere named csv1 csv2 csv3 csv4 csv5 and so on. In each of these csv source files I need to copy values in a particular column into a newly created result worksheet.

The demo worksheet above illustrates an example of the result worksheet to be created.
Down the rows of column A are the values copied from csv1 source file but here in the result worksheet it must start from a zero. Through the columns of row1 is the transpose of the same values copied from the csv1 source file.
Down the rows of column B (starting from B2) are the values copied from csv2 source file and also starts from a zero. Through the columns of row2 (starting from B2) is the transpose of the same values copied from the csv2 source file.
Down the rows of column C (starting from C3) are the values copied from csv3 source file and also starts from a zero. Through the columns of row3 (starting from C3) is the transpose of the same values copied from the csv3 source file.... These pattern of arrangement goes on and on for the rest of the csv source files.

Now leaving the demo aside and coming to the real work which I posted the dropbox link earlier. In the NewFolder folder, kindly disregard the file with 72 rows, it is just a demo, which I've alternatively demonstrated in the explanation above. All the csv source files are in the folder "GbonagunNodeMeasurements". We will create a new result worksheet and carry out the tasks as explain in the demo worksheet above. But before this, some tasks need to be carried out on each of the csv source files.

#1 All the csv source files contain duplicate rows which needs to be removed. Or let me simply say I want to delete the row after every other row in all the csv source files.

#2 Here, the csv source files are named in the pattern 001, 002 and so on. From the 001 csv file, the values in column E (starting from E5) are to be copied to column A of our newly created result worksheet but we first input zero in the result worksheet and transpose same values to the columns of its row1, just as explained earlier in the demo example. And repeat same procedure for rest of the csv files according to their indexed name. That is, the column E values in 002 csv file will also be extracted to column B (starting from B2) of the created result worksheet and so on.

#3 Before the values in column E of every csv source file are extracted to the result worksheet, those values are to be rounded up to two decimal places.

I'll be so glad to elaborate again on any aspect asked. Thank you for helping out.

 

[Marc L]

As it's difficult without text files relative to the result worksheet or vice versa so :​

1. according to the first text file in your zip attachment - aka '001_1-202110290837.csv' - in the result worksheet what is the last row # used
   in column A and what is the cell content ?
   
   
2. According to the second text file - aka '002_2-202110290838.csv' - what is the last row # used in colum B and what is the cell content ?

 

[Marc L]

#3 Before the values in column E of every csv source file are extracted to the result worksheet, those values are to be rounded up to two decimal places.

Do you just need to format cells in the worksheet but keeping the original numbers as they are in the source text files​

or to round / truncate them to two decimals before writing result to cells ?​

 

[Ay Sticky]

As it's difficult without text files relative to the result worksheet or vice versa so :​

1. according to the first text file in your zip attachment - aka '001_1-202110290837.csv' - in the result worksheet what is the last row # used
   in column A and what is the cell content ?
   
   
2. According to the second text file - aka '002_2-202110290838.csv' - what is the last row # used in colum B and what is the cell content ?

I'm seriously finding it hard to understand what you mean by "text files relative to the result worksheet or vice versa". I'll be glad if you could explain more please.
Nevertheless, I'll make a pictorial demonstration to answer these two questions and make the task clearer. For clear illustration, I'm doing the task manually now but only for the first, second and third csv text files.

The image above shows the original first text file to be worked on, as it is in the dropbox link. The values we need copying to the Result Worksheet is in column E as highlighted, ranging from E5 to E1124 (the above picture didn't cover all the rows). The cell E1124 is where the data ends for this column. It could be noticed that there are repeated or duplicate values, so I will delete every other rows now and round up all values in this particular column to 2 decimal places. The result is presented below and I'll tag it as "processed first text file".

From the above picture of the processed first text file, the duplicate rows have been eliminated and also the values were rounded to 2DP.
Now I will create a new xslx worksheet and name it as Result Worksheet. I'll then copy all the values in column E which now ranges from E3 to E562, to paste in the Result worksheet as shown below.

From above screenshot of the Result Worksheet, the values copied from the column E of the processed first text file has been pasted down the rows of the first column A in the Result Worksheet and also transposed to the columns of row 1. But all these are done after inserting a zero at first, as seen.
I'll repeat same procedure for the second text file.

The above screenshot displays the values in the second text file. The values to be copied are from column E5 to E1122 but they have duplicate rows of data. The cell E1122 is where the data ends for this column. I'll delete duplicate rows and round up to 2DP as I also did for first text file. The result is the below.

The above screenshot is the processed second text file. We'll then copy the column E values from column E3 to E561 where we have the last cell with data for that column. The copied values are pasted in our same Result Worksheet as below.

As seen from above image, the copied values from the second text file are pasted starting from B2, down the rows of column B and same values transposed to the columns of row 2. Remember we will first insert zero.

Doing same procedure for the third text file. Assuming the processing procedures for each text file has been understood, I'll just post the result of the processed third text file.

From the above image, the third text file has also been processed by deleting its every other rows and rounding up the values in column E. The range of copied values for this text file is from E3 to E560, where E560 is the last cell with value. For easy confirmation, I have uploaded the three processed text files to this reply along with the Result Worksheet used for this demonstration.

Pasting these copied values into the same Result Worksheet gives the below.

It can also be noticed in the above image of the Result Worksheet that after inserting zero in C3 for the processed third text file, the copied values are pasted down the column of C starting from C3 and also transposed to the columns of row3. This is how the procedures goes on and on for all the text files.

Kindly let me know in case this explanation hasn't still answered your question. Thank you for not getting tired of my boring explanations.

I'm sorry I didn't see where to upload files to this reply, so I have posted the already processed text files used in this explanation to this link

ProcessedFirstThreeTextFiles

Shared with Dropbox

www.dropbox.com

 

[Ay Sticky]

Do you just need to format cells in the worksheet but keeping the original numbers as they are in the source text files​

or to round / truncate them to two decimals before writing result to cells ?​

Either way is welcome. What I highly need is the Result Worksheet, which should have all its values rounded to 2 decimal places.

 

[Marc L]

​

Ok now I have the same result as yours but which layout do you need :​

the original one in your SampleArrangement.xlsx workbook with the first column and first row numbered as file index​

or without any index like in your ResultWorksheet.xlsx last attachment ?​

​

Another point : the result workbook should be saved ? If yes share the necessary information …​

​

 

[Ay Sticky]

​

Ok now I have the same result as yours but which layout do you need :​

the original one in your SampleArrangement.xlsx workbook with the first column and first row numbered as file index​

or without any index like in your ResultWorksheet.xlsx last attachment ?​

​

Another point : the result workbook should be saved ? If yes share the necessary information …​

​

Please pardon me for replying late. I never knew my Email app is not synchronizing until now....

We don't really need the indexing of the first rows and columns, so like that of ResultWorksheet.xlsx will do....

Please yes the result workbook should be saved. In fact that's the main thing we need out of all.... It can be saved with any name though.

 

[Marc L]

A VBA demonstration for starters :​

Sub Demo1()
    Dim P$, C$, L&, V(), F%, K&, N&, S$(), R&
        P = ThisWorkbook.Path & "\GbonagunNodeMeasurements\"
        C = Dir$(P & "*.csv"):  If C = "" Then Beep: Exit Sub
        While C > "":  L = L + 1:  C = Dir$:  Wend
        ReDim V(1 To L + 1, 1 To L + 1)
        V(L + 1, L + 1) = 0
   With Application
       .StatusBar = "       Processing " & L & " csv files …"
        F = FreeFile
        C = Dir$(P & "*.csv")
    Do
        K = 0
        N = N + 1
        V(N, N) = 0
        Open P & C For Input As #F
        S = Split(Input(LOF(F), #F), vbLf)
        Close #F
    For R = 4 To .Min(.Match("""#*", S, 0) - 5, 3 + L * 2) Step 2
        K = K + 1
        V(K + N, N) = Round(Val(Split(Split(S(R), ",")(4), """")(1)), 2)
        V(N, K + N) = V(K + N, N)
    Next
        L = L - 1
        C = Dir$:  If N Mod 300 = 0 Then DoEvents
    Loop Until C = ""
       .SheetsInNewWorkbook = 1
       .StatusBar = False
        Workbooks.Add.Sheets(1).[A1].Resize(N + 1, N + 1).Value2 = V
       .DisplayAlerts = False
        ActiveWorkbook.SaveAs P & "CSV Import ", 51
       .DisplayAlerts = True
   End With
End Sub