Convert CSV to Excel: the right way to import CSV information into Excel spreadsheets

On this article you can find Three fast methods to transform CSV to Excel. Additionally, you will discover ways to import a number of CSV information in a single workbook and the right way to deal with the circumstances when some information from a CSV file is not appropriately displayed in an Excel sheet.

Final week we began to analyze the specificities of the comma-separated values format (CSV) and other ways of changing Excel information to CSV. At present, we’re going to do the other – import CSV to Excel.

This text will present you the right way to open CSV in Excel and import a number of CSV information at a time. We can even red-flag doable pitfalls alongside the way in which and recommend the simplest options.

Methods to convert CSV to Excel

If it’s essential switch some information out of your firm’s database to an Excel spreadsheet, exporting the database to a CSV file after which importing the CSV file to Excel is the primary possibility that involves thoughts.

There exist Three methods of changing CSV to Excel: you’ll be able to open a .csv file straight in Excel, double-click on the file in Home windows Explorer, or import CSV into Excel as exterior information. Under I’ll present the detailed steerage on these strategies and level out the strengths and limitations of every:

Methods to open CSV file in Excel

For those who created a CSV file in one other program, you’ll be able to nonetheless open it as an Excel workbook within the regular approach through the use of the Open command.

1. Along with your Microsoft Excel open, head over to the File tab and click on Open.
2. The Open dialog field seems and you choose Textual content Recordsdata (*.prn,*.txt,*.csv) from the drop-down record within the decrease right-hand nook.
3. Browse for the CSV file and open it as regular by double-click.In case you are opening a .csv file, Excel will open it right away and show the information in a brand new workbook.You probably have chosen a textual content file (.txt), Excel will begin the Import Textual content Wizard.

   Word. When Microsoft Excel opens a .csv file, it makes use of your default information format settings to grasp how precisely to import every column of the textual content information. In case your information incorporates one of many under characters or entries, then use the Import Textual content Wizard as a substitute:

   • totally different delimiters are utilized in your csv file;
   • the csv file incorporates totally different date codecs;
   • you change numbers with main zeroes and need to protect main zeroes in an Excel sheet;
   • you need to see a preview of how your CSV information will likely be imported into Excel;
   • you might be on the lookout for extra flexibility typically.

To get Excel to launch the Import Textual content Wizard, you’ll be able to both change the file extension from .csv to .txt earlier than opening the file, or import CSV to Excel as defined additional.

Methods to open a CSV file utilizing Home windows Explorer

That is the quickest solution to open CSV in Excel. In Home windows Explorer, simply double click on a .csv file and have it open as a brand new workbook.

Nevertheless, this technique works solely you probably have Microsoft Excel set because the default program for .csv information. If that’s the case, then you will note the acquainted inexperienced Excel icon  displayed subsequent to the .csv file’s title in Home windows Explorer.

If Excel is just not the default program, you’ll be able to set it as such on this approach:

1. Proper-click any .csv file in Home windows Explorer, and choose Open with… > Select default programfrom the context menu.
2. Click on Excel (desktop) beneath Really useful Applications, be sure the “At all times use the chosen program to open this sort of file” is chosen and click on OK.

Methods to import CSV to Excel

Utilizing this technique you’ll be able to import information from a csv file into the present or a brand new Excel worksheet. Not like the earlier two strategies, it doesn’t merely open CSV in Excel however actually converts a .csv file to .xlsx (in Excel 2016, 2013, 2010 or 2007) or .xls (in case of Excel 2003 and decrease).

1. With the wanted spreadsheet open, click on the cell the place you need to import the information from a .csv or .txt file.
2. On the Knowledge tab, within the Get Exterior Knowledge group, click on From Textual content.
   
3. Browse for the csv file you need to import, choose it and click on the Import button (or just double click on the .csv file).
   
