We know that the Microsoft Excel application has a row limit of 1,048,576. So we can’t create a CSV file with more than 1048576 rows using Excel. Also if you receive a file with more rows than that, then Excel will only show the rows upto 1048576. So how do we create a CSV file with more than 1048576 rows using Excel? We can do that with the help of a well known text-editing program “Notepad”. Notepad is included in all the Microsoft Windows versions. So if you are using any Microsoft Windows version then you can create the file without installing a new software.

How to import multiple csv files into Excel You click File Open and select Text Files (.prn,.txt,.csv) from the drop down list in the lower right-hand corner. To select adjacent files, click the 1st file, hold down the Shift key, and then click the last file. Both of the clicked. Powershell Script to combine multiple CSV files into an XLSX file. In my day to day I encounter instances where it would be beneficial to combine multiple CSV files into one XLSX file, with one CSV per worksheet. The following script will scan the current directory for files ending in.csv and adds them to an excel spreadsheet. The Best Possible Way for Combining Excel Files by Merging data into ONE Workbook - POWER QUERY Power Query is the best way to merge or combine data from multiple Excel files in a single file. You need to store all the files in a single folder and then use that folder to load data from those files into the power query editor.

If you already have data in Excel files then you can skip the first few steps.

Sometimes you may require to create large CSV files for testing purposes. Suppose that we need to create a file with 2 million rows of data. It is not practical to type all those rows. So we can use a simple macro to write the 2 million rows of data. In this example I will write data only in 3 columns. First create a new Excel workbook. Then add this macro and run it.

Sub WriteData()
Dim i As Long
For i = 1 To 1048576
Range('A' & i).Value = i
Range('B' & i).Value = 'Name ' & i
Range('C' & i).Value = 'Address ' & i
Next i
MsgBox 'Completed!', vbInformation, '
End Sub

So if you run above macro it will create a file like this. Top of the sheet would look like this.

And the end of the sheet would look like this.

So this is a type of simplest sample data we can create using a macro. However you can create more realistic sample data with the use of lists of first names, last names, Address line 1, Address line 2 and City,State. If you can create the above set of lists then you can use the Random function in VBA and combine those lists in many different ways in each row. Longer your lists, lower will be the duplicates.

So now we have created 1048576 rows out of 2000000. Next we can save this file as a CSV file. Let’s name it as “CSV 1.csv”

When you click the “Save” button, Excel will show you a message like this.

Select “Yes”. Then close the CSV file. Now we need to create the rest of the rows in a different excel file as Excel doesn’t allow us to enter more than 1048576 rows in one sheet. Also CSV files can’t contain multiple sheets like .xlsx and .xls files. So let’s create a new blank workbook to add the remaining rows (rows from 1048577 to 2,000,000.). We can use the macro below to write remaining data to this new file.

Combine Csv Files Excel Into Two

Sub WriteRemainingData()
Dim i As Long
Dim Counter As Long
Counter = 1
For i = 1048577 To 2000000
Range('A' & Counter).Value = i
Range('B' & Counter).Value = 'Name ' & i
Range('C' & Counter).Value = 'Address ' & i
Counter = Counter + 1
Next i
MsgBox 'Completed!', vbInformation, '
End Sub

So if you run above macro, the remaining rows of data will be written to this new Excel workbook. So the top rows of the file would look like this.

And the end of the rows would look like this.

Save this file as a CSV file by following the same steps. Let’s name it as “CSV 2.csv”. Now we have two CSV files. Total rows of the both files equals 2 million. Next, let’s look at how we can create a CSV file with 2 million rows using these 2 files. First open the CSV 1.csv file with the Notepad.

So it would look like this.

Merge csv files excel vba

Then open the CSV 2.csv file also with the Notepad. Copy all the data from the second file and paste them on the end of the CSV 1.csv file. Now save the CSV 1.csv file. You have 2 millions of rows in your CSV 1.csv file. However you will not be able to view all the data from the Excel application. It will show this message if you open the file in Excel.

Merge Csv Files Excel 2016

So if you open it with Excel then it will show the data upto row 1048576. But you will be able to view all the rows from the other applications which are capable of handling higher numbers of rows.

Coments are closed

Most Viewed Posts

  • Love Rosie Amazon Prime
  • Power Pivot Merge Two Tables
  • Emulationstation Fire Tv
  • Microsoft Teams For Ios
  • Clearnet Browser

Scroll to top