Manipulate .csv through MaxScript?

[Anonymous]

Hello,

i'm trying to make a (my first) maxscript wich allows me to import a .csv file as an animated timeline with some interaction from the user.
My .csv is built up per line as follows:
,,,,,,,,,
In this line the and all the following values are irrelevant to me. I've attached an example of the .csv file.

What I want to accomplish is that the MaxScript evaluates the second line in the .csv file(the first contains headers) and calculates the total duration of the Task.
I.e: #,Total_Planning,#,#,#,#,#,27-9-2012 5:00,2-10-2012 2:00,Construct
- So the total construction time would be 117 hours. This is easy for us to calculate, but is there a way to let MaxScript do this? Taking into account that the months do not always contain the same amount of days.
What I'm trying to avoid is to manipulate the .csv file myself.
I know excel can calculate the difference but can MaxScript start/call this function? (As I'm trying to avoid altering the .csv file myself)

This calculation is needed multiple times where the resulting values will be used as variables for different things, therefor I want to use MacScript. So far I haven't come further then defining the variables based on a filterstring.
I'm also curious about a way of coping with empty values in the .csv file.

Any help would be appreciated.

PS. I know the numbers for the string filter don't correspond to the layout of the .csv I mentioned above. I'm using the script from below on a test .csv that I wrote myself for now. Using the print part of the code to check the values of the variables.

(
fName = getOpenFileName caption:"Open file" types:"(*.csv)|*.csv|"
fS = undefined
if fName != undefined then
   fS = openfile fName
if fS != undefined do
   (
      while (not eof(fS)) do
         (
         PR = readline fS; 
  PRF = filterstring PR "," 
  TN=PRF 
  TS=PRF 
  TE=PRF 
  TT=PRF
  
 TSF = filterstring TS "- :" 
 DS=TSF as integer
 MS=TSF as integer
 YS=TSF as integer
 HS=TSF
 MiS=TSF
  
 TEF = filterstring TE "- :" 
 DE=TEF as integer
 ME=TEF as integer
 YE=TEF as integer
 HE=TEF 
 MiE=TEF 
  Print TN
  Print TS
 Print DS
 Print MS
 Print YS
 Print HS
 Print MiS
  Print TE
 Print DE
 Print ME
 Print YE
 Print HE
 Print MiE
  Print TT
  
         )--while
 close fS
 ) --fS
)

[Anonymous]

Ok, I've managed to manipulate my .csv file through making an OLE object link and pasting some filtered values to the OLE object.
One problem that is residing right now is that the .csv file is comma seperated but that there are text values in between the comma's that also contain a comma.

I.e: 1,"text, text)",,,,,1,27-10-2012 15:55:00,27-10-2012 20:55:00,,,...

Currently im using the filterstring command to seperate the values by comma:

PR = readline fS;
PRF = filterstring PR "," Splitemptytokens:True

The problem with this filterstring is that it will search for ANY comma in the line, and thus also contains the comma inside the text block delimited by the double quotes. Obviously I don't want this to happen, does anyone have a suggestion on how to tackle this?

[Anonymous]

does the csv string have a known amount of commas ??

[Anonymous]

Yes, a normal csv string contains 28 commas, unless there's a comma inside a text block. Then it cannot be certain how many comma's the string contains.

[Anonymous]

Ok if a normal string contains 28 commas you can use array .count to work out if you have extra commas in the text field.

the maxscript help file gives an example of a function that returns an array with all entries in your string.

Frequently Asked Questions->Practical Questions->Working With Maxscript Values->
How do I filter a string including empty entries

You can now check the array count if its 28 no extra commas so you know where the vars
are that you want just pass the array to a function to collect them.

If there are more then 28 in the array count you know that the text field has commas inside
so pass array and how many extra commas you have to a function that uses the array
index+extra_comma_count to workout where in the string the vars are that you want.
(remember you will not need the extra comma count if you collect any vars before the text field)


Hope that makes some sense 🙂

[Anonymous]

Wow just reread this way too much coffee and not enough commas 😕

[Anonymous]

Hopefully this will make things easier to understand.

/*
Example: 
collection of var from csv string with possible 
extra commas in a known text field.
No error checking included
*/
-----------------------------------------------------------------
--normal csv string (9 commas)
loaded_str1 = "#,Total_Planning,,,text field,,,27-9-2012 5:00,2-10-2012 2:00,Construct" 
--extra commas in text field csv string(+2)
loaded_Str2 = "#,Total_Planning,,,text,,field,,,27-9-2012 5:00,2-10-2012 2:00,Construct" 
--Array to hold the delimited strings
myArray = #()
--Vars to hold collected items
myVar1
myVar2
myVar3
myVar4

--Reusable Function From The Maxscript Help Docs 
--Pass string to work on and a string or array of delimiters 
fn filterString2 theString theDelimiters =
(
 theTokens = #() --array of tokens to return
 ready = false
 while not ready do --repeat until no more delimiters can be found
 (
  ready = true --raise a flag that we are done
  thePosArray = #() --init. an array to hold possible split positions
  for i = 1 to theDelimiters.count do --go through all delimiters
  (
   checkPos = findString theString theDelimiters --check if the delimiter is in the string
   if checkPos != undefined do append thePosArray checkPos --if it is, add to the split positions array
  ) --end i loop 
  if thePosArray.count > 0 then --if the array has any splitpositions,
  (
   sort thePosArray --sort the array in ascending order
   checkPos = thePosArray --grab the first position
   aToken = substring theString 1 (checkPos-1) --take the token from start to the split pos. - 1
   append theTokens aToken --add the token to the array of tokens
   theString = substring theString (checkPos+1) theString.count --remove the token and the delimiter from the string
   ready = false --lower the flag because we are not ready yet
  ) --end if
 ) --end while loop
 append theTokens theString --add what is left of the string to the array
 theTokens --return the array of tokens
)

--Vars Collection Function
fn getMyVars delimitedArray addToInt =
(
 --this var is before the text field so nothing is added
    myVar1 = delimitedArray 
 --the rest of the vars are after the text field 
 myVar2 = delimitedArray
 myVar3 = delimitedArray
 myVar4 = delimitedArray
 
)


--pass the normal csv string and delimiter and fill myArray
--for testing edit loaded_Str1 to loaded_Str2 here 
myArray = filterString2 loaded_Str1 ","

--we know in this example the normal csv has 9 commas but myArray will have 10 elements
--so lets check the element count 
if myArray.count == 10 then
(
 --pass myArray and zero (no extra commas)
 getMyVars myArray 0
)
else 
(
 --loaded_Str2 has more than 10 elements so will land here.
 --workout how many extras commas are in the text field 
 myInt = myArray.count - 10
 --pass myArray and myInt (extra commas)
 getMyVars myArray myInt
)
 --debug
 --print myVar1
 --print myVar2
 --print myVar3
 --print myVar4

loaded_Str1 should return
"Total_Planning"
"27-9-2012 5:00"
"2-10-2012 2:00"
"Construct"
loaded_Str2 with extra commas should return
"Total_Planning"
"27-9-2012 5:00"
"2-10-2012 2:00"
"Construct"

Hope that helps
billP

[Anonymous]

Thanks Bill for the help, and thank you so much for the elaboration. As you allready guessed i didn't understand anything of what you said before.
I'm still trying to figure out what does exactly what, but the basics are great! It will take me some time to rebuild it to my needs, but who cares about that 🙂

Thanks!

[Anonymous]

Bill thanks again!
I've got a question though: If there would be a comma inside the Total_planning field, and I would want the MyVar1 to be: MyVar1 = Total Planning, how would i achieve that?

loaded_Str2 = "#,Total,Planning,,,text,,field,,,27-9-2012 5:00,2-10-2012 2:00,Construct"

A simple:

myVar1 = delimitedArray + delimitedArray 

Would achieve this, but it would also show me the 3rd array value for all other strings. Which i dont want.

I want it to read the "Total, Planning" text field as 1 field. (Given the fact that it is possible to have multiple text fields, all with possibly multiple comma's)

[Anonymous]

Do you know what comes after the Total planning ?
ie:
"#,Total,Planning,???,,text,,field,,,27-9-2012 5:00,2-10-2012 2:00,Construct"
a number or a string ?? and is it always the same type?

[Anonymous]

Yes I do know, it is always a number.
A normal line in the .csv looks like this: (S = string, D=Date)
#,S,#,#,D,D,#,D,D,S,S,S,S,#,#,S,S,#,#,S,S,S,S,S,S,S,S,S,S
Out of which im only interested in entities: 2 (S), 8 (D), 9 (D), 10 (S), 20 (S)
And where only entity 2 (the string) can contain extra comma's.

[Anonymous]

good 🙂
If you know a number follows the string you want
you can test for it.

Short example
S1 = #,Total Planning,1
S2 = #,Total,Planning,9
S3 = #,Total,Plan,ning,3

you want 2 but 3 and 4 or more may also have parts of your data.
But a nice number follows the data you want.

This gives you some logic to work with:
collect array element 2 always collected
check array am i a number ??
no->check next element in the array for number.
yes->the end of the data wanted was in the last element 🙂

Starting at 2 and looping forward for the short examples
S1 ->Yes (all of the string is in )
S2 ->No->Yes (need and to get all of the string)
S3 ->No->No->Yes (need to to get all of the string)

A good rule of thumb:
All the data in the file/string can be helpful even if you
don't want or need it.

billP

[Anonymous]

I know clear as mud
and how do I check for a number if we converted the csv string into an array of strings ?
maxscript variables don't care what type they are but they can be smart(ish).

Try these in the listener

myVar = "iamnotanumber" as integer

string to int = undefined

myVar = "one" as integer

string as int = undefined

myVar = "1" as integer

string as int = 1 = smart 🙂

we can use this to fix the getMyVars function

Currently we have:

--Vars Collection Function
fn getMyVars delimitedArray addToInt =
(
 --this var is before the text field so nothing is added
    myVar1 = delimitedArray 
 --the rest of the vars are after the text field 
 myVar2 = delimitedArray
 myVar3 = delimitedArray
 myVar4 = delimitedArray
 
)

this line we need to collect anyway.(the data wanted always starts here)

myVar1 = delimitedArray

and we need to check delimitedArray once to see if its a number(data finished ??)
if its not a number look in the next element and so on until we hit a number.

a do while loop seems like a good candidate because it will check at least once

gotit = false --a bool set to false until we find a number
indexVar = 3 --a temp var used to keep track of where in the array we are.
do(
tmpVar = delimitedArray as integer --check element for a number
 if tmpVar != undefined then
 (
 --tmpVar is not undefined so we got a number :) 
 gotit = true
 )
 else
 (
 --tmpVar is undefined we got a string
 --move to next element
 indexVar = indexVar + 1
 )
)while gotit != true --loop

Now we have the end of the data we want
Lets check to see where it is.

if indexVar > 3 then --did we loop or was all the data in 
(
 --we did loop so fix string here
 
 --the last element we looked at was a number so 
 --we need to backup one
 indexVar = indexVar - 1
 for i = 3 to indexVar do
 (
 myVar1 = myVar1 + delimitedArray
 i = i + 1
 )
)

ok hopefully that should have sorted it, update the example script test strings

loaded_str1 = "#,Total_Planning,1,,text field,,,27-9-2012 5:00,2-10-2012 2:00,Construct" 
--extra commas in  and in text field csv string(+2)
loaded_Str2 = "#,Total_Pla,nn,ing,55,,text,,field,,,27-9-2012 5:00,2-10-2012 2:00,Construct" 

and update the getMyVar function

--Vars Collection Function
fn getMyVars delimitedArray addToInt =
(
 --this var is before the text field so nothing is added
    myVar1 = delimitedArray 

 gotit = false --a bool set to false til we find a number
 indexVar = 3 --a temp var used to keep track of where in the array we are.
 do(
 tmpVar = delimitedArray as integer --check element for a number
 if tmpVar != undefined then
 (
 --tmpVar is not undefined so we got a number :) 
 gotit = true
 )
 else
 (
 --tmpVar is undefined we got a string
 --move to next element
 indexVar = indexVar + 1
 )
 )while gotit != true --loop while not true

 if indexVar > 3 then --did we loop or was all the data in 
 (
 --we did loop so fix string here
 
 --the last element we looked at was a number so 
 --we need to backup one
 indexVar = indexVar - 1
 for i = 3 to indexVar do
 (
 myVar1 = myVar1 + delimitedArray
 i = i + 1
 )
 )
 --the rest of the vars are after the text field 
 myVar2 = delimitedArray
 myVar3 = delimitedArray
 myVar4 = delimitedArray 
)--end of getMyVars

The output should be the same as before.
Let me know how you get on
billP

[Anonymous]

LOL, Thanks Bill! I´ll try this as soon as i´m done touching up the part of the script i´m working on right now. All this scripting looks so easy once you´ve found the logic, but i know it is not.
Therefore: THANKS!

[Anonymous]

Hey Bill,

i'm testing the script right now and it works fine!
I made some very minor tweaks to it:
I removed the Filterstring2 function and am now using:

myArray = filterString loaded_Str1 "," splitemptytokens: True

I inserted an if fuction in the MyVars function right beneath myVar1 = delimitedArray:

 myVar1 = delimitedArray 
if addToInt != 0 then...

as it is useless to run the addtoint function if it is 0, because then

myVar1 = delimitedArray

is always true.
Besides it gave me an error on the first line of the .csv file where i dont have any numbers, which i corrected in this way.