跳到主要內容

如何在Excel中快速批量導入多個csv / text / xml文件?

在Excel中,您可能不得不將工作簿另存為csv文件,文本文件或xml文件,但是您是否曾經嘗試過將多個csv / text / xml文件從一個文件夾導入到工作簿或工作表中? 在本文中,我介紹了一些快速批量導入它們的方法。

使用VBA將文件夾中的多個文本文件導入到工作簿的每個工作表中

使用VBA將文件夾中的多個csv文件導入到一張工作表中

使用VBA將一個文件夾中的多個xml文件導入到一張工作表中

使用Kutools for Excel將多個xml / csv文件導入或合併到工作表或工作簿中 好主意3

使用Kutools for Excel將每張工作表導出為csv / text / pdf到一個文件夾好主意3


要將文本文件從文件夾導入到工作簿中,可以使用VBA下面的命令對其進行快速處理。

1.啟用空白工作簿,然後按 Alt + F11鍵 打開鑰匙 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將VBA粘貼到 模塊 窗口。

VBA:將所有文本文件從文件夾導入到工作簿中

Sub LoadPipeDelimitedFiles()
'UpdatebyKutoolsforExcel20151214
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xCount As Long
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    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 <> ""
        xCount = xCount + 1
        Sheets(xCount).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & xStrPath & "\" & xFile, Destination:=Range("A1"))
            .Name = "a" & xCount
            .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
            xFile = Dir
        End With
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files txt", , "Kutools for Excel"
End Sub

3。 按 F5 鍵或 按鈕以運行VBA,並在彈出對話框中選擇要從中導入文本文件的文件夾。 看截圖:

doc導入多個csv文本xml 1

4.然後點擊 OK,並且所選文件夾中的每個文本文件都已導入到活動工作簿的一個工作表中。 看截圖:

doc導入多個csv文本xml 2doc導入多個csv文本xml 3

輕鬆地將多個工作表/工作簿合併為一個工作表或工作簿

在Excel中,將多個工作表或工作簿組合成一個工作表或工作簿可能很麻煩,但是使用 結合 在Kutools for Excel中,您可以將數十個工作表/工作簿合併到一個工作表或工作簿中,也可以通過單擊幾下將工作表合併為一個工作表。  點選取得全功能 30 天免費試用!
合併表
 
Kutools for Excel:擁有300多個便捷的Excel加載項,可以在30天內免費試用。

要將所有csv文件從一個文件夾導入到一張紙中,可以使用以下VBA代碼。

1.啟用空白工作表,然後按 Alt + F11鍵 打開鑰匙 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後在VBA下面粘貼到新 模塊 窗口。

VBA:將csv文件從一個文件夾導入到一個工作表中

Sub ImportCSVsWithReference()
'UpdatebyKutoolsforExcel20151214
    Dim xSht  As Worksheet
    Dim xWb As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Set xSht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\" & "*.csv")
    Do While xFile <> ""
        Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
        Columns(1).Insert xlShiftToRight
        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
        ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        xWb.Close False
        xFile = Dir
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files csv", , "Kutools for Excel"
End Sub

3。 按 F5 鍵或單擊 按鈕執行VBA,然後彈出一個對話框,選擇要從其中導入所有csv文件的文件夾。 看截圖:

doc導入多個csv文本xml 4

4。 點擊 OK,然後會彈出一個對話框,提醒您在導入之前是否清除了活動工作表的內容,請點擊此處。 看截圖:

doc導入多個csv文本xml 5

點擊後 ,所選文件夾中的所有csv文件都將導入到當前工作表中,並將數據從A列向右放置。 看截圖:

doc導入多個csv文本xml 6doc導入多個csv文本xml 7

小提示: 如果要將csv文件水平放置在工作表中,可以在VBA下面使用。

Sub ImportCSVsWithReferenceI()
'UpdatebyKutoolsforExcel20151214
    Dim xSht  As Worksheet
    Dim xWb As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xCount As Long
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Set xSht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then
        xSht.UsedRange.Clear
        xCount = 1
    Else
        xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1
    End If
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\" & "*.csv")
    Do While xFile <> ""
        Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
        Rows(1).Insert xlShiftDown
        Range("A1") = ActiveSheet.Name
        ActiveSheet.UsedRange.Copy xSht.Cells(1, xCount)
        xWb.Close False
        xFile = Dir
        xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files csv", , "Kutools for Excel"
End Sub 

doc導入多個csv文本xml 8


如果要將所有XML文件從一個文件夾導入到一張紙中,則可以使用以下VBA代碼。

1.選擇要放置導入數據的空白表,然後按 Alt + F11鍵 啟用鍵 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,將VBA代碼粘貼到 模塊 窗口。

