Excel import csv as rows not columns

To open a comma-delimited (CSV) file properly, use Excel’s Data Import from Text feature to open the import wizard and set all columns as text.

Table of Contents Show

Tips for transposing your data
How do I convert a CSV file to rows in Excel?
How do I import a CSV file into a row?
How to import a text file into Excel and change columns to rows?
Why is my CSV file in columns?

If you click the file and allow Excel to open it automatically, the columns might be formatted improperly.

To convert CSV file to Excel columns, actually, you can handle it through three different methods. In this article, I will talk about these two methods for you.

Open CSV file in Excel

Insert CSV file in Excel

Open CSV file in Excel

The most usually used method must be opening CSV file directly through Excel.

1. Click File > Open > Browse to select a CSV file from a folder, remember to choose All Files in the drop-down list next to File name box. See screenshot:

2. Click Open, and the CSV file has been opened in the Excel.

Tip. If you have Kutools for Excel, you can apply its Insert File at Cursor utility to quickly insert a CSV file or text file or Excel file or PRN file to the cell which cursor places.

Note：

But sometimes, there are some data with special formats such as the leading zeros in the CSV file, these special formats may be lost if the CSV file is opening through Excel as below screenshot shown. In this case, you need to use below method.

Insert CSV file in Excel

To keep the format of the contents CSV file, you can use the From Text utility to import the CSV file.

1. Select a cell which you will insert the CSV file and click Data > From Text. See screenshot:

2. In the Import Text File dialog, select a file you need to import. See screenshot:

3. Click Import, and a dialog of Text Import Wizard opens, check Delimited option and click Next to go to step 2 of the Wizard, and then check Comma. See screenshot:

4. Click Next and select the column with special formats in the Data preview and check a format for it in Column data format section, then one by one to format the columns, also, you can skip the columns you do not need by checking the Do not Import column (skip) option. See screenshot:

I have had problems with CSV text data that contains line breaks. For example, exported spread sheet to CSV with heading cells that had line breaks. Ideally there should be an export/import CSV type variant that decodes escape chars, so it understand escaped chars like \n; \r in a quoted string. I have not seen any easy way to do this in the old version of Excel I use and not expert on latest versions of excel. I usually have to run a custom macro to convert text columns for that kind of text data.

Is this any better in latest versions of Excel?

I have seen CSV exports that just put a line break in the middle of the text data item. This CSV data is difficult to import. Some CSV importers (not necessarily spreadsheet software) do not even respect quoted strings.

I had not heard of "sep=x" in CSV what a shame there is no way to put a header line that indicates CSV column data types rather than having to use import wizard for every to you import a CSV file or letting Excel best guess the CSV column data - that is typical lack of meeting power user needs.

Note: After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.

If you have a worksheet with data in columns that you want to rotate so it’s rearranged in rows, you can use the Transpose feature. It lets you rotate the data from columns to rows, or vice versa.

For example, if your data looks like this, with sales regions listed along the top and quarters along the left side:

You can rotate the columns and rows to show quarters along the top and regions along the side, like this:

Here’s how:

Select the range of data you want to rearrange, including any row or column labels, and either select Copy
on the Home tab, or press CONTROL+C.

Note: Make sure you copy the data to do this. Using the Cut command or CONTROL+X won’t work.
Select the first cell where you want to paste the data, and on the Home tab, click the arrow next to Paste, and then click Transpose.
Pick a spot in the worksheet that has enough room to paste your data. The data you copied will overwrite any data that’s already there.
After rotating the data successfully, you can delete the original data.

Tips for transposing your data

If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative and absolute references before you rotate the data.
If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first by using the Convert to Range button on the Table tab, or you can use the TRANSPOSE function to rotate the rows and columns.
If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.

How do I convert a CSV file to rows in Excel?

Open the Excel spreadsheet where you want to save the data and click the Data tab. In the Get External Data group, click From Text. Select the TXT or CSV file you want to convert and click Import.

How do I import a CSV file into a row?

In the Import Text File dialog box, browse for the . csv file you want to import, select it and click the Import button (or simply double-click the file)..

The Delimited file type..

The row number to start import at (normally, row 1).

Whether your data has headers..

How to import a text file into Excel and change columns to rows?

You can import data from a text file into an existing worksheet..

Click the cell where you want to put the data from the text file..

On the Data tab, in the Get External Data group, click From Text..

In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import..

Why is my CSV file in columns?

When you open it in your spreadsheet editor (Excel, for example), it shows all of the data in a single column, instead of splitting them across columns. The reason behind this behavior is that CSV files (Comma Separated Values) are splitting data into columns using " , " as separator by default.