This article will show you how to import data into excel workbook from local files, the local file format can be excel, csv, text, xml, html and pdf. It also show you how to export an excel workbook to those format files.
1. Import Data From Excel, CSV, Text Into New Excel File.
1.1 General Steps To Import Data Into Excel From Local File.
Below is general steps to import data into excel from local file, no matter what file type the local file is.
- Open Microsoft excel, click File —> New —> Blank workbook to create a new excel workbook file.
- Click Data —> New Query —> From File —> From Workbook / From CSV / From Text to open Import Data dialog.
- Browse to select the existing excel, csv or text file, click Import button to import it.
1.2 Import From Excel File Steps.
- If you import data from local excel workbook file, then it will popup the Navigator window, and it will list all the source tables and worksheets in left pane.
- Click one worksheet or table name in left pane, it will preview it’s content in right pane.
- Click Load button in above Navigator window, it will load the selected table or worksheet data to a new worksheet.
- If you click the down arrow besides the Load button, it will display a button list, include Load and Load To.
- The Load button in the list is same as step 3, the Load To button will open a popup window, then you can configure more detail about the load process in the Load To popup window.
- In the Load To popup window, you can choose load the table data ( check the Table radio button ) or just create a connection ( check the Only Create Connection radio button) from the source excel file table ( do not load the table data now ). You can choose where the data should be loaded to ( in current worksheet or a new worksheet ). You can also add the data to the Data Model.
1.3 Import From CSV, Text File Steps.
- If you import data from a local csv/text file, the popup window is different from above Navigator window. It will list all the csv/text file data in a table to let you preview.
- The Load and Load To button behaves same as import from excel file’s Navigator window.
1.3 Import From Html File Steps.
- You can refer article How To Import Data From Web Page Url Into Excel, the only difference is that you need to use local html file path ( file:////C:/users/…./abc.htm ) to replace the web page url.
1.4 Import From Pdf File Steps.
- You can use Adobe Convert PDF to Excel Online Service. It is very easy and total free.
- You can also use third party tools such as PDF Reader Pro Lite, it’s mac OS version provide feature for you to convert pdf content into an excel file.
- Open a pdf file in PDF Reader Pro Lite.
- Click File —> Convert To —> Excel menu item to convert it to excel file.
2. Export Excel Rows To Local File.
- Click File —> Export menu item in Microsoft excel.
- Then click Change File Type —> Save As button.
- It will popup Save As window. Select the target file type from the Save as type drop-down list. You can see there has excel, csv, text, xml, html ( web page ), pdf and more file type, choose the one which you need.
- Input a file name, click Save button to save it.
- When you save the excel file to html or csv, it may popup some dialog to tell you that some features will lost, you can ignore it and continue to save.
- If the worksheet row data has unicode character, you can select UTF encoding file type such as CSV UTF-8, Unicode Text etc.