Author Topic: CSV/Excel Parsing & Formatting Utility (Read 19433 times)

Saira · « **on:** April 23, 2012, 05:54 PM »

Hello
i have a csv file which has 4 columns Time in , Time out , Name , Text
"00:04:37:08","00:04:38:20","AYLA","May I take my phone back Duran?",

now what i want that .. i also have a excel file which has many columns .. few with TC IN .. TC OUT Actor name
what i want is to have tool in which i import the csv file and where ever the text Missing dialogues or Missing Dialogue or Missing Dialogue! or Missing Dialogues! come
like this one
"00:10:24:16","00:10:25:11","DÜRİYE","missing dialogue!",

it copy the Time in code to my excel TC IN Column and Time out to TC OUT Columnn And as Duriye is actor name it copy that Name to Actor Name column that is all
and so on just check the line 1 by one where ever it comes it gives me the result on that excel format
check the samples
Thanks

barney · « **Reply #1 on:** April 23, 2012, 09:46 PM »

CSVed may do what you want. I haven't tried it with a global replace, but I've used it for a few years with great results. There's also a uniCSVed for unicode files. I believe it can be made portable, as well. If you're on a Win system, you might take a look at it.

Ath · « **Reply #2 on:** April 24, 2012, 01:31 AM »

I'll integrate this in the error-report utility I made for Saira, AMDER, later today. Should be easy, as the error-report features are already there.

AMDER will be released sometime later this week.

Saira · « **Reply #3 on:** April 24, 2012, 07:40 AM »

@barney i found unicsved no use for me..

AndyM · « **Reply #4 on:** April 24, 2012, 12:07 PM »

So all you want to add to your .xls file are the rows in the .csv file that include "Missing d" or "Missing D" ?

Ath · « **Reply #5 on:** April 24, 2012, 12:08 PM »

Andy, I'm already working on another util for Saira that needs this data added/inserted. I'll add this as a feature with that tool

AndyM · « **Reply #6 on:** April 24, 2012, 12:18 PM »

Ath, sounds like you are well along with this, particularly if you already understand and can accomodate how he wants his data to appear in his .xls file.

Ath · « **Reply #7 on:** April 25, 2012, 02:54 AM »

I've pre-released a version of AMDER (v0.7, not yet openly released, but that shouldn't take much longer) to Saira, that has support for reading this file-format into the desired result for further processing.
Saira will be testing this, but I assume this request can be marked resolved.

Saira · « **Reply #8 on:** April 25, 2012, 09:00 AM »

yep

i am very happy its just work fine .. can make error report manually ... and can get froma csv file with in a jiffy it looks like a pro tool to me . with every thing one need .. ath can you add this thing also
if in any place something like (Song............) written it will take it as song and mark as song in excel format ... thr will be more than 6 dots which will indicate that how long song is ... and it will be inside the ()
Thanks

Renegade · « **Reply #9 on:** April 25, 2012, 09:17 AM »

FWIW, this: http://www.codeproje...58/A-Fast-CSV-Reader - is an excellent free component. It's very easy to use and you can then handle text however you like. I've used it before and it's been excellent. However, it requires programming... it's not hard, but still, you need to know a little bit.

Ath · « **Reply #10 on:** April 25, 2012, 10:33 AM »

@Saira: I'll see how to add that, as these 'song'-lines won't fall into the category of "Missing Dialogue"... You want these also added to the error report?

@Renegade: csv is easily handled by the XLSX library (EPPlus, extension of ExcelPackage, both on CodePlex) that I use to write the Excel .xlsx files

Renegade · « **Reply #11 on:** April 25, 2012, 10:35 AM »

@Renegade: csv is easily handled by the XLSX library (EPPlus, extension of ExcelPackage, both on CodePlex) that I use to write the Excel .xlsx files
-Ath (April 25, 2012, 10:33 AM)

Always good to know another solid library! Thanks for the tip!

Saira · « **Reply #12 on:** April 25, 2012, 12:35 PM »

@Ath yes .. true very true .. same time of import ... it does that job also ..

Saira · « **Reply #13 on:** April 25, 2012, 04:44 PM »

last thing
if we type Missing Dialogue with PGNO1 PGNO2 PGNO3 and so on as they are refering to page numbers
is that possible these Page numbers will go directly to the page number field ?
just numbers not the PGNO just numbers after that
so it will be like if we have a missing dialogue to save time to do things manually we will mention that this missingdialogue is on page number this and that
it will take the Time Code in and out with actor name and put the page number to page number field
like this
EP Page NO TIME IN TIME OUT ACTOR .............................
6 2 05 , 45 , 11 05 , 47 ,25 Ayla

hope you understand ..
Regards

Ath · « **Reply #14 on:** April 26, 2012, 02:49 AM »

@Saira: Can you attach here, or e-mail me, a sample please?

Saira · « **Reply #15 on:** April 26, 2012, 05:13 AM »

Well sample ?
well the csv you have just where you find the Missing dialogue just type manually PGNO20 or what ever they can be in small or upper case with it
and try to parse it simple

Saira · « **Reply #16 on:** April 26, 2012, 11:51 AM »

like if there is "Missing Dialogue" make it "Missing Dialogue pgno2" it will count it as missing dialogue also ... and take pgno2 as page number and copy 2 to page number field
Thanks

Ath · « **Reply #17 on:** April 26, 2012, 11:56 AM »

I'll add that, that's not too difficult

Saira · « **Reply #18 on:** April 26, 2012, 02:55 PM »

great

Ath · « **Reply #19 on:** April 26, 2012, 03:43 PM »

Saira, would the pgno 'trick' be applicable to the (song...) lines as well?

Saira · « **Reply #20 on:** April 26, 2012, 04:11 PM »

yes it must ... ... if we do not put pgno with either of that it will stillwork ? and show the 0 page number correct ?

Ath · « **Reply #21 on:** April 26, 2012, 04:39 PM »

Yes, a missing pgno is 0, I've made it somewhat tolerant for input, so that "pgno1", "pgno 1", "pgno-1", "pgno -1" or "pgno - 1" all result in page 1 (and multiple spaces are allowed as well).
I could make it more flexible if needed, by also allowing "pg", "pgn", "pgnr" or "page" instead of "pgno", but I didn't do that yet.

Saira · « **Reply #22 on:** April 26, 2012, 05:02 PM »

no pgno is all ok .. as no word will have this .. if anything i will let u know but all is ok for now .