Polybase for CSV files without headers

Question

Hi,

I am trying to set up a lab where I can query csv files content from polybase -sql server- straight from the OS file system. I am using Access driver for txt files. So far everything works well, though I come accross a use case where a csv file has no headers. I have been looking it up on Microsoft Learn but I can't find any explicit reference saying whether it is possible to create an external table for a flat file without headers. Create external table syntax may imply you cannot -for you have to provide the list of columns you want to work with-. Nevertheless, I feel like there should be a way to bypass it or work it out since this scenario is not improbable, all the contrary.

¿Has anyone come up with a use case like this or knows the answer?

Thank you!

Answer 1

Hi @Seeya Xi-MSFT ,

Thanks for your answer.
See, the use case I am facing is not that I want to skip headers on a file but the fact that a set of files are created without headers at all, then since CREATE EXTERNAL TABLE syntax requires a list of columns and based on documentation for that instruction, I cannot get to create an external table for such files even if a use an external file format.

I have tried to bypass that by using numbers for column names -synapse openrowset likewise-, empty strings,etc. It hasn't worked so far.

So I got doubtful, is that even possible? Is the only use case covered by polybase that of files having headers?

Answer 2

Hi @Juan Carlos Solano ,

Welcome to Microsoft Q&A!
If you want to CREATE EXTERNAL TABLE, here is an official document: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15&tabs=delimited
FIRST_ROW = 2 will skip the headers. This parameter can take values 1-15. If the value is set to two, the first row in every file (header row) is skipped when the data is loaded.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.