Forum Discussion

AL_NH_DHHS's avatar
AL_NH_DHHS
Copper Contributor
Sep 12, 2023

Excel 365 auto creation of header row column1,column2,column3 etc

Is there a way to change the default in Excel 365, so that it does not automatically create a header row with Column1, column2, column3 etc. when importing from a .csv file?

I have found how to remove this row, however we import regularly from .csv files and we don't want to have to remove this bogus row each time.

excel
office 365

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 12, 2023

    AL_NH_DHHS 

    In Excel 365, there is not a built-in option to change the default behavior of automatically creating a header row with "Column1," "Column2," etc. when importing from a .csv file. However, you can work around this by creating and saving a custom import template once and then reusing it for future imports. Here is how to do it:

    1. Prepare a Custom Import Template:
      • Open a blank Excel workbook.
      • Go to the "Data" tab in the Excel ribbon.
      • Click on "Get Data" or "Get & Transform Data" depending on your Excel version.
      • Choose "From Text/CSV" to import a .csv file.
      • In the "Navigator" or "Get Data" window, select your .csv file and click "Import."
      • In the "Text Import Wizard," set the import options as you prefer (e.g., delimiter, data types, etc.). Make sure to uncheck the option that says "Use the first row as headers."
      • Click "Load" or "Transform Data" to load the data into Excel without a header row.
    2. Save the Import Template:
      • After loading the data, you will have a query created in the Power Query Editor. You can close and load this data into a worksheet or just save the query as a connection.
      • To save the query as a connection for reuse, go to "Home" in the Power Query Editor and select "Close & Load To." Choose "Only Create Connection" and click "OK."
    3. Reuse the Template for Future Imports:
      • In the future, when you need to import data from a .csv file without the automatic header row, follow these steps:
      • Go to the "Data" tab in Excel.
      • Click "Get Data" or "Get & Transform Data."
      • Choose "Launch Power Query Editor."
      • In the Power Query Editor, go to the "Home" tab.
      • Click on "Queries" in the "Close & Load" group.
      • Select the query template you previously saved (the one without the header row) and click "Load."
      • This will import the data from the .csv file using your custom template, which does not include the automatic header row.

    By creating and saving a custom import template, you can avoid the need to manually remove the header row each time you import data from .csv files in Excel 365.

    The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources