How To Import Text Files To Excel As Separate Sheets With Original Names In Excel?

Microsoft Excel is renowned for its ability to efficiently manage and analyze large amounts of data. When working with multiple text files that contain data you need to import into separate sheets with the original name in Excel, the process can seem daunting. Nonetheless. Excel presents a straightforward yet powerful solution for effortlessly achieving this task.

Within this article. We shall walk you through precise techniques on how to import multiple text files into multiple sheets. This invaluable skill allows for efficient organization and analysis of your data. Mastery of these methods grants you the ability to seamlessly import data from various text files into Excel. Thereby saving significant time and effort when working with extensive datasets. Whether you're working with data from various sources or need to merge information from multiple text files, Excel's versatile import capabilities will empower you to streamline your workflow. Let's dive into the world of importing text files in Excel and discover the practical techniques that will enhance your data management skills.

To enable VBA in excel follow this instruction

Right Click on Ribbon Bar and select Customize the Ribbon option

Check Developer box and click on OK

Approach 1: Using VBA Macro to Import Multiple Text Files by selecting folder containing all required text files

If you are familiar with Excel VBA (Visual Basic for Applications), you can leverage its programming capabilities to import multiple text files into separate sheets. This approach provides flexibility and customization options for handling specific requirements.

• Step 1 − To open the Visual Basic Editor in Excel click on the "Alt+F11" combination or go to the Developer tab in the ribbon and choose the Visual Basic option.

• Step 2 − In the Visual Basic Editor, click on "Insert" and select "Module" to insert a new module.

• Step 3 − In the module, paste the following VBA code −

Sub LoadPipeDelimitedFiles()
   'UpdatebyExtendoffice20181010
   Dim xStrPath As String
   Dim xFileDialog As FileDialog
   Dim xFile As String
   Dim xSheetCount As Long
   Dim xWS As Worksheet
    
   On Error GoTo ErrHandler
   Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
   xFileDialog.AllowMultiSelect = True
   xFileDialog.Title = "Select a folder [Kutools for Excel]"
   If xFileDialog.Show = -1 Then
      xStrPath = xFileDialog.SelectedItems(1)
   End If
   If xStrPath = "" Then Exit Sub
   Application.ScreenUpdating = False
   xFile = Dir(xStrPath & "\*.txt*")
   Do While xFile <> ""
      Set xWS = Sheets.Add(After:=Sheets(Sheets.Count))
      xWS.Name = Left(xFile, Len(xFile) - 4)
      With xWS.QueryTables.Add(Connection:="TEXT;" & xStrPath & "" & xFile, Destination:=xWS.Range("A1"))
         .Name = "a" & xSheetCount
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .TextFilePromptOnRefresh = False
         .TextFilePlatform = 437
         .TextFileStartRow = 1
         .TextFileParseType = xlDelimited
         .TextFileTextQualifier = xlTextQualifierDoubleQuote
         .TextFileConsecutiveDelimiter = False
         .TextFileTabDelimiter = False
         .TextFileSemicolonDelimiter = False
         .TextFileCommaDelimiter = False
         .TextFileSpaceDelimiter = False
         .TextFileOtherDelimiter = "|"
         .TextFileColumnDataTypes = Array(1, 1, 1)
         .TextFileTrailingMinusNumbers = True
         .Refresh BackgroundQuery:=False
      End With
      xFile = Dir
      xSheetCount = xSheetCount + 1
   Loop
   Application.ScreenUpdating = True
   Exit Sub
ErrHandler:
   MsgBox "No txt files found.", , "Kutools for Excel"
End Sub

• Step 4 − Select "Macro" tab.

• Step 5 − Select available Macro and click on RUN

• Step 6 − It’ll open window to choose folder containing all text file that needs to be imported. Select the folder and press OK.

Excel will import the data from all the text files present in folder into separate sheets, with each sheet named after the corresponding file name.

Approach 2: Using VBA Macro to Import Multiple Text Files by selecting files you want

• Step 1 − To open the Visual Basic Editor in Excel click on the "Alt+F11" combination or go to the Developer tab in the ribbon and choose the Visual Basic option.

• Step 2 − In the Visual Basic Editor, click on "Insert" and select "Module" to insert a new module.

• Step 3 − In the module, paste the following VBA code −

Sub CombineTextFiles()
'updateby Extendoffice
   Dim xFilesToOpen As Variant
   Dim I As Integer
   Dim xWb As Workbook
   Dim xTempWb As Workbook
   Dim xDelimiter As String
   Dim xScreen As Boolean
   On Error GoTo ErrHandler
   xScreen = Application.ScreenUpdating
   Application.ScreenUpdating = False
   xDelimiter = "|"
   xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Kutools for Excel", , True)
   If TypeName(xFilesToOpen) = "Boolean" Then
      MsgBox "No files were selected", , "Kutools for Excel"
      GoTo ExitHandler
   End If
   I = 1
   Set xTempWb = Workbooks.Open(xFilesToOpen(I))
   xTempWb.Sheets(1).Copy
   Set xWb = Application.ActiveWorkbook
   xTempWb.Close False
   xWb.Worksheets(I).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=True, OtherChar:="|"
   Do While I < UBound(xFilesToOpen)
      I = I + 1
      Set xTempWb = Workbooks.Open(xFilesToOpen(I))
      With xWb
         xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
         .Worksheets(I).Columns("A:A").TextToColumns _
         Destination:=Range("A1"), DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, _
         ConsecutiveDelimiter:=False, _
         Tab:=False, Semicolon:=False, _
         Comma:=False, Space:=False, _
         Other:=True, OtherChar:=xDelimiter
      End With
   Loop
ExitHandler:
   Application.ScreenUpdating = xScreen
   Set xWb = Nothing
   Set xTempWb = Nothing
   Exit Sub
ErrHandler:
   MsgBox Err.Description, , "Kutools for Excel"
   Resume ExitHandler
End Sub

• Step 4 − Select "Macro" tab.

• Step 5 − Select available Macro and click on RUN.

• Step 6 − It’ll open a window to text files. Choose multiple text files by pressing ‘ctrl’ and clicking the file you want, then press OK.

Excel will import the data from the text files into separate sheets, with each sheet named after the corresponding file name.

Conclusion

Importing text files as separate sheets with original names in Excel is a powerful feature that enhances your ability to manage and analyze data efficiently. In this article, we explored two approaches to accomplish this task. The first approach utilized Excel's Power Query Editor, allowing you to import and transform data from multiple text files effortlessly. The second approach involved using VBA macros to automate the import process, providing customization options for specific requirements.

Incorporate these techniques into your Excel workflow to streamline the import of text files, saving time and improving data management capabilities. Excel's versatile import capabilities empower you to work with data from various sources seamlessly, facilitating data analysis and decision-making processes.