Import CDM folders using Notebooks in Fabric

[VickyDev18]

I have data synced from D365 to data lake in a CDM (Common Data Model) format. With CDM format the data files are in csv format but the metadata (column information) is stored in a separate json file. 

I am able to import CDM folders to a warehouse using DataFlow Gen2 as there is an option where we can specifiy that the folder is in a CDM format.  However, I would like to know how to do it using Fabric Notebooks though as they are more flexible and perform better. 

Note : I am able to import the csv file but without the column header information. I want to import with the column header information without having to created the table in advance or having to explicitly code in all the column header names. 

How exactly do I get a notebook to recognize that I am importing a CDM folder and make it load it with the header information?

[Anonymous]

Hi @VickyDev18 ,

Maybe you can try below code in notebook:

%%sql
-- Define the CDM folder path
SET @cdmFolderPath = 'C:\\Users...';

-- Define the format of CDM files
SET @fileFormat = 'CDM';

-- Define the format of data files in the CDM folder (CSV in this case)
SET @filePattern = '*.csv';

-- Use OPENROWSET to query the CDM folder
SELECT *
FROM OPENROWSET(
    BULK CONCAT(@cdmFolderPath, @filePattern),
    FORMAT='CDM',
    PARSER_VERSION='1.0',
    FIELDTERMINATOR=',',
    FIRSTROW=2 -- Skip the header row as it's included in the metadata
) AS [Result];

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly