Trouble doing SELECT OPENROWSET from JSON Lines file due to file extension

Richtpt — Wed, 11 Mar 2026 18:38:54 GMT

I have a bunch of files that are JSON Lines files. I have imported the files into a Lakehouse in the Files section. The files all have a ".json" extension. I'm trying to use the SQL Endpoint to select * from a file:

SELECT *
FROM OPENROWSET(BULK '/Files/Test1.json')

When I do this, I get

Msg 15853, Level 16, State 20, Line 3
Invalid or unknown format type 'json'.

If I change the extension to ".jsonl" it works fine. Is there a way to make this work with the ".json" extension? I tried adding FORMAT="JSON" but that's not working, which could be me not understanding the syntax correctcly.

Re: Trouble doing SELECT OPENROWSET from JSON Lines file due to file extension

Richtpt — Wed, 11 Mar 2026 22:13:31 GMT

Found it. I was using FORMAT = "JSON". First, it should be single-quotes not double. Second, it's JSONL. So the syntax is OPENROWSET(BULK '/Files/Test1.json', FORMAT = 'JSONL').

topic Re: Trouble doing SELECT OPENROWSET from JSON Lines file due to file extension in Data Warehouse

Trouble doing SELECT OPENROWSET from JSON Lines file due to file extension

Re: Trouble doing SELECT OPENROWSET from JSON Lines file due to file extension