Converting Time Stamped Text Files for Import into Studiocode

If you have time stamped data from other sources or applications, it is likely importable into a Studiocode timeline. Time stamped data from other applications is often formatted in the following way:

hh:mm:ss.00

In order to import this information into Studiocode, the time stamps need to be converted to the following format. Notice, the colon replaces the decimal from the figure above.

hh:mm:ss:00

The following steps outline a process to convert time stamps with decimals that separate the seconds and hundredths, to time stamps with colons. Before following the steps, please be aware that you will need to have time stamps for the mark-in and mark-out durations. Studiocode requires both time stamps in order to create the instances on the timeline.

1. Let's assume your data can be entered into an Excel spreadsheet in a way that is similar to the following picture, where:
? Each row pertains to a particular event/behavior/action/competency (whatever your terminology maybe).
? Column A = the mark-in time
? Column B = the mark-out time
? Column C = what would become the name of the row in the Studiocode timeline


2. Right-Click on "Column B" and select "Insert" on the drop down menu.


3. Repeat this step on the new "Column D" (e.g. behavior) so you have two blank columns like the picture below.


4. Right-click on "B" (column header) and choose "Format Cells" from the drop down menu. Then choose "General" from the "Number" section and click OK.
**Note: Menu items might be slightly different depending on the version of, and platform (Windows or Mac OSX) upon which you're using, Excel.




5. Repeat step 4 for "Column D".
6. Single click in cell B2, and type (or copy and paste) the following exactly as it reads below, then press return.

=CONCATENATE(LEFT(A2,8),REPLACE(RIGHT(A2,2),1,1,":"))

**Note: If you have digits to the hundredths place, use the following formula instead:
=CONCATENATE(LEFT(A2,8),REPLACE(RIGHT(A2,3),1,1,":"))

7. Copy and paste the contents of cell B2 into cell D2.


8. Double click on the handle on the bottom right corner of the selected cell so it copies the formula down the column (vertically) into each cell.



9. Repeat Step 8 for cell B2.


10. Select the desired range of cells in Column B, Right-click, and choose "Copy".


11. Open a new Excel spreadsheet, Right-click in cell A2, and choose "Paste special". In the subsequent window, choose "Values and number formats".




12. Repeat Steps 10 & 11 for Column D and cell B2, respectively.


13. Enter the following text in the corresponding cells, exactly as it reads below (without quotes)
A1 = "start time"
B1 = "end time"
C1 = "category"
D1 = "Nth instance"
E1 = "# descriptors"
F1 = "descriptors..."


14. **Note:
If your data is sorted by start time, sorting it by "Category" would help with the next step.






15. Type "1", "2", "3" in cells D2, D3, D4, respectively. Then click and drag the handle on the selected range to the last cell corresponding with the first category in Column C.




16. Repeat step 15 for subsequent categories in Column C.

17. Go to File > Save as...
? Name the file
? Choose the destination
? Choose "Tab delimited" as the format
? Click Save


**Note:
If you're prompted with dialogue windows that say something to the following, just select "Continue".



Once you have the time stamps formatted properly, view this article for instructions on how to import an Excel Edit List into Studiocode.