How to transpose multi CSV files in a folder using VBA into 1 excel file

[amir988]

I have a folder in C:\CSV\ where more then 6000 csv files are available and the more important thing is, every csv file has only one colum but many rows. Now i want to transpose all csv files and make it one file in excel where transposed data shown. there is no name of column and rows. Any one plz guide.

 

[arthurbr]

Try Merge all CSV or TXT files in a folder in one worksheet

 

[ajetrumpet]

Now i want to transpose all csv files and make it one file in excel where transposed data shown

if i read your post correctly, you want to do the folling on each file in the directly???

[QUOTE="arthurbr, post: 5404960, member: 71680"]
Try Merge all CSV or TXT files in a folder in one worksheet
[/QUOTE]that's not bad.  as an alternative, which is what I was just checking on, I found that this:[code]
Public Function txtstreamreadall_h()
Dim fs As Object, s As String, a As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.openTextFile("C:\Users\Owner\Desktop\1.csv", 1)
    s = a.readall
    txtstreamreadall_h = s
    a.Close
End Function

returns the entire content of a csv file with line breaks included. whereby this:

Public Function finput_h()
Dim f As String, text As String, textline As String
f = "C:\Users\Owner\Desktop\1.csv"
    Open f For Input As #1
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
    finput_h = text
    Close #1
End Function

also does the same thing but returns the entire file's content *without* the line breaks in it. another resource to use, which is pretty well known, for looping through files in a directory is allen browne's famous page: Microsoft Access tips: List files recursively

 

[amir988]

Try Merge all CSV or TXT files in a folder in one worksheet

this code just merge the files but not transpose it. before merging its important that the data should be transposed. (merging vba is already available.)

 

[amir988]

if i read your post correctly, you want to do the folling on each file in the directly???

[QUOTE="arthurbr, post: 5404960, member: 71680"]
Try Merge all CSV or TXT files in a folder in one worksheet
[/QUOTE]that's not bad.  as an alternative, which is what I was just checking on, I found that this:[code]
Public Function txtstreamreadall_h()
Dim fs As Object, s As String, a As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.openTextFile("C:\Users\Owner\Desktop\1.csv", 1)
    s = a.readall
    txtstreamreadall_h = s
    a.Close
End Function

returns the entire content of a csv file with line breaks included. whereby this:

Public Function finput_h()
Dim f As String, text As String, textline As String
f = "C:\Users\Owner\Desktop\1.csv"
    Open f For Input As #1
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
    finput_h = text
    Close #1
End Function

also does the same thing but returns the entire file's content *without* the line breaks in it. another resource to use, which is pretty well known, for looping through files in a directory is allen browne's famous page: Microsoft Access tips: List files recursively

Merging is not a problem overall, i can merge even a more easy using CMD command, using copy *.csv combined.csv problem is in transposing the data, how do i transpose the csv files in a folder. where files doest not have any name of columns and rows. and csv files contain only 1 column but multiple rows.

 

[amir988]

Check out here i have data shown in img 1 and 2. and i want like img 3. that it.

 

[ajetrumpet]

hey bud, I think I've got it. change this to suit your needs:

Public Function txtstreamreadall_h()
Dim fs As Object, s As String, a As Object, counter As Long, counterArray As Long, arr() As String, cRow As Long, cOffset As Long
cRow = 1
    For counter = 1 To 4
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.openTextFile("path where files are" & CStr(counter) & ".csv", 1)
        s = a.readall

        a.Close
            arr() = Split(s, vbCrLf)
                cOffset = 0
                For counterArray = LBound(arr) To UBound(arr)
                    Range("a" & CStr(cRow)).Offset(0, cOffset) = arr(counterArray)
                    cOffset = cOffset + 1
                Next counterArray
        cRow = cRow + 1
    Next counter
End Function

 

[amir988]

hey bud, I think I've got it. change this to suit your needs:

Public Function txtstreamreadall_h()
Dim fs As Object, s As String, a As Object, counter As Long, counterArray As Long, arr() As String, cRow As Long, cOffset As Long
cRow = 1
    For counter = 1 To 4
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.openTextFile("path where files are" & CStr(counter) & ".csv", 1)
        s = a.readall

        a.Close
            arr() = Split(s, vbCrLf)
                cOffset = 0
                For counterArray = LBound(arr) To UBound(arr)
                    Range("a" & CStr(cRow)).Offset(0, cOffset) = arr(counterArray)
                    cOffset = cOffset + 1
                Next counterArray
        cRow = cRow + 1
    Next counter
End Function

thats work like charm. thank u