While dealing with data, it can often be troublesome to convert it from one tool to another. People often have issues while transposing certain data from text or CSV files to Excel, or vice versa.
In the example below, we will show how can you transfer data that is stored in a CSV file into an Excel file.
Different File Types
When dealing with Excel, you will mostly stumble upon three file types:
- Text File (TXT) – It is simple, plain text created in Notepad or WordPad. If you have some data and you paste it in a simple text file, formatting and styles will not be copied, so you will not have formatting issues.
- CSV file – It is also plain text and it is useful as it can be opened in any operating system, every text editor, but also Excel. If you transfer spreadsheet data into a CSV file, it will be separated by commas.
- Excel Spreadsheet – These types of files can be opened only by the proper applications, i.e. spreadsheet applications. They are not plain as they have a lot of formatting, and calculation options, as well as possibilities, to present information visually. In these file types, information is presented in cells, rows, and columns.
Transposing Csv File in Excel
For our example, we will create a CSV file with the list of NBA players, their teams, and statistics from one night of basketball: points, rebounds, assists, and turnovers to an Excel Spreadsheet:
As seen, all of our data is in one column, column A. We will open the new Excel file now, and then go to the Data tab >> Get & Transform Data >> From Text/CSV:
In the window that appears, we will find our file, and then select Import:
A new window will appear that will guide us. In the previous Excel versions, the whole process was a bit counter-intuitive, but with Office 365, it looks way better.
The window that we have now shows us the File Origin, Delimiter (we will choose Semicolon as data in CSV are separated with a semicolon), and Data Type Detection (we can choose among different ones: Based on the first 200 rows, Based on the entire dataset, and Do not detect data types):
We have various options for File Origin and Delimiter (colon, comma, space, tab, etc.). We got the preview of our data in the window as well. When we click Load, we will end up with the nice table where NBA players will be presented:
