See all How-To Articles

How to Convert a CSV File to XLSX (Excel) or Google Sheets

This tutorial will demonstrate how to convert a .csv file to Excel or Google Sheets.

 

Convert Intro

 

Excel has the ability to open or import both .csv and .txt files.

Opening a .txt or .csv File in Excel

1. In the Ribbon, select File > Open > Browse, and then select the text or .csv file to be opened.
The Text Import Wizard will open to the first of three steps.

 

Convert Step1

 

2. Depending on the data contained in the file selected, you can either choose Delimited or Fixed Width as the file type. If the data is separated with a character like a comma, semicolon, space, or tab, select Delimited. If the first row of data contains column headings, check My data has headers.

3. Click Next to go to the next step.

 

Convert Step2

 

4. In Step 2, select the type of delimiter contained in your text file – in this case, tab delimiter.
Then select the text qualifier. In some cases, the data may be enclosed in quote marks but for this example, (none) is selected as the text qualifier.

5. Click Next to go to the next step.

 

Convert Step3

 

6. The final step of the Text Import Wizard allows us to specify the data type contained in each column. Most of the time, Excel will be able to determine the data type automatically, and you can leave the selected Column data format on General.

7. Click Finish to import the data into Excel.

 

Convert Text File

 

Opening a .csv File in Excel From Windows Explorer

A .csv file is similar to a text file, but due to the fact it has the .csv extension, it is typically shown in Windows Explorer as associated with Excel.

 

Convert GetData Files Format

 

This means if you double-click on the file in Windows Explorer, the file will automatically open in Excel. As it is a comma delimited file, Excel will automatically separate the data into columns without the need to use the Text Import Wizard.

If a .csv file does not show the Excel icon, then it is not associated and will not open automatically in Excel, but that can be changed.

1. To associate a .csv file with Excel, right-click on the file in the Windows Explorer and then select Properties.

 

Convert GetData Files Properties

 

2. To the right of Opens with, select the Change button.

 

Convert OpensWith

 

3. Select Excel and then click OK.

 

Convert OpensWith Excel

 

4. All .csv files will now be associated to open with Excel. Double-click on the .csv file from the Windows Explorer to open the file in Excel.

 

Convert GetData Import

 

5. The delimiter in the file (in this case, comma) will automatically be selected, as will the file origin. The columns will be divided based on the data contained in the file as determined by Excel. Click Load to load the data into Excel.

Importing a Text file into Excel using Get Data

An alternative way of importing data into Excel from a text or .csv file is to use the Get and Transform Data function.

1. In the Ribbon, select Data > Get and Transform Data > From Text/CSV.

 

Convert GetData Ribbon

 

2. Select the file to be imported, and then click Import.

3. The file will appear in a dialog box with the delimiter already automatically selected, and the text divided by Excel according to the data stored in the .csv file. Click Load to load the data into Excel.

 

Convert LoadData

 

The data now appears in a new sheet in Excel. There are now two new tabs on the ribbon: Table Design and Query. A Queries & Connections pane appearing on the right-hand side of the screen. This shows us that the data is linked to the .csv file and if any data in the .csv file changes (external to Excel), then the data displayed in Excel would change too. This is especially useful when the data in Excel is linked to an external file such as a database.

 

Convert GetData LinkedData

 

4. To unlink the imported data from the external .csv file, in the Ribbon, select Table Design, External Table Data > Unlink.

 

Convert GetData Unlink

 

5. The data is imported into Excel as a table. To convert it to a standard Excel range, in the Ribbon, select Table Design > Tools > Convert to Range.

 

Convert GetData ConvertToRange

 

Both the Table Design and Query tabs on the Ribbon will then disappear.

How to Convert a .csv File to Google Sheets

1. From the File menu, select Import.

 

Convert GS ImportMenu

 

2. Click Upload, and then click on the blue “Select a file from your device” button to select the required file.

 

Convert GS Upload

 

3. The name of the file to be imported will be displayed under File with the default options available for importing automatically selected.

 

Convert GS ShowFile

 

4. To change the Import Location, click the drop down list under Import Location and select the option required.

 

Convert GS CreateNew

 

5. To change the Separator type, click the drop down list under Separator type and select the separator required.

 

Convert GS Delmiter

 

6. Keep “Convert text to numbers, dates and formulas” checked and then click Import Data to import the file into Google Sheets.