We know finance people love Excel. And when you use Microsoft Dynamics GP you will often import and export Excel files.
Let’s say you receive a customer.csv report when we are implementing your Dynamics GP system and you need to review the file. Or you get a Historical Transactions 2016.txt report that you need to review prior to our uploading into your new system.
How are you going to open these so that they come up clean and readable?
We asked an expert at
HOW TO IMPORT A CSV FILE INTO EXCEL (By Spreadsheeto)
Beyond mere spreadsheet type files, Microsoft Excel can handle importing text and comma separated values (CSV) files as well. Importing a CSV file into Excel can allow you to view the file in a way that allows you to make sense of the data included in the original file. In this short article, we are going to look at the method for importing a CSV file into Excel and how to make sense of it.
By importing a CSV file into Excel, you will be able to better view the data and edit if necessary. Then you can export and preserve your changes. The illustrations and examples here will be performed using Microsoft Excel 2016.
STEPS FOR OPENING CSV FILES IN EXCEL
If you simply want to view the contents of a CSV file in Excel, you can simply open the file. Excel will automatically format it into spreadsheet form based on its embedded formatting.
Once you have the Excel application open, on the File tab click on Open. Then navigate to the folder location of your CSV file.
The first thing you will notice is that your file does not appear in the directory location selected. Note in the lower right corner of the ‘Open’ dialog
Find ‘Text Files’ and select. Once you do this, your file will now be visible in the ‘Open’ dialog.
Notice in the ‘Type’ column for this file that is says ‘Microsoft Excel Comma Separated Values File’. Now that the file name shows in the ‘File name’ text box, we can click on the ‘Open’ button. Then the file will now appear in spreadsheet form just like a typical xls or xlsx file.
STEPS FOR IMPORTING CSV FILES AS TEXT FOR EDITING
If you need to open the contents of a CSV file in Excel to edit data types that you want to save back as CSV, there is a bit of a different route. For this method, let’s open a new file and navigate to the ‘Data’ tab and expand the ‘Get External Data’ dropdown.
Select ‘From Text’. Then the ‘Import Text File’ dialog that looks like the ‘Open’ dialog we saw previously will appear. We select our CSV file and click on the ‘Import’ button.
This will open the ‘Text Import Wizard’ which allows control over how you want the data in the CSV file to appear in Excel.
You have several options in this step of the wizard. For this case, we will go with the default ‘Delimited’ for data type. We will start our import at row 1 and check the box that says ‘My data has headers’. However, keep in mind that this is part of the flexibility of this process. If you did need to import only a portion of the data without headers, it is possible.
Now we can click on ‘Next’. Notice that in the preview window, that the data structure and appearance is still in its original state.
But at this point, we will unselect Tab in the ‘Delimiters’ box and instead check the box next to Comma. Bear in mind that you could also check the box by ‘Treat consecutive delimiters as one’ if necessary. In our case, we do not need to do so. And we will also leave ‘Text qualifier’ unchanged. Now the preview window shows us the effect by sorting the data into columns based on the comma delimiter in the original file.
Click ‘Next’. This brings us to the third step of the import wizard. This is the step where you can now make edits to data types and make custom changes that you can save to the CSV file per your data import needs. There are advanced options in this step. Most notably, you can adjust the data types column by column and even exclude some columns.
The ‘Text Import Wizard’ offers a lot of flexibility and control over the data you import from the original file. Now we can click ‘Finish’
Finally, we will import the data into our current worksheet in cell A1. Note that we have not opened the actual CSV file. We have simply imported the data, and we can now make changes and export while preserving the integrity of the original CSV file.
If Excel does not offer the required format options for a column in the wizard, you can change data formats once you have imported the data. For example, in our case we may want to change the values in column H from ‘General’ to ‘Currency’.
If you have a CSV file that simply needs to be viewed, opening the file is a simple option. But if you need to make changes to data types and formatting, using the import option is the method that will allow you to do that.
This guide was written by Microsoft Office Specialist, Kasper Langmann. Kasper Langmann is the co-founder of Spreadsheeto, the place to learn Excel efficiently. If you liked this tutorial, make sure to check out
By CAL Business Solutions and Spreadsheeto