Trimming leading and trailing spaces from imported text.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Trimming leading and trailing spaces from imported text.

Postby nic » Fri Nov 11, 2011 2:41 pm

Have any readers of this forum encountered a situation where it becomes necessary to trim leading and trailing spaces from imported text?

I am sure that there is an easy way to deal with this sort of thing in Ffenics, other than manually editing (finding and replacing quotes and spaces) before importing.

Please can someone point me in the right direction.

Thank you in advance.

Nic :D
You must never lose your ignorance, you can never get it back again.
nic
 
Posts: 9
Joined: Thu Jul 14, 2011 12:57 pm
Location: Namibia
Has thanked: 0 time
Been thanked: 0 time
 

Re: Trimming leading and trailing spaces from imported text.

Postby Phil Winkler » Fri Nov 11, 2011 3:05 pm

How is the import file being created? It is usually best to fix this at the source.

Trailing spaces shouldn't be a problem.

Leading spaces (unless they are hard spaces-alt-255) could probably be removed via a DQL that used textpos() to find the where the first character is and then midc() to grab the entire string using the field length as one of the parameters and then modifying that field in that record.

I've never done this, but it ought to be an interesting exercise.
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Trimming leading and trailing spaces from imported text.

Postby Fred Kingston » Fri Nov 11, 2011 3:25 pm

What I usually do is create a tempForm to hold the data.. ALL the fields are simple text fields.. in one column down the form.. I import the raw data into this table.
After importing the data.. I then go back through the form, and create fields in a column with the correct data types for each field next to the imported field, and write a derivation formula in the new fields to parse the data correctly.. the new field is saved as a virtual field.. When everything is parsed and formatted correctly, I then write a procedure to Enter the imported data based on the corrected fields into their respective fileds in the database..

i.e.

TempImportTable
Field1 (raw data) CorrectedField1 (virtual, derivation(Field1)
Field2 (raw data) CorrectedField2 (virtual, derivation(Field2)

Procedure
For TempImportTable ;
Enter a record in RealTable
RealField1 := CorrectedField1 ;
RealField2 := CorrectedField2 ;
Fred Kingston
 
Posts: 281
Joined: Sun Aug 01, 2010 10:54 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Trimming leading and trailing spaces from imported text.

Postby Pete Tabord » Fri Nov 11, 2011 4:19 pm

There was a problem many moons ago in DE where trailing spaces would upset uniqueness tests and indices - but I think that was resolved about (many moons - 24) ago :-)
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: Trimming leading and trailing spaces from imported text.

Postby nic » Fri Nov 25, 2011 1:40 pm

Thanks for the great responses. Some great ideas put forward; that I would love to try.

As I was pressed for time and the data was not too voluminous, I edited the text before importing.

When I get back to this again, I will let you know what I did and what worked.

Ta ta for now.
You must never lose your ignorance, you can never get it back again.
nic
 
Posts: 9
Joined: Thu Jul 14, 2011 12:57 pm
Location: Namibia
Has thanked: 0 time
Been thanked: 0 time
 
 

Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron