How to group columns and normalize a csv file with a spreadsheet
I have to do on a daily basis with CSV files that contain entire archives of data, and punctually in order to use them I need to convert them in to a spreadsheet, to be able to work with the data separated into columns.
We will see how to perform a subdivision of a table of the CSV file so you can facilitate every type of work.
- For this example we download a CSV file, and apriamolo with Excel
- All rows will be reported under a single column, the A
- Select the column A
- Select the Data tab
- We use the Text to columns command to start the conversion wizard
- At this point we can make two different choices
Fields delimited by a character
- If the columns are separated with a special character (e.g. tab or comma (,) or semicolon (;)) we are going to use the Delimited option
- Then we will be asked what is the character that delimits the fields
Fields configured in the offset
- If the fields are configured in offset, meaning that each field starts with a given fixed font of each line, we’ll use the Width option to fixed
- The conversion wizard will ask us what are the offset between one field to another
Our file will be split into columns, and we can save and treat it as a normal spreadsheet.