Solved

SSIS: Dealing with empty strings in CSV

Posted on 2014-02-17
15
765 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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