Solved

SSIS: Dealing with empty strings in CSV

Posted on 2014-02-17
15
680 Views
Last Modified: 2016-02-10
Hi Experts,

I was wondering if someone can help me.

I have a CVS file with a column name called 'Invoice #'. During importation I have renamed this column as 'invoicenumber' in the output column in the Flat File Source.

In Derived Column instead of the default NULL values for empty strings for this column that occurs in a SQL Server table I want to replace these with 'No Invoice Number'

I've put the below expression in the Derived Column:

ISNULL(invoicenumber) ? "No Invoice Number" : "invoicenumber"

Open in new window

.

However, the final table is still showing NULL for these empty strings.

I've also tried REPLACE function and this does not work.

Help!

OS
0
Comment
Question by:onesegun
  • 8
  • 6
15 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 39866623
Hi,

SSIS is case sensitive, is that a problem.
Try also putting the column heading in braces...

So...

ISNULL([invoicenumber]) ?  "No Invoice Number" : [invoicenumber]

Open in new window


Hope this helps a bit.

T
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39866983
If you can't get it to work with ISNULL then perhaps it's an option to use the LEN function:

LEN(invoicenumber) > 0 ? "invoicenumber" : "No Invoice Number"

Open in new window

Note that "invoicenumber" is a hardcoded string, if you expect the actual content of the column then you'll need to remove the quotes.
0
 

Author Comment

by:onesegun
ID: 39867048
Hi Tony and Valentino,

Thanks for your input but unfortunately it is still frustratingly producing 'NULL' values for empty strings for both solutions.

I tried re-creating the table where the CSV file needs to go into (making the invoicenumber column not null in the create script) but it fails the package at the OLE DB Destination.

SSIS simply removes the braces from the solution below:

ISNULL([invoicenumber]) ?  "No Invoice Number" : [invoicenumber]

Open in new window


Any other ideas?

Thanks,

OS
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39867610
The Flat File Source has got a property called RetainNulls.  To what is it currently set?  When set to False you should be getting empty strings instead of NULLs.  And when set to True you should be able to test on NULL with ISNULL.  Try changing this setting to see if you get better results.
0
 

Author Comment

by:onesegun
ID: 39867987
Ok so now I changed RetainNulls as false and the empty strings are coming through. However, when I apply the following expression in Derived Column:

REPLACE(invoicenumber,"","No Invoice Number")

Open in new window


I still get empty strings. I'm sure I have the syntax right.

Any ideas?

Thanks,

OS
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 100 total points
ID: 39869593
Try using the inline if logic (condition) ? true-part : false-part as mentioned before instead of Replace.  How about this:

LEN(invoicenumber) > 0 ? invoicenumber : "No Invoice Number"

Open in new window

0
 

Author Comment

by:onesegun
ID: 39869951
Yeah I tried the solution above but no joy.... :-(

I've imported the csv in Excel and it is string length 0. That LEN function should work surely....
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39869981
Something must be differing from what has been specified.  The above expressions should all work, I use them all the time...

Would it be possible to post a small sample file with which you've got the issue?  Careful to not post any sensitive data!
0
 

Author Comment

by:onesegun
ID: 39873068
Data Flow Task
Hi Valentino,

I've attached an image of my DFT and also a small sample file of the csv I'm attempting to load with invoice numbers and with no invoice numbers.

Thanks,

OS
testfile.txt
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39873206
What you've posted is not a CSV but a fixed width flat file.  And the columns don't match.  This is what line 5 and 6 look like:

xxxx	999200	xxxx001	xxxxxxxxx London		999200	xxxxxxxx xxx (UK) Ltd.	DZ	08/23/2012		181,747.05		
xxxx	325000	xxxx001	xxxxxxxxx London	xxxxxtel Renewal Costs FY12	30003068	INTERNAL	RV	09/24/2012	9049002405	8,388.00	

Open in new window

Scroll to the back and you'll see immediately that something's off...
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39873211
ow, unless TAB is your delimiter?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39873231
Okay, I've tested your file and it's working perfectly (except for a missing CRLF on the last line but that doesn't impact invoice #).  Perhaps try to start over?  Delete the flat file source and connection manager and start from scratch.

The LEN expression works using the defaults.  The ISNULL expression works after settings the RetainNulls property to True.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39873236
BTW: why are you using that Data Conversion transform?  Try to remove that and use only the Derived Column.

In fact, I never use Data Conversion because you can do the same using Derived Column (and Derived Column supports copy/paste).
0
 

Author Comment

by:onesegun
ID: 39873551
Ok I've removed the data conversions.

The only reason I put it there was because I was getting errors in the OLE DB Destination regarding
column cannot convert between unicode and non-unicode string data types

The column name properties are nvarchar(255) in the SQL table I'm importing the data too.

Did you get this error and if so how do you recommend dealing with this?

Thanks,

OS
0
 

Author Comment

by:onesegun
ID: 39873815
Yippeee!!!

I got it working at last. It must have been that Data Conversion that I put in the middle.

Anyway, the problem seems to have been that the csv file was in non-unicode and my SQL table was in unicode.

By right clicking on the Flat File Source and clicking on Show Advance Editor I was able to amend the data type and output length on the flat file output columns.

Cheers

OS
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now