# Handling 'empty text' from flat file imports

**URL:** <https://community.dremio.com/t/handling-empty-text-from-flat-file-imports/8057>\
**Category:** Uncategorized\
**Created:** [September 29, 2021, 10:37am UTC](https://community.dremio.com/t/handling-empty-text-from-flat-file-imports/8057 "2021-09-29T10:37:45Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![spireite](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@spireite](https://community.dremio.com/u/spireite)\
**Post date:** [September 29, 2021, 10:37am UTC](https://community.dremio.com/t/handling-empty-text-from-flat-file-imports/8057/1 "2021-09-29T10:37:45Z")

</div>

Reading a delimited text file I can’t find a way to handle ‘empty text’ on a conversion of a field

So for example,

Paul,1234.5678,9999.99  
Jim,9999.99

On trying to convert the numeric values from string to decimal with CAST on column2, I get an error on conversion because of the ‘empty value’ on row 2

In SQLServer, I’d use a TRY\_CAST/TRY\_CONVERT which would return a NULL if not a number, or the proper casted value datatype if it is.

In the Dremio UI it uses CONVERT\_TO\_FLOAT, but that is undocumented and it has a problem. The values returned from the REST API on a conversion are horrid. A value of 46801122.6600 is returned as 4.680112266E7. I also need it to be a proper decimal.

What is the best way to achieve what I need?

---

<div class="post-metadata">

**Author:** ![spireite](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@spireite](https://community.dremio.com/u/spireite)\
**Post date:** [September 29, 2021, 10:51am UTC](https://community.dremio.com/t/handling-empty-text-from-flat-file-imports/8057/2 "2021-09-29T10:51:16Z")

</div>

I do realise that I could use

cast(case when AmountSecured=’’ then null else AmountSecured end as decimal(18,4))

which appears to work, but my goodness that adds a lot of noise to the query, and it’s horrid to read on the Dremio UI which lacks some configurability for reading …

so I’m looking for a nicer solution.

---

<div class="post-metadata">

**Author:** ![lenoyjacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lenoyjacob/32/3474_2.png) [@lenoyjacob](https://community.dremio.com/u/lenoyjacob)\
**Post date:** [September 29, 2021, 6:16pm UTC](https://community.dremio.com/t/handling-empty-text-from-flat-file-imports/8057/3 "2021-09-29T18:16:46Z")

</div>

> I get an error on conversion because of the ‘empty value’ on row 2

What’s the error exactly? The below seem to work for me on your example dataset (even with Null values).

`cast(B as decimal(18,4)) as B`

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/1/1790c1869f1a6bd462c241ba186959cd8a59041d.png)

---

<div class="post-metadata">

**Author:** ![spireite](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@spireite](https://community.dremio.com/u/spireite)\
**Post date:** [September 30, 2021, 8:32am UTC](https://community.dremio.com/t/handling-empty-text-from-flat-file-imports/8057/4 "2021-09-30T08:32:10Z")

</div>

Thanks for your reply.

I’m going to hold my hands up on this one, cardinal sin of not analysing my actual source data enough, and assuming it was empty text screwing it up, and creating a dataset on the fly for the post.

What it actually was dodgy data further down and a figure literally of 99.xx

I see lots of CASE WHEN IS\_xxxx wrappers in my future.

Again, apologies.