NOCOA Import error

I agree with Mr. Short. Most probably it was caused by non-printing characters in Street1 or Street2 in table t_address (and correspondingly in TW_NCOA_INCOMING). Please try for characters CR and LF (Run it in Text output mode Ctrl-T rather than in Grid mode of the SSMS -- if there are occurrences of such characters then you'll see an extra line for each address entry):

SELECT  customer_no, address_no ,street1 
from t_address
where ( CHARINDEX( char(13),street1 )>0 or CHARINDEX( char(10),street1 )>0)

Here are two ways to fix the addtesses (not possible to do that in Tessitura front-end):

--A (simple) to substitude any occurrence of CR+LF or LF with a space
update t_address
set street1=replace(replace(street1,char(13),''),char(10),' ')
where address_no in(SELECT address_no from t_address
where ( CHARINDEX( char(13),street1 )>0 or CHARINDEX( char(10),street1 )>0)
and isnull(street2,'')<>''
)

--B:  if street2 is blank then find the firs occurrence of CR+LF or LF in street1 and insert the corresponding parts of the address in street1 and street2 (with removing of any extra occurrences of the characters from street2). If street2 is not blank then remove any occurrence of CR+LF or LF in street1.
declare
@street1 varchar(100),
@street1_new varchar(100),
@street2_new varchar(100),
@position int
declare crs1 cursor local for
SELECT  street1 --customer_no
from t_address
where ( CHARINDEX( char(13),street1 )>0 or CHARINDEX( char(10),street1 )>0)
and isnull(street2,'')=''
--and customer_no in(80177441,85063388,85074251)---------------->>>
for update of street1, street2
--
open crs1
fetch next from crs1 into
@street1
while (@@fetch_status=0)
begin 
  set @position=CHARINDEX( char(10),@street1 ) --ASII 10 is always presented
  --replace to get rid of the any remaining CR:
  set @street1_new=replace(replace(SUBSTRING(@street1,1,@position-1),char(13),''),char(10),'')
  set  @street2_new=replace(replace(SUBSTRING(@street1,@position+1,100),char(13),''),char(10),' ')
  print 'position='+convert(varchar,@position)+
  ', str1='+ @street1_new+
  ', str2='+ @street2_new
  update t_address set
  street1= @street1_new,
  street2= @street2_new
  where current of crs1
  --
  fetch next from crs1 into
  @street1
end --while @@fetch_status=0
close crs1
deallocate crs1

 Please let me know if any questions. Also, I have more complicated script to identify all non-printing characters, if needed.

Simon Basyuk, sbasyuk@carnegiehall.org

DBA of Carnegie Hall

I agree with Mr. Short. Most probably it was caused by non-printing characters in Street1 or Street2 in table t_address (and correspondingly in TW_NCOA_INCOMING). Please try for characters CR and LF (Run it in Text output mode Ctrl-T rather than in Grid mode of the SSMS -- if there are occurrences of such characters then you'll see an extra line for each address entry):

SELECT  customer_no, address_no ,street1 
from t_address
where ( CHARINDEX( char(13),street1 )>0 or CHARINDEX( char(10),street1 )>0)

Here are two ways to fix the addtesses (not possible to do that in Tessitura front-end):

--A (simple) to substitude any occurrence of CR+LF or LF with a space
update t_address
set street1=replace(replace(street1,char(13),''),char(10),' ')
where address_no in(SELECT address_no from t_address
where ( CHARINDEX( char(13),street1 )>0 or CHARINDEX( char(10),street1 )>0)
and isnull(street2,'')<>''
)

--B:  if street2 is blank then find the firs occurrence of CR+LF or LF in street1 and insert the corresponding parts of the address in street1 and street2 (with removing of any extra occurrences of the characters from street2). If street2 is not blank then remove any occurrence of CR+LF or LF in street1.
declare
@street1 varchar(100),
@street1_new varchar(100),
@street2_new varchar(100),
@position int
declare crs1 cursor local for
SELECT  street1 --customer_no
from t_address
where ( CHARINDEX( char(13),street1 )>0 or CHARINDEX( char(10),street1 )>0)
and isnull(street2,'')=''
--and customer_no in(80177441,85063388,85074251)---------------->>>
for update of street1, street2
--
open crs1
fetch next from crs1 into
@street1
while (@@fetch_status=0)
begin 
  set @position=CHARINDEX( char(10),@street1 ) --ASII 10 is always presented
  --replace to get rid of the any remaining CR:
  set @street1_new=replace(replace(SUBSTRING(@street1,1,@position-1),char(13),''),char(10),'')
  set  @street2_new=replace(replace(SUBSTRING(@street1,@position+1,100),char(13),''),char(10),' ')
  print 'position='+convert(varchar,@position)+
  ', str1='+ @street1_new+
  ', str2='+ @street2_new
  update t_address set
  street1= @street1_new,
  street2= @street2_new
  where current of crs1
  --
  fetch next from crs1 into
  @street1
end --while @@fetch_status=0
close crs1
deallocate crs1

 Please let me know if any questions. Also, I have more complicated script to identify all non-printing characters, if needed.

Simon Basyuk, sbasyuk@carnegiehall.org

DBA of Carnegie Hall

What version of AP_NCOA_PROCESS do you have? If you have one published 2010-Feb-10 or later, the fields are cleansed upon export using FS_REMOVE_NON_ALPHANUMERICS_NCOA(), which removes all non alphanumeric characters..

You can also use that function to cleanse any field.

Rob(tm)

Hi everyone, thank so much for all your suggestions. The file came from MBS and everything seems to be formatted correctly according to the documentation….although that is a human eye looking.  Thanks so much for copying your code here Simon. There are a number of things in there I have never used before and don’t quite understand and would probably take me a long while to decipher being a newbie. If I can use a built in function, it’s probably best…less chance of breaking something!

Rob - We have an up to date version so you are saying that I can use FS_REMOVE_NON_ALPHANUMERICS_NCOA() against the file MBS sent back to remove all non-alphanumeric characters? How would I go about doing this?

I noticed how it does it within the AP_NCOA_PROCESS procedure however am having trouble figuring out how I would apply this to the file. Or am I applying this to the records in TW_NCOA_INCOMING? So something like…..

UPDATE TW_NCOA_INCOMING   

Set aadd1  = dbo.FS_REMOVE_NON_ALPHANUMERICS_NCOA(aadd1), 

sadd1 = dbo.FS_REMOVE_NON_ALPHANUMERICS_NCOA(sadd1)

Sorry for my confusion…being not too familiar with SQL just yet, I’m very VERY slow (and cautious). Thanks so much for your help all!

Hi Natasha,

Yes, that SQL will work, but it may not be necessary. Check the number of records in your TW_NCOA_INCOMING table - select count(*) from TW_NCOA_INCOMING where ncoa_session = {whatever_your_ncoa_session_is}. If it matches up, you're ok. My recollection is that this error comes up on the import process because of the header row. So if your records import, it's a benign error -- the header row just won't import and you don't need to further cleanse the data.. You could always remove the header row from the file first if you wanted a squeaky clean report. :) 

Let me know if that scenario resonates.

Rob(tm)

Ahh...I see.

Yep, the number is fine so I shall process away!

Thanks again.

Tash