4. The Textual content Import Wizard will seem and also you observe its steps.Earlier than we transfer additional, please have a fast have a look at the screenshot under that exhibits the unique CSV file and the specified end in Excel. Hopefully, this may show you how to higher perceive why we select this or that setting within the instance that follows.
   
   • Step 1. Select the file kind and the row quantity to start out importing the information. Sometimes, you select Delimited and row 1, respectively. The preview window within the decrease a part of the wizard exhibits the primary few entries of your CSV file.
     
   • Step 2. On this step, you choose a delimiter(s) and textual content qualifier. Delimiter is the character that separates values in your .csv file. In case your CSV file makes use of some character aside from accessible within the pre-defined record, choose the Different test field after which kind your customized character. In our instance, we specify Tab and Comma since we wish every product (separated by tabs) to start out with a brand new row and the product particulars corresponding to IDs and gross sales numbers (separated by commas) to be imported in separate cells.Textual content qualifier is the character that wraps the values in your textual content file. All textual content enclosed within the textual content qualifier symbols, e.g. “text1, text2” will likely be imported as one worth, even when the textual content incorporates a delimiter you specified.On this instance, we have now chosen a comma because the delimiter and a citation mark because the textual content qualifier. Because the end result, the entire numbers with a decimal separator (which can be a comma in our case!) will likely be imported in a single cell, as you’ll be able to see within the preview part under. If we didn’t specify the citation mark because the textual content qualifier, then all of the numbers with decimal separators could be imported into two adjoining cells.
   • Step 3. In case you are proud of the Knowledge preview, then click on the End button.
   Tip. If a couple of consecutive comma or different delimiter characters could happen in your CSV file, then test Deal with consecutive delimiters as one test field to forestall empty cells.
5. Select the vacation spot for the imported information, both an present or a brand new worksheet, and click on OK to complete importing your CSV file to Excel.
   
   Tip. You may click on Properties… to set some superior choices corresponding to refresh, structure and formatting for the imported information.
   
   Word. In case your CSV file incorporates some numerical information or dates, Excel could not convert these values appropriately. To vary the format of the imported information, choose the problematic column(s) in Excel, proper click on and select Format cells from the context menu.

Convert CSV to Excel: issues and options

The CSV format has been round for over 30 years, however however its lengthy historical past it has by no means been formally documented. The title CSV (comma-separated values) implies the usage of comma to separate information fields. However that is in idea. De facto, many so-called “CSV” information use different characters to separate information, corresponding to:

• Tabs – TSV information (tab-separated values)
• Semicolon – SCSV information (semicolon separated values)

Some implementations of CSV information wrap information fields in single or double citation marks, whereas others require a Unicode byte order mark (BOM), e.g. UTF-8, for proper Unicode interpretation.

This lack of ordinary spawns varied issues chances are you’ll be confronted with when attempting to convert an Excel file to CSV and particularly when importing a .csv file to Excel. Let’s examine these points starting with the commonest one.

CSV file is incorrectly displayed in Excel

Signs. You are attempting to open a .csv file in Excel and the entire information is displayed within the first column.

Trigger. The basis of the issue is totally different separators set in your Home windows regional and language settings and the csv file. In North America and another international locations, the default Checklist Separator is a comma. Whereas in European international locations the comma (,) is reserved because the Decimal Image and the Checklist Separator is ready to semicolon (;).

Options. There are a number of doable options for this drawback, you’ll be able to rapidly look via the suggestions under and select the one most suited on your specific activity.

1. Specify the appropriate separator straight within the CSV file. Open the CSV file with any textual content editor (even the standard Notepad will do) and add the under textual content within the first line. Word, it ought to be a separate line earlier than any information:
   • To separate with comma: sep=,
   • To separate with semicolon: sep=;

   As you’ll be able to guess, you’ll be able to set every other customized separator in the identical approach, merely kind it after the equality signal.

2. Select the wanted separator in Excel. In Excel 2016, 2013 or 2010, go to the Knowledge tab > Knowledge Instruments group > Textual content To Columns.
   The Convert Textual content to Column wizard, select the Delimited file kind advert click on Subsequent. Then choose the wanted separator within the subsequent step and click on End.
   
3. Change the .csv extension to .txt. Opening a .txt file in Excel will begin the Textual content Import Wizardand you may select any delimiter as mentioned in importing a csv file to Excel.
4. Open a semicolon delimited CSV file with VBA.Here’s a pattern VBA code to open a semicolon delimited CSV file in Excel. Although the code was written a number of years in the past for older Excel variations (2000 and 2003), in case you are fairly comfy with VBA, you will not have issues with updating it or modifying for comma delimited CSV information.
   Word. All the options above change the separator for a given CSV file solely. For those who’d quite change the default separator as soon as and for all, proceed with resolution 5.
5. Change separators in Area and Language settings.Click on the Begin button and open the Management Panel, then click on Area and Language > Further Settings. The customise Format dialog window will open and also you select a dot (.) because the Decimal image and a comma (,) because the Checklist separator.Click on OK twice to shut two dialog home windows and you might be completed. From now Microsoft Excel will open and show all CSV (comma separated) information appropriately.
   Word. Setting new decimal and record separators in Management Panel modifications the default separator characters for all applications in your laptop, not solely Microsoft Excel.

