boya's picture

HI all.

I´m going on tour with a show in Europe and I need to make subtitles in diferent languages that will be screened for the audience in sync with a mov playing (sepparate displays).

So I have this subtitle file .xls file I´ve pulled out of DVDsp:

00:05:54:21 , 00:05:57:14 , I don't understand it 00:05:58:01 , 00:06:00:07 , Time flies so fast 00:06:00:07 , 00:06:04:06 , that it almost stands still

etc..I have about 1000 lines

So first I need to build something QC understands. I´ve been trying to export into .cvs then convert to .plist with http://blog.danilocampos.com/2009/12/04/convert-a-csv-into-a-plist-file/

But I get a plist that yelds nothing in Kineme Structure from file.

So in short: how do I make a structure in QC from an excel file?

Bilde 6.png
Bilde 6.png96 KB

tobyspark's picture
Re: Textmachine

easiest would probably be to get that text info into one giant string and then use the string scanner patch to break into individual chunks based on a separation character. csv export should get you there, you'll either have commas or returns between your text. if you have the choice of separation character, something more unusual than a comma is usually wise.

dust's picture
Re: Textmachine

xml export would work as well. kineme structure from file should get a plist. you would need to use a combination of index and keys to extract the file from either .plist or xml and as well you can explode the strings.

usefuldesign.au's picture
Re: Textmachine

Not to question toby, but a way to do it if you want to maintain the structure is to convert the file to XML and use the XML importer patch.

You can use a text editor with a good find/replace tool to automatically generate the tags usually. In your example file searching for new line character: /n and replacing it with: </sub-title-item>/n<sub-title-item>/n<begin-time> and so on.

If it just can't be done that way (possibly not now I think about it) another way is to put the data into a spreadsheet and insert columns between the data with the appropriate XML tags. Then copy and paste the whole thing into a text file. (At this point you may want to globally replace all end tags with themself plus a new-line character.) Open the textfile in Dashcode and you should have nicely formatted XML. I've done this with 3D point dumps from Blender and it definitely works and gives you a readable structure.

gtoledo3's picture
Re: Textmachine

re:tobyspark's suggestion : I've used that same method to good effect... and any part of the string that is a number converts fine when you put it into a number port, so "it's all good".

boya's picture
Re: Textmachine

I really think I want to maintain the structure, instead of one great string. But I can not find a way to convert to xml/plist automaticly, and for tags and all, I do not know where to start.

Anyone´d like to help out with the .xls?

usefuldesign.au, why xml importer and not Knm Stucture from file? What´s the difference?

Skugar excerpt.xls14 KB

cybero's picture
Re: Textmachine

I heartily recommend that you download, compile and run at the Terminal window this command line application, csv2xml. This is a topic that has been apprehended before now.

Works like a charm , so long as you don't present it with blank empty space.


Now find attached an XML output of your XLS file, which I eliminated the comma columns out of, exported as Unicode CSV and then gave the file an explicit extension of .csv and ran that through the csv2xml application with the trivial terminal line,

csv2xml <Skugar.csv> Skugar.xml

The application is a doddle to compile, must mail the author whose ReadMe is a little in need of update about OS X compatibility, although it does list FreeBSD already. Just make and make install at the command line.

Skugar.xml_.zip911 bytes

boya's picture
Re: Textmachine

Tanks cybero. Dont´t know why I missed that previous discussion in my search.

But your file gives nothing in Knm structure from file and only the three first datasets in XML importer. I´m still in 5.8, last File Tools beta.

usefuldesign.au's picture
Re: Textmachine

One of XML's main design features is to present human readable data files that are not application or use specific. Kineme Structure File tools use .plist files — which can be dumped to an XML by the Property List Application anyhow — they have a bit more info like data type and are a OSX native file type for storing prefs etc. you can't open them TextEdit.

Both give you structured data. I just got into XML before File Tools were released and am more familiar with parsing text in InDesign than programming tools ;) String Components patch also gives you a structure but it's a flat one.

