Exam DP-500 All Questions

View all questions & answers for the DP-500 exam

Exam DP-500 topic 1 question 67 discussion

Actual exam question from Microsoft's DP-500

Question #: 67
Topic #: 1

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?

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 -
)

Show Suggested Answer

Suggested Answer: A 🗳️

by Maazi at Dec. 29, 2022, 11:23 a.m.

Comments

Submit Cancel

Maazi

Highly Voted 2 years, 6 months ago

Selected Answer: A

Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file

upvoted 6 times

...

DarioReymago

Most Recent 2 years, 3 months ago

Selected Answer: A

a is correct

upvoted 1 times

...

Az301301X

2 years, 3 months ago

Selected Answer: A

Chat GPT: Based on the characteristics of the File1.txt, the correct query to execute in Azure Synapse Analytics serverless SQL pool is option A. This query uses the OPENROWSET function to read a CSV file, which is specified by the BULK option. The FIELDTERMINATOR is set to a tab character (\t), and the ROWTERMINATOR is set to the UNIX-style line ending (0x0a). The FIRSTROW option is set to 2 to skip the header row. Option B uses a comma as the field terminator, which does not match the tab-delimited format of the file. Option C uses a comma as the field terminator and the UNIX-style line ending as the row terminator, which does not match the characteristics of the file. Option D sets the FIRSTROW option to 1, which would include the header row in the result set. So, A.

upvoted 2 times

solref

2 years, 3 months ago

CHAT GPT : Based on the characteristics of the File1.txt, the correct query to read the file using an Azure Synapse Analytics serverless SQL pool is: D Explanation: The file has a header row, so it's important to specify that the first row is not a header row in the query by setting FIRSTROW=1. The file is tab-delimited, so FIELDTERMINATOR should be set to '\t'. The file has UNIX-style line endings, so ROWTERMINATOR should be set to '0x0a'. The FORMAT option should be set to 'CSV', even though it is not strictly a CSV file because it is tab-delimited. This is because CSV format can handle tab-delimited files as well. Options A, B, and C are not correct because they have incorrect field terminators or row terminators, or they do not specify that the first row is a header row.

upvoted 1 times

...

cookiemonster42

2 years, 5 months ago

It's C, open AI confirmed it :)

upvoted 1 times

...

stfglv

2 years, 5 months ago

This part of the A solution is the key and makes all other solutions incorrect (t for tab): FIELDTERMINATOR = ‘\t'

upvoted 4 times

Az301301X

2 years, 5 months ago

....and FIRSTROW= 2, because the Headers.

upvoted 2 times

...

solref

2 years, 3 months ago

You are right Terminating character Tab = '\t' Newline character = '\n' https://learn.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver16

upvoted 1 times

...

sgodd_0298

2 years, 2 months ago

Option D contains FIELDTERMINATOR = ‘\t' as well FYI.

upvoted 1 times

...

moreinva43

2 years, 6 months ago

that website makes it look like the answer is c with the same field terminator and row terminator.

upvoted 1 times

...