Replace comma form txt file

[alfredl20605198]

Hi everyone,

I wrote a script to read and replace characters from a .txt file string and insert the result into after effects. Problem : the txt is made with a single copy/paste from excel, and when I import it into my script, the end of each excel line becomes a comma. So, if I replace the comma by a "\r", it affects all the other commas in texts or numbers...

example of lines in my .txt file :

1  16  Titre  Association

1  2  Citation  Convention par laquelle deux ou plusieurs personnes mettent en commun, d'une façon permanente, leurs connaissances ou leur activité dans un but autre que de partager des bénéfices@loi de 1901

2  7  Picto Grand  ampoule.png@1,3 millions d'associatons

3  7  Picto Grand  billets.png@70 milliards d'euros de dépenses

4  7  Picto Grand  contrats.png@1,8 millions de salariés

and how After Effects see it :

1  16  Titre  Association,1  2  Citation  Convention par laquelle deux ou plusieurs personnes mettent en commun, d'une façon permanente, leurs connaissances ou leur activité dans un but autre que de partager des bénéfices@loi de 1901,2  7  Picto Grand  ampoule.png@1,3 millions d'associatons,3  7  Picto Grand  billets.png@70 milliards d'euros de dépenses,4  7  Picto Grand  contrats.png@1,8 millions de salariés

So, is there a way to keep the lines, or to change the comma in another character automatically, or anything else ? Maybe the method to copy/paste from excel...

Thanks for your answer
Phil

[Dan Ebberts]

You could try saving the Excel file as a tab-delimited text file. Then you just replace tabs characters.

Dan

[alfredl20605198]

Still the same problem... I fixed it by copying an extra column, and I change tab + comma. Not the best way, but it's working like this.

[Mathias Moehl]

In classes 6 and 7 of this (paid) training, I show how to develop a more robust spreadsheet parser in AE scripting. If you want to do it the clean way, it becomes pretty complex (as you can guess from the fact that I spend more than 1 hour of the training explaining it).

https://www.fxphd.com/details/?idCourse=539

[phlexib]

And this all course is awesome !!

Ben

[runegan]

You can do this using regular expressions

To replace the any comma at the end of a line you can do:

var regex = /,$/mg;
string.replace( regex, '\n' );

• , matches the character ',' literally
• $ asserts position at the end of a line
• g modifier: global. Replace all matches, instead of just the first one (the default)
• m modifier: multi line. Causes ^ and $ to match the begin/end of each line (not only begin/end of string)

Visit https://regex101.com/r/SAX0es/1/​ to see the regex in action

[alfredl20605198]

Yes, great, that's exactly that !! Thanks