Designing and Implementing Enterprise-Scale Analytics Using Microsoft Azure and Power BI — Question 78

You have a file named File1.txt that has the following characteristics:

A header row -

Tab delimited values -

UNIX-style line endings -
You need to read File1.txt by using an Azure Synapse Analytics serverless SQL pool.
Which query should you execute?

Answer options

• A. SELECT* FROM OPENROWSET( BULK ‘file1.txt’, DATA_SOURCE = ‘Sql1’, FORMAT = ‘CSV’, PARSER_VERSION = ‘2.0’, FIELDTERMINATOR = ‘\t’, ROWTERMINATOR = ‘0x0a’, FIRSTROW= 2 - )
• B. SELECT* FROM OPENROWSET( BULK ‘file1.txt’, DATA_SOURCE = ‘Sql1’, FORMAT = ‘CSV’, PARSER_VERSION = ‘2.0’, FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’, FIRSTROW= 2 - )
• C. SELECT* FROM OPENROWSET( BULK ‘file1.txt’, DATA_SOURCE = ‘Sql1’, FORMAT = ‘CSV’, PARSER_VERSION = ‘2.0’, FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘0x0a’, FIRSTROW= 2 - )
• D. SELECT* FROM OPENROWSET( BULK ‘file1.txt’, DATA_SOURCE = ‘Sql1’, FORMAT = ‘CSV’, PARSER_VERSION = ‘2.0’, FIELDTERMINATOR = ‘\t’, ROWTERMINATOR = ‘0x0a’, FIRSTROW= 1 - )

Correct answer: A

Explanation

The correct answer is A because it specifies the proper field terminator as a tab (' ') and the correct row terminator for UNIX-style line endings ('0x0a'). Option B uses a comma as the field terminator, which is incorrect for this file format. Option C also uses a comma, leading to an incorrect parsing of the file. Option D incorrectly starts reading from the header row instead of the second row.