VBA:將XML文件從文件夾導入到工作表中。

Sub From_XML_To_XL()
'UpdatebyKutoolsforExcel20151214
    Dim xWb As Workbook
    Dim xSWb As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xCount As Long
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    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
    Set xSWb = ThisWorkbook
    xCount = 1
    xFile = Dir(xStrPath & "\*.xml")
    Do While xFile <> ""
        Set xWb = Workbooks.OpenXML(xStrPath & "\" & xFile)
        xWb.Sheets(1).UsedRange.Copy xSWb.Sheets(1).Cells(xCount, 1)
        xWb.Close False
        xCount = xSWb.Sheets(1).UsedRange.Rows.Count + 2
        xFile = Dir()
    Loop
    Application.ScreenUpdating = True
    xSWb.Save
    Exit Sub
ErrHandler:
    MsgBox "no files xml", , "Kutools for Excel"
End Sub

3。 點擊 按鈕或 F5 鍵運行VBA,然後在彈出的對話框中選擇一個文件夾,請參見屏幕截圖:

doc導入多個csv文本xml 9

4。 點擊 OK,並將所選文件夾中的所有XML文件導入到活動工作表中。


如果您不熟悉VBA,請放心,在這裡,我介紹一個方便的工具– Excel的Kutools 為了你。 憑藉其強大 結合 實用程序,您可以將多個xml文件或csv文件快速組合到一個工作簿或一個Excel工作表中。

Excel的Kutools, 與以上 300 方便的功能,使您的工作更加輕鬆。 

安裝後 Kutools for Excel,請執行以下操作:(立即免費下載Kutools for Excel!)

1. Active Excel,然後單擊 Kutools 加 > 結合。 查看截圖:
doc合併1

2.在 合併的第一步 對話框中,根據需要選擇一個分離選項。 看截圖:
doc合併2

3。 點擊 下一頁合併的第2步點擊此處成為Trail Hunter 加入 將文件從多個文件夾添加或將文件從一個文件夾添加到 工作簿 列表,還可以指定要從中合併的圖紙 下載學習單 右側部分列表。 看截圖:
doc kutools組合紙3

4。 點擊 下一頁 到最後一步 結合,然後您可以指定合併選項。
doc kutools組合紙4

5。 點擊,會彈出一個對話框,提醒您選擇一個位置來保存新的合併結果。 看截圖:
doc合併5

6。 點擊 節省。 所有添加的工作表都已合併為一個新的工作表。
doc合併6

小提示: 這款獨特的敏感免洗唇膜採用 Moisture WrapTM 技術和 Berry Mix ComplexTM 成分, 結合,您也可以將多個 CSV文件 在一個工作表或工作簿中形成多個文件夾或一個文件夾。


如果要將每張工作表作為csv / text / pdf文件導出到文件夾, Excel的Kutools拆分工作簿 實用程序可以幫您一個忙。

免費安裝 Kutools for Excel,請執行以下操作:

1.啟用要導出其工作表的工作簿,然後單擊 Kutools 加 > 工作簿 > 拆分工作簿。 看截圖:

doc導入多個csv文本xml 10

2。 在裡面 拆分工作簿 對話框中,您可以檢查需要導出的工作表名稱,默認情況下,所有工作表均已選中,然後檢查 指定保存格式 然後從下拉列表中選擇要另存為的文件格式。 看截圖:

doc導入多個csv文本xml 11

3。 點擊 分裂 然後選擇一個文件夾將拆分文件保存在 瀏覽文件夾 對話框,請參見屏幕截圖:

doc導入多個csv文本xml 12

4。 點擊 OK,現在所有選中的工作表都將作為新文件格式導出到所選文件夾中。


相關文章:

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (36)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bagaimana caranya menghilangkan header dari tiap-tiap file csv yang terbuka dalam worksheetnya
terima kasih
This comment was minimized by the moderator on the site
Hi there,this is a great tool, but I want to import the various XMl-Files into separate TAB-sheets. Is this possible as the XML's have different header ?
This comment was minimized by the moderator on the site
HelloThe instructions for importing multiple xmls into one tab of an excel document works but was wondering how to get it to line up the columns. My xmls don't all have the same tags. They are set up such that if the xml had no data for some headers(tags) then the header is missing from that xml. Is there a way to get the xmls to import so the same headers from each xml and associated data fall into the same column of excel?
This comment was minimized by the moderator on the site
Hi Experts

I am using the above code for importing multiple xml files into 1 worksheet using VBA however issue i am facing is when rows count reaches 650000 in a worksheet then this code doesn't process rest of the xml files in the folder. It gives an error "no files.xml". Require your kind support
This comment was minimized by the moderator on the site
Hi Team

I am using the code for importing Multiple XML files into single sheet with VBA however issue i am facing is when rows count reaches approximately 650000, then it doesn't processes rest of the xml files in the folder and gives an error that no xml files. Need your support to increase this count.
This comment was minimized by the moderator on the site
Hi, is there any way to import multiple csv files with semicolon as separator? Thank you!
PS Nice article!
This comment was minimized by the moderator on the site
Hello - I've used your VBA codes to extract data from multiple CSV files to excel file (the code on this page) and convert csv files to excel files ( this one: https://www.extendoffice.com/documents/excel/4615-excel-batch-convert-csv-to-xls-xlsx.html), with great results. They helped me save a lot of time.

However, I notice a common problem with both of these types of codes. To clarify, my system is set up to use the European standards for dates, while some of the CSV files I received for my work contain dates in US standards. The first problem is, when I extract or convert data from a CSV file that contains dates in US format, all of those dates are reversed (matching the EU standards used by my system). This is great but it also caused me troubles since I didn't know the codes would reverse the dates for me, so I went on ahead and did the same thing again. The second problem is, for the CSV files that contain dates already in the same format as the one used by my system (EU standards), only the ambiguous dates are reversed (i.e 04/05/2019 - 05/04/2019), while the ones that are too obvious, remain unchanged (i.e 30/04/2019).

What I would like the codes to do, is the exact same thing as they are shown here, only that they should copy and paste the data (especially dates) in the exact formats used in the original files. This would help prevent any possible confusions and mistakes. I would like to learn VBA so I can one day write my own codes, but for now, I'm not even able to modify parts of the existing codes to suit my needs. So if you can help, please tell me where I should put the modified codes (that you come up with) to the existing codes. I appreciate all feedback & support I can get. Thank you all!
This comment was minimized by the moderator on the site
Hi Marshall, in the Workbooks.Open method, add in the option Local:=True.

i.e.
Set xWb = Workbooks.Open(xStrPath & "\" & xFile, Local:=True)
This comment was minimized by the moderator on the site
Hi Robert,
It's me again. It took me a while to actually have the time to figure out which part of the code the "Local:True" part should be added to. The result turned out great as the dates are no longer reversed. Thank you!
For anyone having the same problem, just change this line:
Set xWb = Workbooks.OpenXML(xStrPath & "\" & xFile)

To this:
Set xWb = Workbooks.Open(xStrPath & "\" & xFile, Local:=True)
This comment was minimized by the moderator on the site
Thank you very much Robert. Sorry I couldn't reply to you any earlier. I didn't get any notification until now. I will try this out and come back to you later to let you know if this works.
This comment was minimized by the moderator on the site
Hi - I'm using the import all csv files into one file listed above "Import Multiple Csv Files From A Folder Into A Single Sheet With VBA"- i'd like to define the folder it collects the data from without having to manually choose it. Can this be done? thanks - SW.
This comment was minimized by the moderator on the site
Hi, Scott W, I found a VBA code may can help you.
Option Explicit

Sub ImportCSVsWithReference()
'Author: Jerry Beaucaire
'Date: 10/16/2010
'Summary: Import all CSV files from a folder into a single sheet
' adding a field in column A listing the CSV filenames

Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("Sheet1")
Dim fPath As String: fPath = " C:\Users\DT168\Desktop\New folder\" 'path to CSV files, include the final \
Dim fCSV As String

If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Clear?") _
= vbYes Then wsMstr.UsedRange.Clear

Application.ScreenUpdating = False 'speed up macro

fCSV = Dir(fPath & "*.csv") 'start the CSV file listing

Do While Len(fCSV) > 0
'open a CSV file
Set wbCSV = Workbooks.Open(fPath & fCSV)
'insert col A and add CSV name
Columns(1).Insert xlShiftToRight
Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
'copy date into master sheet and close source file
ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbCSV.Close False
'ready next CSV
fCSV = Dir
Loop

Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
How to eliminate duplicate header and CSV file name column. Please do help....I have gone through several articles, but unfortunately all give same result.
This comment was minimized by the moderator on the site
Thank you. This site has been a big help. I have one issue I cannot figure out. I am trying to import multiple csv files into an excel separate sheets in excel and have each sheet renamed after the file name of the csv file. I know this was covered below for a txt file but I am working with csv files. Thanks in advance.
This comment was minimized by the moderator on the site
Hi! I used the code to merge multiple XML files into one, but unfortunately the columns got messed up. The 5 files being merged all had the same format. Is there anyway to fix this? I also was wondering if there was a way to get rid of the headers that are duplicated when the files are merged. Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations