CSV Header on Row 1 and 2

Jozef_783863
Jozef_783863 Posts: 331 admin

Incoming CSV header row splits across row 1 and row 2 because there is a line break present.

If my file always comes in like this, how can I place these two rows in row 1?

Image below shows the CSV's view in both Excel and a text editor with JSON syntax highlighting.

Comments

  • Jozef_783863
    Jozef_783863 Posts: 331 admin
    edited April 2020

    My Excel: Extract range output returned this JSON string:
    Column A;Column B;Column D;Column D;Column E;"Column,F";Column G

    Removing the comma from "Column,F" gives me the column headers I need in a string. From there, it looks like I can use Tables: Convert text to a data table. This should create a data table with my header rows in it. Then I can save my original data to this table using Excel: Save Spreadsheet to Table.

    Has anyone used a similar or a different approach for this scenario?

  • Dylan_191182
    Dylan_191182 Posts: 92 admin
    edited April 2020

    @Jozef_783863 I think your approach would work for this specific case, but not in general. In general this approach should work
    1. Use Files: Convert file to text
    2. Use Tables: Convert text to a data table with a column delimiter of ; and a row delimiter of \n.
    3. Use Excel: Create spreadsheet from data table to create a CSV from the table (note the incoming file is not a comma separated file, but a file delimited by semi colons)
    4. Use Excel: Extract Range to get the first row.

    There are other ways to get the columns after Step 1 - but Step 1 is the key