Convert text string "mm/dd/yy,hh:mm AM/PM" to Date & Time format

[johnsandiego]

Hello -

 

I'm rather new at this and I am sure there is probably an easy way to do this but...

 

1. I exported a Playlist from iTunes (latest version) as a text file and saved it as an Excel File and also as a Filemaker 12 file.

2. I would like to  sort any of these files by the "Date Added" field. There are over 17,000 song titles in these files.

    Since all of the data in this file is text, I obviously cannot just perform a sort on that field and expect it to be correct.  I want

    to store the information and and other date/time info in the file in a FM "Timestamp" field

 

Does anyone have a simple solution to this problem?

 

I am running FileMaker 12 Advanced on a 2011 iMac

     OS X 10.9.1

     (3.4 GHz Intel Core 7

    16 GB  RAM

    256 GB SSD

   2 TB  2n Internal Drive

 

Thanks in advance for any help this list can provide.

(Note to commercial developers, I would not be adverse to paying for a solution so long as it doesn't break the bank)

[comment]

Could you post an exact sample of the string?

 

Please use data that will show unambiguously whether there are leading zeros in the various elements.

[Lee Smith]

A sample of the raw data would be helpful.

 

It appears to me that if you copy the file to a text editor, and then drag that onto the FileMaker application, you would get something like this

Toy Story 3	1:42:33	Pixar		Kids & Family		1	7/2/12, 9:59 PM

the last field (on mine) is “f8”, you can use left and right words to extract to new fields

 

LeftWords (f8 ; 1 )

RightWords ( f8 ; 2 )

 

and sort the two fields.

[comment]

It appears to me that if you copy the file to a text editor, and then drag that onto the FileMaker application, you would get something like this

Toy Story 3	1:42:33	Pixar		Kids & Family		1	7/2/12, 9:59 PM

 

If this:

7/2/12, 9:59 PM

is the actual format (and of course it's "m/d/yy, h:mm" and not as stated in the title), then you can simply define a calculation field (result is Timestamp) =

GetAsTimestamp ( Substitute ( Date Added ; "," ; "" ) )

Note that this relies on both the file and iTunes using the same date and time formats (as set in the OS).

[johnsandiego]

Sorry, I thought the "format" was evident in the subject line. But, as it turns out, the format was not quite correct. It should be "m/d/yy, h/m PM" (or AM).

 

The original file was exported from iTunes v11.2.3 and opens by default in "TextEdit" app, which is quite useless. Instead, I opened the file in MS Excel (Filemaker is not an option). I tried to do the calculation to convert in Excel and didn't have much luck. so, since I wanted the information in FileMaker anyway, I created a FMP12 database and pre-populated it with Field Names that matched what was in the Excel file and imported the Excel file into FileMaker. FWIW I formatted the FMP "Date Added" field as a Timestamp.

 

Here is a small subset of the 17,623 record file I wanted to convert the "Date ADDED" field. I also removed several other fields to keep the size small.

 

Thanks again.

 

 

Stats 131130.fmp12.zip

[johnsandiego]

Thanks Lee and Comment. Both of your recommendations worked! It looks like I need to review FileMaker Calculations a bit more closely before I post...

[Lee Smith]

You might fine these series of video on the Left, right and middle functions https://www.youtube.com/results?search_query=left+fumction+FileMaker&sm=3