How to quickly batch import multiple csv/text/xml files in Excel?In Excel, you may have tied to save a workbook as csv file, text file or xml file, but have you ever tried to import multiple csv/text/xml files from a folder into a workbook or a worksheet? In this article, I introduce some methods to quickly batch import them.Easily Combine multiple sheets/Workbook into one Single sheet or WorkbookTo combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only. To import text files from a folder to a workbook, you can use below VBA to quickly handle it.1. Enable a blank workbook, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.2. Import multiple csv files from a folder into a single sheet with VBATo import all the csv files form a folder into a single sheet, you can use below VBA code.1. Enable a blank worksheet, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.2. Tip: If you want to place csv files horizontally in a worksheet, you can use below VBA.
Sub ImportCSVsWithReferenceI'UpdatebyKutoolsforExcel20151214Dim xSht As WorksheetDim xWb As WorkbookDim xStrPath As StringDim xFileDialog As FileDialogDim xFile As StringDim xCount As LongOn Error GoTo ErrHandlerSet xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)xFileDialog.AllowMultiSelect = FalsexFileDialog.Title = 'Select a folder Kutools for Excel'If xFileDialog.Show = -1 ThenxStrPath = xFileDialog.SelectedItems(1)End IfIf xStrPath = ' Then Exit SubSet xSht = ThisWorkbook.ActiveSheetIf MsgBox('Clear the existing sheet before importing?' After installing Kutools for Excel, please do as below:1. Active Excel, and click Enterprise Combine.
In the Import dialog box, click the option for the type of file that you want to import, and then click Import. In the Choose a File dialog box, locate and click the CSV, HTML, or text file that you want to use as an external data range, and then click Get Data.
See screenshot:2. And in the step 1 of Combine dialog, choose one separation option as you need. See screenshot:3. Click Next to go to step 2 of the Combine, click Add to add files from various folders or files from one folder to Workbook list, and also you can specify the sheets you want to combine from Worksheet list of right section. See screenshot:4. Click Next to the last one step of Combine, and you can specify the options.1) Specify the title row number2) Decide if insert worksheet information3) Skip blank range or not4) Specify paste option5.
Click Finish, a dialog pops out to remind you select a location to save the new combined result. See screenshot:6.
All adding sheets have been combine into a new single sheet.Tip: With Combine, you also can combine multiple CSV files form multiple folders or one folder into one sheet or workbook. Combine Multiple XML/CVS Files into One Sheet/Workbook. Export each sheet as csv/text/pdf to a folder with Kutools for ExcelIf you want to export each sheet as csv/text/pdf file to a folder, Kutools for Excel’s Split Workbook utility can do a favor for you.After Kutools for Excel, please do as below:1. Enable the workbook you want to export its worksheets, and click Enterprise Workbook Split Workbook. See screenshot:2. In the Split Workbook dialog, you can check the sheet names you need to export, in default all the sheets are checked, and check Specify save format and select the file format you want to save as from below drop down list.
See screenshot:3. Click Split and select a folder that to save the split files in the Browse for Folder dialog, see screenshot:4. Click OK, now all the checked sheets are exported as new file format in the selected folder. Export Each Sheet as Single XML/CSV/TXT/PDF File. Quickly convert multiple XLSX. Files to XLS or PDF.
Files in once timeIn many cases, you may want to convert between XLSX and XLS, or convert a workbook to PDF file, but in Excel, you only can convert one workbook once time, have you ever imaged to convert multiple workbooks at once time? With Kutools for Excel's, you can quickly convert multiple workbook to pdf/97-2003 workbook/2007 or higher workbook at once and save them in a folder, at the same time, a converted report is generated!Relative Articles:. The Best Office Productivity Tools Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%. Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails. Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range. Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns. 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:, 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 - ), while the ones that are too obvious, remain unchanged (i.e ).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!
Hi, Scott W, I found a VBA code may can help you.Option ExplicitSub ImportCSVsWithReference'Author: Jerry Beaucaire'Date: 'Summary: Import all CSV files from a folder into a single sheet' adding a field in column A listing the CSV filenamesDim wbCSV As WorkbookDim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets('Sheet1')Dim fPath As String: fPath = ' C:UsersDT168DesktopNew folder' 'path to CSV files, include the final Dim fCSV As StringIf MsgBox('Clear the existing sheet before importing?' , vbYesNo, 'Clear?'
) = vbYes Then wsMstr.UsedRange.ClearApplication.ScreenUpdating = False 'speed up macrofCSV = Dir(fPath & '.csv') 'start the CSV file listingDo While Len(fCSV) 0'open a CSV fileSet wbCSV = Workbooks.Open(fPath & fCSV)'insert col A and add CSV nameColumns(1).Insert xlShiftToRightColumns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name'copy date into master sheet and close source fileActiveSheet.UsedRange.Copy wsMstr.Range('A' & Rows.Count).End(xlUp).Offset(1)wbCSV.Close False'ready next CSVfCSV = DirLoopApplication.ScreenUpdating = TrueEnd Sub. Hi there, thank you very much for those code samples! The first one (VBA: Import all text files from a folder into a workbook) is almost exactly what I need.
I would like, however, to select which files to import and not automatically import all the text files in any given folder. I would also like the exiting contents of my target sheets to be overwritten by the contents of the text files. (With the code above, the existing contents is shifted to the right instead of being replaced.)How do I go about that?Thanks in advance!
Excel spreadsheets are great for storing large amounts of data that would be unmanageable elsewhere. But what happens when you need to transfer that data somewhere else. Hundreds, sometimes even thousands of cells crammed with information… that’s a lot of data to manually re-type!
Extensible Markup Language, or XML, is an easy to use, read, and format document markup language that can easily store your Excel files and help you transfer them to other applications.
In this guide, you’ll learn how to convert Excel to XML the quick and easy way. All you need is an Excel spreadsheet, an XML schema, and a rudimentary understanding of Excel, which you can learn in this Excel training course for beginners.
Converting Excel to XML
The first thing you need is, of course, an Excel spreadsheet. Below, we have an example spreadsheet with six columns and 26 rows of data. The columns separate the last names, first names, ages, majors, GPAs, and school year of each student listed in the spreadsheet.
Step 1 – Create your Excel spreadsheet
This is a very basic Excel document and lacks advanced functions. It is a simple spreadsheet storing numerous types of data, separated by columns. It keeps track of 25 college students, and their last names, first names, ages, majors, GPAs, and school years.
Step 2 – Create your XML schema
After you have your Excel document setup, you need an XML schema. An XML schema is the document that defines an XML file’s structure. You can set one up easily in Notepad using the following code:
You can change the name of the <TAGNAME> tags into the names of your columns. They do not have to be the exact name of the columns, as long as they’re in the correct order. You only need to fill out a few examples, manually, but we can get Excel to do the rest for us later.
Here’s an example XML schema for our Excel spreadsheet:
As you can see, <TAGNAME> and <TAGNAME2> and so on have become <LastName> and <FirstName> and so on, after the columns in our data set.
Also take note of the <student-data> tags that hold the contents of each <record> tag. <student-data> should be renamed according to the name of your Excel spreadsheet. In this example, our Excel workbook with the student data is called, fittingly, student-data.xlsx, and so our tags should be <student-data>.
Don’t forget to name your XML schema with a .xml at the end. In this example, our schema is simply called schema.xml, if you’d like to follow along.
If you want to know more about XML documents, how they work, and what they’re useful for, check out this introduction to XML course. You can also get started with this beginners XML tutorial class.
Step 3 – Enable the Developer tab
If the Developer tab is not available, you need to enable it in the options. Click on File, and then go all the way down to Options. Click on the Customize Ribbon section, and in the column on the right, there should be a list of tabs called Main Tabs that you can check or uncheck.
Find where it says Developer, and make sure the box is checked. Then click OK. The Developer tab should now be up top, to the right of the View tab.
If you’re having trouble navigating Excel and its various options, it would be beneficial to take a basic Excel course for beginners, and leanr your way around.
There are also plenty of advanced Excel training courses for those who have a basic understanding, but need experience with the more complicated elements.
Step 4 – Open the XML Source pane
Now that you’ve brought up the Developer tab and clicked on it, you should see a number of sections called Code, Add-Ins, Controls, and so on.
Find the XML section between Controls and Modify, and click on the large button that reads Source. This will bring up the XML Source task pane to the right of the screen, where you’ll have the option to manage XML maps. That’s what we want to do next.
Step 5 – Add an XML Map
In the XML Source task pane, click the XML Maps option at the very bottom right. A window will pop up. Click Add to add a new XML schema, find your schema.xml file in the browser, and click OK.
Now that your XML file is added, click OK again and return to the XML Source task pane.
Step 6 – Drag and drop the XML elements into your spreadsheet
Each of your columns should appear as a new element in the record tree created by the XML Source task pane: LastName, FirstName, Age, and so on. Drag each element individually into its appropriate column, starting at Row 1.
Converted columns should turn blue. Do this until all of your columns have been fitted with the XML elements.
Step 7 – Export your XML data
Once you’ve finished converting each column into an XML element, return to the Developer tab, find the XML section again, and click Export. Save your XML file, and then open it in Notepad to see your results:
… and so on, until the end of our data set. That’s right! Excel was able to format all of this information automatically, so you didn’t have to.
This trick can come in handy with large data sets of over a hundred or thousand cells, but even if you have a couple dozen and need to save time.
You’ve done it! You’ve successfully converted Excel to XML. This was a very simple example, though, and in the future you might find yourself facing some tougher scenarios.
Learn how to program in XML with this beginners course, and learn Excel from the ground-up with this introductory Excel training course.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |