Website Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • August 30, 2015, 09:34:13 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

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

Saira

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
CSV/Excel Parsing & Formatting Utility
« on: April 23, 2012, 05:54:01 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
« Last Edit: April 23, 2012, 08:18:20 PM by Saira »

barney

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 1,269
    • View Profile
    • Read more about this member.
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #1 on: April 23, 2012, 09:46:31 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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #2 on: April 24, 2012, 01:31:30 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

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #3 on: April 24, 2012, 07:40:13 AM »
@barney  i found unicsved no use for me..

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #4 on: April 24, 2012, 12:07:02 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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #5 on: April 24, 2012, 12:08:42 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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #6 on: April 24, 2012, 12:18:57 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.
« Last Edit: April 24, 2012, 01:35:59 PM by AndyM »

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #7 on: April 25, 2012, 02:54:52 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

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #8 on: April 25, 2012, 09:00:22 AM »
yep :D 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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 12,785
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #9 on: April 25, 2012, 09:17:05 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.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #10 on: April 25, 2012, 10:33:11 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 8)


Renegade

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 12,785
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #11 on: April 25, 2012, 10:35:54 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 8)

Always good to know another solid library! Thanks for the tip! :)
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Saira

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #12 on: April 25, 2012, 12:35:03 PM »
@Ath yes .. true very true .. same time of import ... it does that job also ..

Saira

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #13 on: April 25, 2012, 04:44:47 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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #14 on: April 26, 2012, 02:49:08 AM »
@Saira: Can you attach here, or e-mail me, a sample please?

Saira

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #15 on: April 26, 2012, 05:13:53 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

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #16 on: April 26, 2012, 11:51:08 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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #17 on: April 26, 2012, 11:56:11 AM »
I'll add that, that's not too difficult :)

Saira

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #18 on: April 26, 2012, 02:55:36 PM »
great :D

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #19 on: April 26, 2012, 03:43:46 PM »
Saira, would the pgno 'trick' be applicable to the (song...) lines as well?

Saira

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #20 on: April 26, 2012, 04:11:28 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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,532
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #21 on: April 26, 2012, 04:39:47 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

  • Participant
  • Joined in 2010
  • *
  • gravatar avatar
  • Posts: 22
    • View Profile
    • Donate to Member
Re: CSV/Excel Parsing & Formatting Utility
« Reply #22 on: April 26, 2012, 05:02:01 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 .