Blank rows not coming

Question

After running the below code i am getting only rows where is present and not the rows where just ID is present.

Is there any problem with the extraction of file.

DROP VIEW IF EXISTS dbo.Consolidated;
CREATE VIEW IF NOT EXISTS dbo.Consolidated
AS
EXTRACT Statement

FROM   "adl:///2016/08/12/File_name.csv"
USING Extractors.Csv(silent : true, quoting : true, nullEscape : "/N");

@Temp =
SELECT *
FROM Consolidated;


OUTPUT @Temp
TO "adl://arbit/new_cont_check.csv"
USING Outputters.Csv();

i am getting red line below OUTER keyword while running your code ..... — Bond, Mar 30 '17 at 13:54
Have you accidentally added a semi-colon at the end of the first `EXTRACT` statement? — wBob, Mar 30 '17 at 14:39
No... i haven't added any semi-colon at the end. Even if -else statements are not working in my Visual Studio. I think i have older version of Visual Studio. will update and check. — Bond, Apr 04 '17 at 13:44

David Paul Giroux · Answer 1 · 2017-03-31T15:42:27.113

2

So your number of columns vary? If yes, try using a user-defined extractor. An example of a user-defined extractor where the number of columns vary can be seen @ Using User-Defined Extractor - FlexExtractor.

edited Mar 31 '17 at 15:42

answered Mar 28 '17 at 15:44

David Paul Giroux

657
4
10

score 1 · Answer 2 · edited May 23 '17 at 10:30

You have added the silent: true argument to your USING clause meaning the EXTRACT will not fail or even complain if the rows don't quite match your schema definition. This is the intended behaviour and probably what you want for this example. In order to pick up the other rows, you can use OUTER UNION, like in this recent example:

Another working example similar to yours:

@input =
    EXTRACT ControllerID int?,
            ParameterID int?,
            MeasureDate DateTime,
            Value float
    FROM "/input/input56.csv"
    USING Extractors.Csv(silent : true, quoting : true, nullEscape : "/N", skipFirstNRows : 1)

    OUTER UNION ALL BY NAME ON ( ControllerID )

    EXTRACT ControllerID int?
    FROM "/input/input56.csv"
    USING Extractors.Csv(silent : true, quoting : true, nullEscape : "/N", skipFirstNRows : 1);


OUTPUT @input
TO "/output/output.txt"
USING Outputters.Tsv(quoting : false);

I used this sample file and got these results:

NB I have changed the nullability of your columns as otherwise the OUTER UNION add default values for the .Net types as per the Sep 2016 release notes.

Blank rows not coming

2 Answers2