File Exists function in USQL

Question

I am doing a simple test in USQL to check if a file exists and then perform Merge operations based on that. However, I cannot seem to get this right. Can you please help me with this and let me know where am I going wrong.

DECLARE @InputFile1 string = "/MergeTest/FINAL/Check1.csv";
DECLARE @InputFile2 string = "/MergeTest/STAGING/Test-{*}.json";                                                                         
DECLARE @OutputFile1 string = "/MergeTest/FINAL/Check1.csv";

IF (FILE.EXISTS(@InputFile1))
THEN
@Final = EXTRACT [ID] int,
                 [Name] string,
                 [City] string,
                 [Country] string
FROM @InputFile1
USING Extractors.Csv(skipFirstNRows:1);
END;

@Delta = EXTRACT [ID] int,
                 [Name] string,
                 [City] string,
                 [Country] string
FROM @InputFile2
USING new JsonExtractor("$.DEP[*]");

The below code does not work in the IF condition . I get the error : @FileExists is already declared as a scalar variable. If this is a wrong way to implement this, what would be the correct approach?

@FileExists = SELECT (FILE.EXISTS(@InputFile1)) AS exists_good FROM (VALUES (1)) AS T(dummy);

IF ####@FileExists THEN
    @result =   SELECT * FROM @Delta
                UNION ALL
                SELECT * FROM Final AS T1
                ANTISEMIJOIN (SELECT * FROM @Delta) AS T2
                ON T1.[ID] == T2.[ID];
ELSE
    @result =   SELECT * FROM @Delta;
END;

OUTPUT @result   
TO @OutputFile1
USING Outputters.Csv(outputHeader:true);

Thanks, Sunitha

Answer 1

Just write this instead:

IF FILE.EXISTS(@InputFile1) THEN ...

When you assign to this with SELECT this is basically a rowset and cannot be treated like a scalar variable.

I also note in your example that @inputFile1 and @outputFile1 have the same filename. I am sure this will cause problems. Make sure they are different in your real example.