Polybase CREATE EXTERNAL TABLE skip header

Question

I am new to Azure and Polybase, I am trying to read a CSV file into a SQL External Table.

I noticed that, it is not possible to skip the first row, the header on some forums I read.

I'm hoping for the opposite,Can you help me ?

The code I used is below.

Thanks in advance

 CREATE EXTERNAL TABLE dbo.Test2External (
   [Guid] [varchar](36) NULL,
    [Year] [smallint] NULL,
    [SysNum] [bigint] NULL,
    [Crc_1] [decimal](15, 2) NULL,
    [Crc_2] [decimal](15, 2) NULL,
    [Crc_3] [decimal](15, 2) NULL,
    [Crc_4] [decimal](15, 2) NULL,
    [CreDate] [date] NULL,
    [CreTime] [datetime] NULL,
    [UpdDate] [date] NULL,
    ...
WITH (
    LOCATION='/20160823/1145/FIN/',
    DATA_SOURCE=AzureStorage,
    FILE_FORMAT=TextFile
);


-- Run a query on the external table

SELECT count(*) FROM dbo.Test2External;

Answer 1

there is a workaround by using 'EXTERNAL FILE FORMAT' with 'FIRST_ROW = 2'. e.g. if we create a file format

CREATE EXTERNAL FILE FORMAT [CsvFormatWithHeader] WITH (
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',', 
        FIRST_ROW  = 2,
        STRING_DELIMITER = '"',
        USE_TYPE_DEFAULT = False
        )
)
GO

And then use this file format with create external table

CREATE EXTERNAL TABLE [testdata].[testfile1]
(
    [column1] [nvarchar](4000) NULL
)
WITH (DATA_SOURCE = data_source,
LOCATION = file_location,
FILE_FORMAT = [CsvFormatWithHeader],REJECT_TYPE = PERCENTAGE,REJECT_VALUE = 100,REJECT_SAMPLE_VALUE = 1000)

It will skip first row while executing queries for 'testdata.testfile1'.