Solved

SSIS: Dealing with empty strings in CSV

Posted on 2014-02-17
15
742 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

803 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