Date match format error

hargun · Post by **hargun** » Fri Jan 04, 2013 9:55 pm

Hi,

i am reading CSV file but getting the below error.

APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm.

the source column is Complete_DATE VARCHAR(10)

SEQFILE..............xfm.................DB2 table.

in tranformer i have also tried with function

If IsNull(select_stats.Complete_DATE) then SetNull() else StringToDate(select_stats.Complete_DATE,'%mm/%dd/%yyyy')

The date in source file is given below the last column is date column

20451,1,2006/07/28
30451,10,2006/07/28
50451,15,2006/08/16
60451,45,
70451,49,
80451,53,
20451,55,
30451,67,
00451,78,
70451,86,
40451,98,
40451,400,
210906,M01,2005/10/31
210906,1,2005/10/31
210906,10,2005/10/31

Can you please provide the solution to get rid of this.

ray.wurlod · Post by **ray.wurlod** » Sat Jan 05, 2013 1:22 am

And, sure enough, the dates in the data don't match the specified format string. The solution obtains immediately from the foregoing - you have to make them match (probably by providing a correct format string). You may also have to handle the empty (null?) date fields.

hargun · Post by **hargun** » Sat Jan 05, 2013 8:51 am

sorry my mistake date format is coming like 07/12/2007.Still same error coming.

given below how is coming in CSV file

20451,1,07/28/2006
30451,1,07/28/2006
80451,10,07/28/2006
70451,15,08/16/2006
60451,45,
50451,49,
40451,53,
90451,55,
00451,67,
40451,78,
40451,86,
40451,98,
40451,400,
210906,M01,10/31/2005
210906,1,10/31/2005
210906,10,10/31/2005

chulett · Post by **chulett** » Sat Jan 05, 2013 9:10 am

To me that implies those missing dates are not null. As noted, you may need to be more complete in your 'empty' date handling.

hargun · Post by **hargun** » Sat Jan 05, 2013 9:24 am

How can i handle empty date handling.I tried with the function that I have posted in the begnining.

chulett · Post by **chulett** » Sat Jan 05, 2013 9:34 am

First off, try to verify the contents of the field. 'Null' and 'Empty' are two different things, the latter might be some number of spaces. Check.

hargun · Post by **hargun** » Sat Jan 05, 2013 1:50 pm

file contains both Null and empty so i tried with function

If IsNull(select.DATE) or select.DATE=' ' then SetNull() else StringToDate(select.DATE,'%mm/%dd/%yyyy')

still thowing the same APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm. error

ray.wurlod · Post by **ray.wurlod** » Sat Jan 05, 2013 3:20 pm

How can a file contain NULL ?

chulett · Post by **chulett** » Sat Jan 05, 2013 3:20 pm

Tell us what you mean by "empty". You checked for a single space and an empty string should be checked for with just a pair of qoutes: ''

hargun · Post by **hargun** » Sat Jan 05, 2013 3:36 pm

i am getting the data in the format given below

20451,1,07/28/2006
30451,1,07/28/2006
80451,10,07/28/2006
70451,15,08/16/2006
60451,45,
34567,24.09/16/2006

this is csv file so second last date field have nothing just blank....can you help how can i handle this........now i am getting same warning which i need to get rid of......

chulett · Post by **chulett** » Sat Jan 05, 2013 3:48 pm

The devil is in the details, please be more precise. "Nothing just blank" means what, exactly? If the EOR is right after the comma then try checking for select.DATE='' rather than select.DATE=' ' and see if that is the magic you were missing.

rsripathy · Post by **rsripathy** » Mon Jan 07, 2013 1:00 pm

Read the 3rd (date column) as Varchar(10). In transformer use the below expression. For null values we are giving a default value of '12/31/9999'; If you want null then specify SetNull();

If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')

hargun · Post by **hargun** » Tue Feb 05, 2013 8:35 pm

rsripathy wrote:If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')

i have tried this function also but still getting the same error

APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 05, 2013 11:28 pm

How have you specified the Null Field Value property for this date field?

chandra.shekhar@tcs.com · Wed Feb 06, 2013 12:21 am

While reading the date value from the file, mark the column as NOT NULL and in the transformer check for empty only.

Code: Select all

If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')

Or Use the "Null Field Value" property in the sequential file and use ''(two single quotes or Empty) as its value. Then in the transformer check for null only.

Code: Select all

IsNull(select.DATE) then StringToDate("12/31/9999",'%mm/%dd/%yyyy') else StringToDate(select.DATE,'%mm/%dd/%yyyy')