Subfield Command

[#1]

My data in my table appears like:

EventID       EventDate

1                    9/1/2014 (carriage return)

                      9/2/2014 (carriage return)

                      9/3/2014 (carriage return)

What I want to end up with is:

Event ID       EventDate

1                    9/1/2014

1                    9/2/2014

1                    9/3/2014

Dropping the carriage return and duplicated the EventID.  I would like to do this in the load statement because for each EventID there could be a different number of dates. 

I have been trying the Subfield function and cant get it to work.  Any help would be great.

Thanks,

David

[JonnyPoole · Accepted solution]

It all depends if they are carriage returns, line feeds or both:   (IE:  chr(13) , chr(10) or both)

one or the other:

subfield([EventDate],chr(13))

subfield([EventDate],chr(10))

if both use replace with subfield:

subfield( replace(replace([EventDate],chr(10),''),chr(13),',')   ,',')

[#3]

So it would look like:

Load EventID,

         subfield(EventDate,chr(13)) as EventDate

From......

[mark_casselman]

why not use the purechar function ?

Load EventID,

PurgeChar(EventDate, chr(10) & chr(13)) AS EventDate

Resident...

[#5]

What I am looking the output to be is:

Event ID       EventDate

1                    9/1/2014

1                    9/2/2014

1                    9/3/2014

That is why I thought the subfield function was the answer but I have tried it and can get it to work.  I am attaching the Excel File.

[JonnyPoole]

looks like chr(10) is the winner.

You need to use it in the load script.  Here is the working code.

LOAD FSScheduleID,

     EventDate,

     SubField(     EventDate , chr(10)) as Date

FROM

(biff, embedded labels, table is Sheet1$);