2

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.

Milad Rashidi
  • 1,296
  • 4
  • 22
  • 40
vignesh asokan
  • 137
  • 3
  • 12

1 Answers1

1

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?

wBob
  • 13,710
  • 3
  • 20
  • 37
  • 1
    Also note that the way to filter the files is to add a SELECT after the EXTRACT as you see in Bob's reply. Just having the fileset pattern in the EXTRACT gives you "only" the virtual column(s). – Michael Rys Jun 05 '18 at 07:48
  • Hi Bob it was a typo. ANd thanks for the response and Michael.. It is working for me. – vignesh asokan Jun 05 '18 at 08:55