Creating Order from Chaos

[Steve G]

I've got about 97,000 records with each record being a unique file name. The file names are everything from "Picture 1.png" to things like "724358428520_108594.TIF".

What I need is a script that will look for a block of numbers in each file name, then copy that block of numbers to another field in that record. The blocks of numbers can be anywhere from six to 20 digits. They are not in the same place in every file name. They are always separated from the other components of the file name by a space, dash, or underscore. Some file names have numbers attached to letters in addition to the block of numbers I need (e.g., "coltrane-72dpi-727738.jpg"). Some file names have multiple blocks, e.g., "Roof_028_20100126_150547.jpg" or "724353767020_104967_RGB_3x3_72.JPG" in which case I would need an "all-encompassing" set of numbers ("028_20100126_150547" and "724353767020_104967" in this example, respectively).

Is there ANY hope of salvaging these numbers through some clever scripting?

[comment]

Is there ANY hope of salvaging these numbers through some clever scripting?

Sure. First, substitute dashes with spaces (or underscores) so that each "block" becomes a separate word. Then use a looping script or a custom function to check each word for:

word = Filter ( word ; "0123456789" ) and Length ( word ) ≥ 6

I would need an "all-encompassing" set of numbers ("028_20100126_150547" and "724353767020_104967"

Not sure how useful such format would be.

Edited October 18, 2011 by comment

[Raybaudi]

This calculation may not cover all the possibilities ... but you can try it.

Let([

x = YourField ;

code = Substitute ( Trim ( Substitute ( Filter ( x ; 1234567890 & "_ -" ) ; [ " " ; "§§§" ] ; [ "_" ; " " ] ) ) ; [ "-" ; ¶ ] ; [ "§§§" ; ¶ ] ; [ " " ; "_" ] ; [ "__" ; ¶ ] )

];

Case(

Length ( GetValue ( code ; 1 ) ) ≥ 6 ; GetValue ( code ; 1 ) ;

Length ( GetValue ( code ; 2 ) ) ≥ 6 ; GetValue ( code ; 2 ) ;

Length ( GetValue ( code ; 3 ) ) ≥ 6 ; GetValue ( code ; 3 )

)

)

your examples |--------------------| result

Picture 1.png

724358428520_108594.TIF |----------| 724358428520_108594

coltrane-72dpi-727738.jpg |--------| 727738

Roof_028_20100126_150547.jpg |-----| 028_20100126_150547

724353767020_104967_RGB_3x3_72.JPG | 724353767020_104967