Excel import multiple CSV files into separate sheets VBA

The CSV file extension appears on text documents that contain comma-separated values. Like spreadsheet data, each line in a CSV file separates into individual values that correspond with the cells in a worksheet row. CSV files use commas to show the divisions between values and carriage returns to mark the ends of lines. One way to turn a CSV file into a Microsoft Excel spreadsheet is to open the document in Excel, but with this method you won't see the text-import wizard that allows you to specify how Excel should interpret text files. Overcome these problems and turn multiple CSV files into a single multiworksheet Excel document.

1. 1.

   Click on one of the worksheet tabs at the bottom of the interface to select the sheet into which you want to import your CSV-based data. By default, Excel files contain three worksheets when you first create them, all with generic names.

2. 2.

   Select the "Data" tab from the Excel Ribbon. In the Get External Data section, click on the "From Text" button.

3. 3.

   Locate your file in the Import Text File dialog box. You can choose a file on your local hard drive or select from a networked volume.

4. 4.

   Click on the "Import" button to bring up the Text Import Wizard. Verify that the wizard correctly identifies your data as delimited. If it doesn't, click on the "Delimited" radio button.

5. 5.

   Set the "File Origin" drop-down menu to the correct text-encoding type. If you created the file yourself on Windows, select "Windows (ANSI)." If you're not sure of the encoding, check with the person who created the file.

6. 6.

   Click on the "Next" button to move on to step two of the import wizard. Activate the Comma check box in the list of delimiter choices. Scroll through the data preview at the bottom of the wizard dialog box to verify that Excel correctly divides up the individual data fields on each line of your text file and make any necessary adjustments.

7. 7.

   Click on the "Next" button again to continue with step three. Review each column of data to check the data format assigned by the wizard. If its selection doesn't match the data displayed in the preview, use the "Column Data Format" radio buttons to reassign the correct format. Use the "Advanced" button to access additional format-specific data settings, including date and number formats.

8. 8.

   Click on the "Finish" button to bring up the Import Data dialog box. Select the "Existing Worksheet" radio button to point the wizard toward the worksheet you want to use. Click on the "OK" button to bring in your data.

9. 9.

   Select another worksheet and repeat the process with another CSV file. Create additional worksheets if you need to import more than three data sets into your spreadsheet. Save your file in XLSX or XLS format, depending on whether you only work with other users of Excel 2010 or you send files to people who use older versions of the software.

   I am trying to merge multiple excel files that have CSV extension and the sheet name is different for every file. The files schema is the same.

   Is there any macro that can merge multiple CSV excel files? I tried to find smth similar on  community.

   Although a CSV file can hold a huge amount of data, the sheer weight of these files can cause issues when you want to share the file with others. As a result, large CSV files are often divided into multiple, smaller files. This can become an overwhelming task when you need to edit your data. Luckily, CSV files are compatible with most software applications, so you can easily import your CSV files into your desktop and online spreadsheet tools.

   In this article, you’ll learn how to combine multiple CSV files into one Excel file by using the Power Query built-in feature. You’ll also learn how to use the Windows Command Prompt function to merge multiple CSV files. Finally, you’ll explore our top online tools that are an alternative to Excel’s built-in functions.

   

   Get Started With Layer Today!

   Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

   If I have helped, Don't forget to add to my reputation (click on the star below the post)
   Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
   Use code tags when posting your VBA code: [code] Your code here [/code]

   **I would like to give ALL credit to the Original author and the coder who updated it to work with Excel 2016.**
   
   As i'm still early into my journey with VB and I would really appreciate some help with this.
   The code below imports the CSVs fine, creates and names the worksheets fine but it leaves the excel 'import' workbooks open ( it was only 3 this time but imagine if there was 20 or more) so how can i get it to NOT open the workbooks or automatically close them afterwards or is there a better way to do this altogether?
   
   Option Explicit
   Sub ImportCSVs()
   'Author: Jerry Beaucaire
   'Date: 8/16/2010
   'Summary: Import all CSV files from a folder into separate sheets
   ' named for the CSV filenames
   
   'Update: 2/8/2013 Macro replaces existing sheets if they already exist in master workbook
   'Update: base script as seen in: Jerry Beaucaire's - Excel Assistant - CSVs to Sheets
   'Update: adjusted code to work in Excel 2016
   
   Dim fPath As String
   Dim fCSV As String
   Dim wbName As String
   Dim wbCSV As Workbook
   Dim wbMST As Workbook
   
   
   wbName = "this is a string"
   Set wbMST = ThisWorkbook
   
   fPath = "C:\csvs\" 'path to CSV files, include the final \
   Application.ScreenUpdating = False 'speed up macro
   Application.DisplayAlerts = False 'no error messages, take default answers
   fCSV = Dir(fPath & "*.csv") 'start the CSV file listing
   
   On Error Resume Next
   Do While Len(fCSV) > 0
   Set wbCSV = Workbooks.Open(fPath & fCSV) 'open a CSV file
   If wbName = "this is a string" Then 'this is to check if we are just starting out and target workbook only has default Sheet 1
   wbCSV.Sheets.Copy After:=wbMST.Sheets(1) 'for first pass, can leave as is. if loading a large number of csv files and excel crashes midway, update this to the last csv that was loaded to the target workbook
   Else
   wbCSV.Sheets.Copy After:=wbMST.Sheets(wbName) 'if not first pass, then insert csv after last one
   End If
   
   fCSV = Dir 'ready next CSV
   wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
   Loop
   
   Application.ScreenUpdating = True
   Set wbCSV = Nothing
   End Sub
   
   Many Thanks In Advance.
   o/

    

   rlv01 said:

   Any reason you can't just close the import CSV file at the end of each loop?
   
   

   VBA Code:

     wbCSV.Close False
     fCSV = Dir 'ready next CSV
     wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
   Loop

   Click to expand...

   YES! I changed the argument to TRUE (which i thought you might be hinting at) and it closed (well infact it didnt even look like it opened anything!) each sheet.
   As i say i'm very early into my journey with VB and the above code isn't mine but I can 'read' and 'understand' most of the workings even though i dont understand every syntax and more so some of the 'arguments' that each syntax uses YET but I'm enjoying the adventure into VB.
   
   Thanks for you help,
   (please feel free to look at my other requests (CRIES!) for help)

    

   How do I import multiple CSV files into separate sheets in Excel?

   Import multiple CSV files to Excel with Copy Sheets tool.

   On the Ablebits Data tab, click Copy Sheets and indicate how you want to import the files: ... .

   Click the Add files button, and then find and select the csv files for importing. ... .

   Finally, the add-in will ask exactly how you want to paste the data..

   How do you import multiple files in Excel VBA?

   Combine and Transform Data To combine all the files with a query, and then launch the Power Query Editor, select Combine > Combine and Transform Data. Combine and Load To display the Sample file dialog box, create a query, and then load to worksheet, select Combine > Combine and Load.

   Can a CSV file have multiple sheets?

   csv is a simple text file format. It can't store multiple sheets, pivottables, or any other advanced feature.