VBScript CSV to JSON

'CSVToJSON

'Purpose : Convert an existing CSV file, to a JSON file.

'Method: drag a file over this script. It will parse your textfile and convert it to a JSON file.

set fso = wscript.createobject("scripting.filesystemobject")


if wscript.arguments.count > 0 then

 for each x in wscript.arguments

  iLines = 0

  strOutFile = x

  fso.movefile x, replace(x,".csv", "_input.csv")

  set objInFile = fso.opentextfile(replace(x,".csv", "_input.csv"))

  set objOutFile = fso.createtextfile(replace(x, ".csv", ".json"))

  

  'read header

   objOutFile.writeline"var data={""users"":["

   strHeader  = objInFile.readline

   strHeader = replace(strHeader, """", "")

   arrTitles = split(strHeader, "|")

   
  do until objInFile.AtEndOfStream

   strLine = objInFile.readline

   strLine = replace(strLine, """","")

   arrLine = split(strLine, "|")

   iCount=0

   iLines = iLines + 1

   objOutFile.writeline "{"

   do while iCount <= ubound(arrTitles)

    if iCount < ubound(arrTitles) then

     objOutFile.writeline """" & replace(arrTitles(iCount), " ", "_") & """" & ":" & """" & arrLine(iCount) & """" & ","

    else

     objOutFile.writeline """" & replace(arrTitles(iCount), " ", "_") & """" & ":" & """" & arrLine(iCount) & """"

    end if

    iCount = iCount +1

   loop

   objOutFile.writeline "},"

  loop
  objOutFile.writeline "{"
  objOutFile.writeline """O"":""O""}], ""count"":""" & iLines & """"
  objOutFile.writeline "}"
 next
 msgbox "Done."
else
 msgbox "drag csv files over this script for it to work !"
end if

Business days between two dates (in Business Objects)

A colleague of mine asked me: is it possible to calculate the amount of working days between two dates. Immediately, I thought : of course, but as it turns out, it's not that easy.

A quick google and I found this formula:

=DaysBetween(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin])));RelativeDate([End];Floor(DayNumberOfWeek([End])/6)*(8-DayNumberOfWeek([End])))) -Floor(DaysBetween(RelativeDate(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin])));(DayNumberOfWeek(RelativeDate([Begin];Floor(DayNumberOfWeek([Begin])/6)*(8-DayNumberOfWeek([Begin]))))*-1)+1);RelativeDate([End];Floor(DayNumberOfWeek([End])/6)*(8-DayNumberOfWeek([End]))))/7)*2

I'm sure there's an easier way, and I'm working on it, but for the time being, it does the job just nicely. Thanks to this guy's post: http://blog.davidg.com.au/2012/11/workdays-between-two-dates-in-webi.html

Thanks mate. Loved the article.

Peter