Preskoči na glavno vsebino

Kako hitro paketno uvoziti več datotek csv / text / xml v Excelu?

V Excelu ste se morda povezali, da delovni zvezek shranite kot datoteko CSV, besedilno datoteko ali datoteko XML, vendar ste že kdaj poskusili uvoziti več datotek CSV / besedilo / XML iz mape v delovni zvezek ali delovni list? V tem članku predstavljam nekaj metod za hiter serijski uvoz.

Uvozite več besedilnih datotek iz mape v vsak delovni list delovnega zvezka z VBA

Uvozite več datotek csv iz mape na en list z VBA

Uvozite več datotek xml iz mape v en list z VBA

Uvozite ali združite več datotek xml / csv v list ali delovni zvezek s programom Kutools za Excel dobra ideja3

Izvozite vsak list kot csv / text / pdf v mapo z Kutools za Exceldobra ideja3


Če želite besedilne datoteke uvoziti iz mape v delovni zvezek, lahko za hitro obdelavo uporabite spodnji VBA.

1. Omogočite prazen delovni zvezek in pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. klik Vstavi > Moduliin prilepite VBA v Moduli okno.

VBA: Uvozi vse besedilne datoteke iz mape v delovni zvezek

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. Pritisnite F5 ključ ali Run za zagon VBA in v pogovornem oknu izberite mapo, iz katere želite uvoziti besedilne datoteke. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 1

4. In kliknite OK, in vsaka besedilna datoteka v izbrani mapi je bila uvožena na en delovni list aktivnega delovnega zvezka. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 2doc uvoz več CSV besedila xml 3

Preprosto združite več listov / delovni zvezek v en sam list ali delovni zvezek

Združevanje več listov ali delovnih zvezkov v en list ali delovni zvezek je lahko v Excelu resnično, vendar z Združite funkcijo v Kutools za Excel, lahko združite ducate listov / delovnih zvezkov v en list ali delovni zvezek, liste pa lahko združite v enega samo z več kliki.  Kliknite za 30-dnevno brezplačno preskusno različico vseh funkcij!
kombinirajte liste
 
Kutools za Excel: z več kot 300 priročnimi dodatki za Excel lahko brezplačno preizkusite brez omejitev v 30 dneh.

Če želite uvoziti vse datoteke csv iz mape na en list, lahko uporabite spodnjo kodo VBA.

1. Omogočite prazen delovni list in pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. klik Vstavi > Moduliin prilepite spodaj VBA v novo Moduli okno.

VBA: uvozite datoteke CSV iz mape na en delovni list

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. Pritisnite F5 tipko ali kliknite Run za zagon VBA in odpre se pogovorno okno za izbiro mape, iz katere želite uvoziti vse datoteke csv. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 4

4. klik OKin odpre se pogovorno okno, ki vas opozori, če izbrišete vsebino aktivnega delovnega lista pred uvozom, tukaj kliknem Da. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 5

Po kliku Da, vse datoteke csv v izbrani mapi se uvozijo v trenutni list in podatke iz stolpca A postavijo na desno. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 6doc uvoz več CSV besedila xml 7

Nasvet: Če želite datoteke CSV postaviti vodoravno na delovni list, lahko uporabite spodaj 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 uvoz več CSV besedila xml 8


Če želite uvoziti vse datoteke XML iz mape na en list, lahko uporabite spodnjo kodo VBA.

1. Izberite prazen list, kamor želite umestiti uvožene podatke, in pritisnite Alt + F11 tipke za omogočanje Microsoft Visual Basic za aplikacije okno.

2. klik Vstavi > Moduli, prilepite kodo VBA v Moduli okno.

VBA: Uvozite datoteke XML iz mape na delovni list.

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. klik Run gumb ali F5 tipko za zagon VBA in v pogovornem oknu izberite mapo, glejte posnetek zaslona:

doc uvoz več CSV besedila xml 9

4. klik OKin vse datoteke XML v izbrani mapi se uvozijo v aktivni list.


Če VBA ne poznate, skrbite, tukaj vam predstavljam priročno orodje - Kutools za Excel zate. S svojo močno Združite Pripomoček lahko hitro združite več datotek xml ali CSV v en delovni zvezek ali en Excelov list.

Kutools za Excel, z več kot 300 priročne funkcije, vam olajša delo. 

Po namestitvi Kutools za Excel, naredite spodaj:(Brezplačno prenesite Kutools za Excel zdaj!)

1. Active Excel in kliknite Kutools Plus > Združite. Oglejte si posnetek zaslona:
doc združiti 1

2. In v 1. korak kombiniranja pogovornem oknu izberite eno možnost ločevanja, kot jo potrebujete. Oglejte si posnetek zaslona:
doc združiti 2

3. klik Naslednji iti korak 2 kombinacije, Kliknite Dodaj , da dodate datoteke iz različnih map ali datoteke iz ene mape v Delovni zvezek lahko tudi določite liste, iz katerih želite kombinirati Delovni list seznam desnega odseka. Oglejte si posnetek zaslona:
doc kutools združujejo liste 3

4. klik Naslednji do zadnjega koraka Združite, in lahko določite možnosti kombiniranja.
doc kutools združujejo liste 4

5. klik Konec, odpre se pogovorno okno, ki vas opomni, da izberete lokacijo za shranjevanje novega kombiniranega rezultata. Oglejte si posnetek zaslona:
doc združiti 5

6. klik Shrani. Vsi dodani listi so bili združeni v nov en sam list.
doc združiti 6

Nasvet: z Združite, lahko tudi kombinirate več CSV datoteke oblikujte več map ali eno mapo v en list ali delovni zvezek.


Če želite izvoziti vsak list kot datoteko csv / text / pdf v mapo, Kutools za ExcelJe Razdeljeni delovni zvezek pripomoček vam lahko naredi uslugo.

po brezplačna namestitev Kutools za Excel, naredite spodaj:

1. Omogočite delovni zvezek, v katerega želite izvoziti njegove delovne liste, in kliknite Kutools Plus > Delovni zvezek > Razdeljeni delovni zvezek. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 10

2. V Ljubljani Razdeljeni delovni zvezek v pogovornem oknu lahko preverite imena listov, ki jih želite izvoziti, privzeto so preverjeni vsi listi in preverite Določite obliko shranjevanja in na spodnjem spustnem seznamu izberite obliko datoteke, ki jo želite shraniti. Oglejte si posnetek zaslona:

doc uvoz več CSV besedila xml 11

3. klik Split in izberite mapo, v katero želite shraniti razdeljene datoteke v Poiščite mapo pogovorno okno, glej posnetek zaslona:

doc uvoz več CSV besedila xml 12

4. klik OK, zdaj so vsi preverjeni listi izvoženi kot nova oblika datoteke v izbrani mapi.


Relativni članki:

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
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