Advertisement Excel is the Do you really need Excel? Both desktop and online solutions have their advantages.
If you have trouble choosing between Excel and Google Sheets for managing your spreadsheets, let us help you decide., but sometimes you need to open a document that wasn’t formatted specifically for Microsoft programs. And other times, you’ll need to save your document in a different format so that people not using Excel can use it. Importing and exporting data in Excel seems like a complicated process, but it’s actually pretty easy once you’ve done it a couple times. Here’s what you need to know. How to Import Data into Excel No matter what kind of data you’re importing, you’ll start the process in the same way.
Hit File Import to get started. You’ll see this pop-up, letting you choose the format of the original file that you’d like to import: This dialog gives you the option to import comma-separated value (CSV), FileMaker Pro, HTML, and text files. If you want to import another type of spreadsheet file, your best bet is to export the spreadsheet in a different format from the original program. Most programs shouldn’t have any difficulty exporting to CSV or text. I’ll be using a CSV in this example because it’s a common data format, used for everything from research data to Facebook makes many aspects of your life more convenient. It's an easy way to stay in touch, it reminds you of your friends' birthdays, and it can sync your contacts and Facebook events to your.
Below you will find the step-by-step instructions to export an Excel workbook to the CSV format by using Excel's Save As command. In your Excel workbook, switch to the File tab, and then click Save As. Alternatively, you can press F12 to open the same Save As dialog. In the Save as type box, choose to save your Excel file as CSV (Comma delimited).
Launch Text Import Wizard Click Import, and you’ll see the Text Import Wizard. Excel’s Text Import Wizard does as much as it can to make this process easy, and for the most part, it works really well. Most of the time, you’ll just hit Next until the wizard disappears and your data shows up in the spreadsheet. As you can see in the image above, the text wizard determined that this file is delimited—each cell is separated by a comma. Because this is correct, we can just hit Next to move on with the import (if it’s not, read on for tips on fixed-width importing).
Set Data Delimiters Step two lets you choose a number of different options related to delimiting that will help you make sure your data gets imported correctly. First, you can choose your delimiter. The data that I’m importing here uses commas to separate cells, so I’ll leave Comma selected. Tab is also selected, and doesn’t have an adverse effect on this import, so I’ll leave it alone. If your spreadsheet uses spaces or semicolons to differentiate between cells, just select that option. If you want to split up the data on another character, like a slash or a period, you can enter that character in the Other: box. The Treat consecutive delimiters as one box does exactly what it says; in the case of commas, having two commas in a row would create a single new cell.
When the box isn’t checked, which is the default, that would create two new cells. The Text qualifier box is an important one; when the wizard imports a spreadsheet, it will treat some of the cells as numbers and some as text. The character in this box will tell Excel which cells should be treated as text. Normally, there will be quotes (” “) around text, so this is the default option. The text qualifiers will not be displayed in the final spreadsheet. You can also change it to single quotes (‘ ‘) or none, in which case all of the quotes will remain in place when they’re imported into the final spreadsheet. Set Data Formats Once everything looks good, hit Next to go to the final step, which allows you to set data formats for imported cells.
The default value for the Column data format is General, which converts data automatically to numerical, date, and text formats. For the most part, this will work just fine. If you have specific formatting needs, though, you can select Text or Date. The date option also lets choose the format that the date is imported in. And if you want to skip specific columns, you can do that too.
Each of these options is applied to a single column, or multiple columns if you shift-click to select more than one. It can take a long time to go through all of the columns this way if you have a giant spreadsheet, but it might save you time in the long run if all of your data is correctly formatted when you first import it.
The last option in this dialog is the Advanced menu, which lets you adjust the settings used for recognizing numerical data. The default uses a period as the decimal separator and a comma as the thousands separator, but you can change this if your data is formatted differently. After those settings are dialed to your liking, just hit Finish and the import is done. Use Fixed Width Instead of Delimited If Excel gets your data delimiting wrong, or you’re importing a text file with no delimiters, you can choose Fixed width instead of Delimited in the first step.
This allows you to separate your data into columns based on the number of characters in each column. For example, if you have a spreadsheet full of cells that contain codes with four letters and four numbers, and you’d like to split the letters and numbers between different cells, you can choose Fixed width and set the split after four characters: To do this, select Fixed width and click Next. In the following dialog, you can tell Excel where to split the data into different cells by clicking in the displayed data. To move a split, just click and drag the arrow at the top of the line. If you want to delete a split, double-click the line.
After selecting your splits and hitting Next , you’ll get the same options as you would have in the delimited import; you can select the data format for each column. Then hit Finish and you’ll get your spreadsheet.
In addition to importing non-delimited files, this is a good way to Mixing numbers and text in an Excel spreadsheet can introduce challenges. We'll show you how to change the formatting of your cells and separate numbers from text. From files you’re working with. Just save the file as a CSV or text file, import that file, and use this method to split it up however you want. Importing HTML is the same as importing CSV or text files; select the file, go through the same choices as above, and your HTML document will be transformed into a spreadsheet that you can work with (you might find this useful if you want to download HTML tables from a website, or if web form data is saved in HTML format).
Exporting Data from Excel Exporting data is much simpler than importing it. When you’re ready to export, hit File Save As (or use a Keyboard shortcuts can save you a lot of time! Use these Microsoft Excel shortcuts to speed up working with spreadsheets on Windows and Mac.), and you’ll be presented with a number of options.
Just choose the one you need. Here’s a breakdown of a few of the most common:.xlsx /.xls: standard Excel formats.xlt: Excel template.xlsb: an Excel format written in binary instead of XML, which allows for the saving of extremely large spreadsheets faster than standard formats.csv: comma-separated value (as in the first import example used above; can be read by any spreadsheet program).txt: a number of slightly different formats that use tabs to separate the cells in your spreadsheet (when in doubt, select Tab Delimited Text instead of another.txt option). When you select a format and hit Save, you may get a warning that looks like this: If you’re looking to save your file as something other than.xlsx or.xls, this is likely to happen. Unless there are specific features that you really need in your spreadsheet, just hit Continue and your document will be saved. One Step Closer to Excel Mastery For the most part, people just use Excel-formatted spreadsheets, and it’s really easy to open, modify, and save them. But every once in a while you’ll get a different kind of document, like one extracted from the web or generated in a You'll be pressed to find an occupation that doesn't require word or number processing of some sort. And you may wonder, is Microsoft Office really the best solution?
Here are your options. Knowing how to import and export different formats can make working with these kinds of sheets a lot more convenient. Do you import or export Excel files on a regular basis? What do you find it useful for? Do you have any tips to share or specific problems that you haven’t found a solution for yet? Share them below!
Explore more about:,.
Recently I was tasked to create a pipe delimited file from an Excel Spreadsheet, a simple task you would expect. On first glance it was not as simple I thought. I use Excel 2010 and was expecting to be able to do something like ‘save as’ and select the file type ‘CSV (Comma delimited)(.csv)’ and then have the option to select the delimiter like when importing a csv file. After a little head scratching and a quick google, I found I had to make a global setting change in the control panel (Yes, that’s right). So in Windows 7, I found you must do the following to change the delimiter:. Make sure Excel is closed.
Navigate to control panel. Select ‘Region and Language’. Click the ‘Additional Settings’ button. Find the List separator and change it from a comma to your preferred delimiter such as a pipe ( ).
Click OK. Click OK. Exit Control panel. Open the Excel file you want to export to a pipe delimited file.
Select File, Save As. Change the ‘Save as type’ to ‘CSV (Comma delimited)(.csv)’. Change the name and file extension if you want, by default stays as csv even though a different delimiter. Click Save.
Click OK. Click Yes And that is it, easy right?
Hmmmm – if you only wanted to change the delimiter once not an easy/simple process. Ok, it is not as bad as I make out. For example, if you want to have tab delimiter or fix width (space) there are save as file type options available, however, given how easy the import wizard is I would have thought they would have done something similar. Have I missed something or has google put me wrong? If so let me know Barry, Preventer Of Chaos. Barry blogs about how to stop chaos in your systems Read to find out how to filter based on the structure of information or Barry’s blogs.
Visit our to find out how our courses can help learn how to get the best from your data. Hi Barry, It doesn’t work for me. ? I did exactly what you explained, even rebooted my computer to hope the changes would have any effect, but even after a reboot, the saved CSV will still have; instead of,. The Region and Language settings are correct, so the list separator is configured with a, not a;. Even when I open up a new Excel window and a Blank Workbook, fill in some data and save as CSV (Comma delimited) it still is being saved with;.
I am very sad because things aren’t working the way I would like to. Best regards, Erik. Hi Raman, Barry’s colleague Shaun here. Welcome to the wonderful world of “text encoding”!
In general, Microsoft favours character sets that only include Latin characters, and I have faced the same problems working with Russian characters that you face here with Japanese. Unfortunately successive Office versions have hidden the solution further and further from view! On Windows: – When you have your Excel file ready, save it in Excel format somewhere safe, so you can return to it if needed. – Then, go File - Save As, and choose a location for your CSV. – In the “Save as type” box under the file name, select “CSV (Comma delimited) (.csv)”, then click the “Tools” button next to “Save”, and select “Web Options”.
– Choose the “Encoding” tab and change the option under “Save this document as” to either “Unicode (UTF-8)” or one of the Japanese options. You might need to experiment a little! On Mac: the options above don’t exist, but see my next comment on this post! Good luck, Shaun.
If you try my solution to Raman’s problem on Mac, you’ll find there is no (longer) support for saving CSVs in other encodings. For the other problems described above, including Barry’s original use case, the solution I use is LibreOffice or OpenOffice. – Install LibreOffice or OpenOffice (both are free and open source) – Open your Excel or CSV file, make sure it looks right – “Save as”, choose CSV, and check the “Edit filter settings” box – Choose encoding (character set), field delimiter (comma, pipe, or whatever else you want), and text delimiter! Nobody likes an answer that involves installing new software. But I think the outpouring of CSV-related issues in other folks’ comments support my point above that we would really like these features but they have been taken away (or hidden) in successive redesigns of Office.
Fortunately, if you do a lot of work with other languages or exotic file formats, LibreOffice/OpenOffice can help. Good Information! Note: If you have changed the list separator from, to then on manually saving an excel file to CSV will generate a pipe delimited CSV. However, if you have a code(macro) which is saving an excel to csv format then in the SAVEAS method you need to add a parameter “Local” and set its value to True. If you do not set this parameter, the default value for this parameter will remain false and your code will not refer to the Windows List separator changes at all. Hoping this information helps.