Extract files from single day- U SQL

Question

I am facing issues with a U SQL script. I am trying to get files which were created on current day from a directory. the file name will have the date in yyyyMMdd format. But when i try to extract data instead of taking only one days files i am getting all the files inside the directory. I am using the below script.

DECLARE @file_set_path string ="/XXXX/Sample_{date:yyyy}{date:MM}{date:dd}{*}.csv";

@searchlog =
EXTRACT PART_NUMBER string, date DateTime FROM @file_set_path USING Extractors.Tsv(skipFirstNRows:1);

Can someone please help me on this.

wBob wBob · Accepted Answer · 2018-06-04T15:17:06

You can use the Date property of the DateTime object to compare dates without including the time component, something like this:

DECLARE @file_set_path string ="/Sample_{date:yyyy}{date:MM}{date:dd}{*}.csv";
DECLARE @now DateTime = DateTime.Now;


@searchlog =
    EXTRACT PART_NUMBER string,
            date DateTime
    FROM @file_set_path
    USING Extractors.Csv(skipFirstNRows : 1);


@output =
    SELECT *,
           @now AS now,
           date.Date AS x,
           @now.Date AS y

    FROM @searchlog
    WHERE date.Date == @now.Date;


OUTPUT @output
TO "/output/output.csv"
USING Outputters.Csv();

NB I noticed you are using the Tsv extractor with Csv files. It may not matter when there is only one column or possibly this is a typo?

Extract files from single day- U SQL

1 Answers