Extract dates from textfield

[#1]

I have a problem.

You can see in the attachment the field interventions_date.

I need to use these dates in my application, but this is a simple textfield in the database.

Anyone any ideas?

Kind regards,

Katleen

[#2]

Still testing alle the possible suggestions.

If it would be helpful, I will attach the qvd file with the data

[#3]

How do I do this from a qvd?

[flipside]

You just need to replace the 1st section to something like ...

//1: Data being loaded

LOAD name,
     intervention_dates,
     from_date,
     to_date,
     recno() as sourceRow
FROM
[???.qvd] (qvd);

flipside

[#5]

Unfortunatly the result doesn't seem correct (see attachment)

[flipside · Accepted solution]

Try this using the qvd you uploaded ...

//3: Preceding load to handle space-delimited strings, splitting into separate rows (there's only one row in the qvd with this issue). As it is the last step, I also set the date field to date format.
Load
id,
sourceRow,
name,
date(subfield(trim(intervention_dates),' ',iterno())) as intervention_dates,
from_date,
to_date
while iterno()<=SubStringCount(trim(intervention_dates),' ')+1;

//2: Preceding load to handle comma-delimited strings, splitting into separate rows
Load
id,
sourceRow,
name,
trim(subfield(intervention_dates,',',iterno())) as intervention_dates,
from_date,
to_date
while iterno()<=SubStringCount(intervention_dates,',')+1;

//1: Data being loaded
LOAD id,
     name,
     intervention_dates,
     from_date,
     to_date,
     recno() as sourceRow
FROM
[Collection_routes.qvd] (qvd);

I get 1715 rows total.

flipside

[#7]

Looks great!

Thank you very much for your help!

Kind regards,

Katleen

[flipside]

Great.

The only caveat to this is that with the source being a text string, if another delimiter was introduced, or a bad date was encountered, it would display incorrectly. Preferably these should be prevented from being entered at source, but if this cannot be done then you would need to add further 'preceding loads' to handle them.

flipside