Import data from text/CSV without headers?

[plasticinhialeah]

I use Excel 2019 and I'm trying to import some data from text/CSV, but the data I'm importing has headers, and I don't want them. Is there a way I can remove the headers and their formatting from the text before importing? I don't want to just delete the row with the headers after I import the text.

 

[sandy666]

The shortcut only seems to function similarly to selecting the "from text/CSV" option, and there are no legacy wizards in that menu as far as I checked.

did you try select file then hit Import button?
wizard will show up only after that not before

 

[plasticinhialeah]

Okay, I just found out I can't even delete the headers after I import the text. When right-clicking the row with the headers, the 'Delete' option is grayed out. I've tried other things, and nothing seems to delete them. How are you supposed to delete the headers from imported text at all?

 

[sandy666]

PQ Editor
Home - Remove Rows - Remove Top Rows (set how many rows you want to remove)

but you cannot remove headers totally, it will change to Column1, Column2 etc...

PQ works with columns and content - forget about worksheet style

 

[plasticinhialeah]

What is PQ Editor?

 

[sandy666]

hm.......

PQ Editor = Power Query Editor

or describe what you did exactly

 

[plasticinhialeah]

What I'm doing is actually part of a school assignment. It's a series of Excel 2016 tutorials. Because I have Excel 2019, I have to deal with a few discrepancies between the two versions. This is one of those examples. In Excel 2016, you could easily remove the headers from text imported by selecting what row to start importing at. You could do this in a importing Wizard. Excel 2019 doesn't have a Wizard for this operation. You click "From Text/CSV", select the file to import, click "Import", and then click on "Transform Data" on the popup window that opens. This opens the Power Query Editor.

The CSV files I'm importing for these assignments have headers, and because it's assumed I'm using Excel 2016 (since that's what these tutorials are for), it's telling me to just select to start at row 2 or whatever. As I've said before, this Wizard is not in Excel 2019, and I haven't found the option for selecting the row to start importing at in the Power Query Editor. Removing top rows only removes the first rows under the headers, so that function would do me no good here.

Also, after importing the non-transformed data, I'm unable to delete the headers in any way. The "Delete" option is grayed out in the right-click menu for the row with the headers. I can't clear the headers' contents and I can't get rid of the headers' formatting (these options are not grayed out, but when you perform them, nothing happens to the headers).

I can access the legacy Wizard, but I don't want to have to rely on that all the time. I'd like to be able to do it completely in Excel 2019.

 

[sandy666]

could you post a screenshot what you see after import csv into PowerQuery?

be aware you are working with Tables all the time

Table has headers, you cannot jump over that, headers can be original from your csv or after remove original headers - you will get headers automatically like Column1, Column2... etc

to remove headers totally you can load your QueryTable into the sheet, convert loaded Table to Range then remove (delete) first row with headers

How to...
right click on Table in the sheet
select Table - Convert To Range

 

[sandy666]

after that you can remove (delete) Query Table from Workbook Queries (if you want)

btw. I'd like to know what you see after Alt+A+FT , select file , click Import ?

it's Text Import Wizard?

 

[plasticinhialeah]

Thanks, that seemed to work!

 

[sandy666]

You are welcome

Have a nice day