This comp shows the plist way (using Troy's Koellings recent Image Texture Splitter) and the text string into structure way using a sample of the data you provided in Skugar_excerpt.xls file.

I've used different separator keys (&& and ^^^) to distinguish top level items (separated with &&) and each item's 3 components start,end,subtitle (separated with ^^^).

Cybero's way sounds really neat btw will try in the future.

smokris's picture
Re: Textmachine

FileTools's Structure From File only handles plist files (whether binary or xml), not general xml files.

usefuldesign.au's picture
Re: Textmachine

Hey cybero,

I can't compile and run this csv2xml . ATM it's sitting in my downloads folder, what to do?? 'make' just gives me an error: make: *** No targets specified and no makefile found.  Stop. I cd to my download folder and still didn't find it. :/

cybero's picture
Re: Textmachine

Well I had figured out what was wrong, namely the lack of appropriate xml header tag, a wrapping root tag and line breaks, so not much wrong with that one. I'd uploaded an in edit state - this one is correctly formatted.

The file attached does work aok.

The work route is therefore make data only xls and convert to or export as csv and then convert in csv2xml to xml and away you go to add in the missing XML tag , and the root tag.

See attached XML file & working .qtz.

That problem you mention with compiling, puzzling.

csv2xml has been around and not much changed since 2005, so Leopard shouldn't be a hurdle for that source code.

Just use the Terminal, cd to your csv2xml directory, type make and then type make install after the first make command has run. should be good to go.

xmlAOK.zip4.08 KB

usefuldesign.au's picture
Re: Textmachine

Okay thanks cybero I had the downlo0ad directory not the download/csv2xml directory (I'm still new a this terminal game). Closer but when I > make I get errors:

cd src && make install
cp: directory /usr/local/bin does not exist
make[1]: *** [install] Error 1
make: *** [install] Error 2

and when I run csv2xml I get file created but it is empty (some code not installed and executing I guess). Do I need a header row in the .csv file to generate the tag names? tried that anyhow and no joy.

Any help appricated.

cybero's picture
Re: Textmachine

Last login: Fri Jun  4 15:14:26 on ttys000
cpc4-seac20-2-0-cust448:~ cybero$ cd Desktop
cpc4-seac20-2-0-cust448:Desktop cybero$ cd csv2xml
cpc4-seac20-2-0-cust448:csv2xml cybero$ ls
CHANGES      Makefile   TODO      src
CVS      README      man
cpc4-seac20-2-0-cust448:csv2xml cybero$ cd src
cpc4-seac20-2-0-cust448:src cybero$ ls
CVS      Makefile   csv2xml.cpp   test.csv
cpc4-seac20-2-0-cust448:src cybero$ make
g++ csv2xml.cpp -o csv2xml -g
csv2xml.cpp: In function ‘int get_csv_token(char*, int)’:
csv2xml.cpp:73: warning: deprecated conversion from string constant to ‘char*’
csv2xml.cpp: In function ‘int get_field_headers()’:
csv2xml.cpp:115: warning: deprecated conversion from string constant to ‘char*’
csv2xml.cpp:121: warning: deprecated conversion from string constant to ‘char*’
csv2xml.cpp: In function ‘int get_row()’:
csv2xml.cpp:168: warning: deprecated conversion from string constant to ‘char*’
csv2xml.cpp:171: warning: deprecated conversion from string constant to ‘char*’
csv2xml.cpp: In function ‘int main(int, char**)’:
csv2xml.cpp:283: warning: deprecated conversion from string constant to ‘char*’
cpc4-seac20-2-0-cust448:src cybero$ make install
cpc4-seac20-2-0-cust448:src cybero$ ls
CVS      csv2xml      csv2xml.dSYM
Makefile   csv2xml.cpp   test.csv
cpc4-seac20-2-0-cust448:src cybero$ csv2xml <test.csv> test.xml
cpc4-seac20-2-0-cust448:src cybero$ 

That's my session, the file that is output still needs some further formatting [see other posts on this thread by me for further details], but that is pretty straight forward in Dashcode or Text Wrangler, note that the 'warnings' about its increasingly deprecated code don't stop csv2xml compiling & working though. You might need to enable root user by the look of things, as that /usr/local/bin directory has to exist.

dust's picture
Re: Textmachine

the easiest way to test the cvs2xml is to cd to the examples directory included in cvs2xl then type ./examples.sh

this will run all the examples and put the xml files into the results folder.

on a basic level you can cd to cvs2xml folder you downloaded then type

./c2x-cmd -v -s:/path/to/myfile.cvs -t:/path/2/myfile.xml

thats how you would run it. you will want to check the formatting examples to see how to properly format your cvs. like you will need separators or make headings in xls so the converter knows what to use for tags.

here would be an example of what you need to do once you get your headers etc...

bash-3.2$ cd /Users/d0cut0uch/Desktop/cvs2xml 
bash-3.2$ ./c2x-cmd -v -s:/Users/d0cut0uch/Desktop/Skugar\ excerpt.xls -t:/Users/d0cut0uch/Desktop/Skugar\ excerpt.xml-e:ISO-8859-1
Converting source file </Users/d0cut0uch/Desktop/Skugar excerpt.xls>
to         target file </Users/d0cut0uch/Desktop/Skugar excerpt.xml-e:ISO-8859-1>
Settings : 
-> Separator(s)     : ;
-> XML Mode         : Elements with Element Context (default)
-> Encoding         : ISO-8859-1
-> Suppressing      : nothing
-> Writing Rec Num  : No
-> Writing Rec ID   : No
-> ISO-8859-1 file header found 
Header contains 1 items 
Working :
-> 4 records
-> 199 lines (source)
-> 4 elements
-> Conversion time : 0 seconds 
bash-3.2$ ./c2x-cmd -v -s:/Users/d0cut0uch/Desktop/Skugar\ excerpt.xls -t:/Users/d0cut0uch/Desktop/Skugar\ excerpt.xml-e:ISO-8859-1

boya's picture
Re: Textmachine

I have a thing working mostly the way I want.

Whent with the long string inside the comp with bits from Troy Koelling and useful.

There is probably room for improvement. I noticed the comp pulls 14% of the prosessor when running only off a stopwatch. I guess things can be simplified if you have the know-how. My greatest concern is that the pulse to the counter will hickup sometimes during the performance and things may get out of sync since it is not "hardwired" to the timecodes.

Thanks all. Just add a move.

Textmachine_01.qtz81.45 KB

usefuldesign.au's picture
Re: Textmachine

cybero wrote:
You might need to enable root user by the look of things, as that /usr/local/bin directory has to exist.

Hey cybero that sounds intelligent ;) Can you point me to a Terminal 101 primer or let me know how to "enable root user". Possibly both... Once csv2xml is installed I can use the command in any directory can't I, not just the same as csv2xml?

usefuldesign.au's picture
Re: Textmachine

Thanks Dust. I understood about 40-50% of that! Appreciate the help though. As I asked cybero, where can I start with a nice (read: Idiot friendly) Terminal primer?

How did you workout the command extensions like -e -s -t -cmd? How did you get the abbreviation ./c2x to replace csv2xml?

So much to learn, so little time.

On a basic level …

./c2x-cmd -v -s:/path/to/myfile.cvs -t:/path/2/myfile.xml

was a no go so likely I haven't got the install to work as Cybero suggested, need to enable root user whatever that means(?)

cybero's picture
Re: Textmachine


From the Apple menu choose System Preferences....

From the View menu choose Accounts.

Click on the lock and authenticate with an administrator account.

Click Login Options....

Click the "Edit..." or "Join..." button at the bottom right.

Click the "Open Directory Utility..." button.

Click the lock in the Directory Utility window.

Enter an administrator account name and password, then click OK.

Choose Enable Root User from the Edit menu.

Enter the root password you wish to use in both the Password and Verify fields, then click OK.

from http://support.apple.com/kb/ht1528

however, for most purposes using sudo before root commands should also work, it will require you to enter your account password.

Enabling root is disabled by default as it can create security issues.

You should have been able to run the make and make install commands without enabling your root account though... I must admit I'd made that root user account remark when it seemed as though you had no such /usr/bin directories.

To see if you do have those directories open up Terminal and type

defaults write com.apple.finder AppleShowAllFiles TRUE

to show all hidden files

http://www.westwind.com/reference/os-x/invisibles.html is a good reference upon such things as is Apple's own support site.

to hide the files again

defaults write com.apple.finder AppleShowAllFiles FALSE

By the way, when you open up a new Terminal , you can use the up and down arrows to scrollback through commands entered in previous sessions held in cache.

usefuldesign.au's picture
Re: Textmachine

Thanks for the links, Cybero.

dust's picture
Re: Textmachine

i was able to run without enabling root user. you type su into the terminal if you want root user permissions. usually you don't need to do this but something like .perl for instance you need to. i couldn't get spread sheet posted here to work with any combination of formatters or extensions. i tried with "," as a separator. i don't have Microsoft on my computer so couldn't experiment with making headers. things like -v -s and the formatters are all in man or help page but with this program that didn't help me. i just opened the examples.sh file in text edit and looked at how the example commands where run. at this point you can edit the .sh to use your file and run that like ./examples.sh or you can run them in the terminal. without the examples i would not have been able to get this to run. don't know why i even bothered seeing i don't have excell. just wanted to help maybe...

usefuldesign.au's picture
Re: Textmachine

Hey the code you credited to tkoelling inside your composition is all mine, but it's all good, the code is trivial.

I marked troy's code in the patch I posted with a note. He did all the fast texture splitting stuff and I just used it to demo how by writing the start positions (as a structure saved to .plist) for sprites could be read in again even after the Viewer window is resized and they don't make sense.