1

When I change the type to string, I am fine. But, when I use int?, datetime? etc, sometimes it accepts NULL and sometimes, it fails with the error.

"Problem while converting input string '"NULL"' to proper type."

Vanamali
  • 47
  • 5
  • Can you please provide a concrete example of a failure? Also, are you passing "NULL" (the string) or _null_ (the value)? – Michael Rys May 31 '18 at 17:57
  • Passing 'NULL'. Same one in both the places. where the parser accepts and where it does not for int?|31|NULL|NULL|NULL|NULL|1 – Vanamali Jun 01 '18 at 13:19

1 Answers1

2

If you pass the following file content:

31|NULL|1

to the following EXTRACT expression:

@data = 
  EXTRACT a int?, b int?, c int? 
  FROM @file 
  USING Extractors.Text(delimiter:'|');

you will get the error message you observe since NULL is treated like a string. The default way to serialize and parse null values with the built-in Extractors/outputters is as no value, e.g., 31||1. The built-in extractors provide you with a nullEscape parameter though. So try:

… USING Extractors.Text(delimiter:'|', nullEscape:"NULL");
Michael Rys
  • 6,684
  • 15
  • 23