Main zeros are misplaced when opening a CSV file in Excel

Symptom. Your .csv file incorporates values with main zeros and people zeros are misplaced once you open the CSV file in Excel.

Trigger. By default, Microsoft Excel shows SCV information within the Normal format, which cuts off main zeros.

Answer. As an alternative of opening a .csv file in Excel, run the Textual content Import Wizard as defined in Importing CSV to Excel with the intention to convert CSV to Excel.

In step Three of the wizard, choose the column(s) that incorporates values with main zeros and alter the column’s format to Textual content. This may covert your CSV file to Excel maintaining all zeros in place.

Excel converts some values to dates when opening a CSV file

Symptom. Some values in your .csv file seem like dates and Excel robotically converts such values from textual content to a date.

Trigger. As talked about above, Excel opens CSV information within the Normal format and converts values that occur to resemble dates from textual content to a date. For instance, in case you are opening a .CSV file containing person logins, the entries just like apr23 will likely be transformed to dates in Excel.

Answer. Convert CSV to an Excel file by working the Textual content Import Wizard (Knowledge tab > From Textual content). In step 3, choose the column(s) with information that look sufficient like dates and alter the column’s format to Textual content.

In case you are on the lookout for the other, i.e. you need the values in a sure column to be transformed to dates in Excel, then set the format of that column to Date and select the specified date format from the drop-down record.

Methods to import a number of csv information into Excel

As you most likely know, Microsoft Excel permits opening a number of CSV information utilizing the Open command.

1. You click on File > Open and choose Textual content Recordsdata (*.prn, *.txt, *.csv) from the drop down record within the decrease right-hand nook.
2. To pick out adjoining information, click on the first file, maintain down the Shift key, after which click on the final file. Each of the clicked information in addition to all of the information in-between will get chosen.To pick out a number of non-adjacent information, maintain down the Ctrl and click on every .csv file you need to open.
3. With a number of CSV information chosen, click on the Open button.
   This technique is easy and fast and we may name it good however for one small factor – it opens every CSV file as a separate Excel workbook. In apply, switching forwards and backwards between a number of Excel information could be fairly inconvenient and burdensome.

Now let me present you how one can import a number of CSV information into one Excel workbook, putting every .csv file on a separate sheet or consolidating all of the information in a single spreadsheet.

Importing a number of CSV information in a single Excel workbook

To import a number of .csv information into one workbook, we’re going to use the Consolidate Worksheets Wizard. This add-in works with all variations of Excel 2016, 2013, 2010, 2007 and 2003. The analysis model can be accessible and you may obtain it proper now to observe this instance extra carefully. The set up will take you simply a few minutes.

1. Go to the Ablebits Knowledge tab on the Excel ribbon and click on the Consolidate Worksheets icon.
   
2. Choose the CSV information you need to import in Excel. You probably have the information open, the wizard will show their names within the dialog window. In any other case, click on the Add information… button within the decrease right-hand nook.
   Choose the information as you normally do in Home windows Explorer (maintain Shift to pick out contiguous information or Ctrl to pick out non-contiguous information). The end result will look just like what you see the screenshot under, and also you click on Subsequent.
3. Select how precisely you need to import the chosen CSV information to Excel. As you’ll be able to see within the screenshot under, you will have a number of selections. Sometimes, you click on the final open that imports the entire chosen CSV information right into a single workbook, every file on a separate sheet.
   
   Tip. In case you are not sure which possibility to decide on, click on the i icon  proper to the choice in query and you will note a mockup of the imported information. For instance, to consolidate information from a number of CSV information in a single worksheet, select the second possibility – “Copy information from the chosen ranges to at least one worksheet“.

   

4. Within the subsequent step, you resolve whether or not you need to import all the information or values solely. When importing .csv information, you sometimes go forward with the default setting “Paste all” and easily click on Subsequent.
   
5. The very last thing so that you can do is to decide on the vacation spot for the imported CSV information. And once more, you proceed with the default setting because the New workbook is the one accessible possibility.
   
6. Click on End, look forward to a number of seconds and get the specified end result – a number of CSV information imported into one Excel workbook. Quick and painless, is not it?

As you perceive, this was simply one of many many doable purposes of the Consolidate Worksheets Wizard. You could find extra about this Excel add-in on its homepage.

Hopefully, now you’ll be able to convert any CSV information to Excel with ease. You probably have any questions or have run into any points, do not hesitate to drop me a remark. And thanks on your endurance everybody who has learn this lengthy tutorial to the tip! : )