Combine multiple csv files to one excel file

[jdbaba]

Hi All,

I have a bunch of csv files that I need to combine on one single excel sheet. I have around 100 or more files so copy and paste doesn't seem feasible here. Basically all the csv files have the same format and equal number of rows and column. Each file has 40 rows and 2 columns. The first column on each file is the information about the data on column 2. So, I want to copy the first column of first excel file and after that for other excel files, I want only the second column to be on columns B, C, D, ...... (of combined files) and so on. I know a program can be written to do this stuff. But I am naive at this complicated stuff. I would highly appreciate if you could help me as soon as possible.

Thanks so much.

Jd

 

[Trevor G]

Are all the files in one folder? If so you could use something like this.

Sub Open_My_Files()
Dim mypath As String
Dim MyFile As String
mypath = "M:\Access Files\"
MyFile = Dir(mypath)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Sheets("sheet1").Range("A1") = "Headings"
Do While MyFile <> ""
If MyFile Like "*.csv" Then
Workbooks.Open mypath & MyFile
Sheets(1).UsedRange.Copy

ActiveWorkbook.Close True
Range("A1").Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).PasteSpecial xlPasteAll

Cells.PasteSpecial xlPasteAll

End If
MyFile = Dir
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

 

[jdbaba]

Hi Trevor,

Thank you so much for your reply. I have some questions about your code. What do I need to change on your code. I guess mypath means the folder where I have saved the csv files. I don't understand what do I need to write on myfile. What other things do I need to do before I run this code ?

When I run the code you provided the code runs fine but it only writes "Headings" on cell A1. But there is no other information.

Thanks.

Jd

 

[Trevor G]

You should only need to change the path to the folder location on your system.

You shouldn't need to do anything else.

 

[jdbaba]

HI Trevor, Thank you so much for your reply again.

I was able to combine all the csv files to one excel file. The only thing now I want is to arrange the content of each cells in different columns. RIght now all the data are stacked on first 2 columns. Each excel csv file has 2 columns. So if I have 5 files then I want 10 columns. Thanks again.

Jd

 

[Trevor G]

Adjusted the code and tested it with 2 CSV files and it works.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#00007F">Dim</SPAN> mypath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>mypath = "M:\Access\" <SPAN style="color:#007F00">'Adjust to your path</SPAN><br>MyFile = Dir(mypath)<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>Application.Calculation = xlCalculationManual<br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br><br>Sheets("sheet1").Range("A1") = "Headings"<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.csv" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open mypath & MyFile<br>Sheets(1).UsedRange.Copy<br><br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br>Range("iv1").Select <SPAN style="color:#007F00">'Changed to select column IV1</SPAN><br>Selection.End(xlToLeft).Select <SPAN style="color:#007F00">'Used to find last column with data in it</SPAN><br>ActiveCell.Offset(0, 1).PasteSpecial xlPasteAll <SPAN style="color:#007F00">'Offset to one column</SPAN><br><br>Cells.PasteSpecial xlPasteAll<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>Application.Calculation = xlCalculationAutomatic<br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>