Anaplan Connect: Convert excel file to csv file in script using Linux environment


Dear Expert,


Good day!

Would like to seek your inputs with regards to converting a excel file to csv file in the script using Linux environment (Version: SLES 11 SP4). Please advise. Attached file is the excel file to retrieve the current date (sourcexlsFile.xlsx) and csv file as the expected result(destinationcsvfile.csv) and ExcelToCsv.txt (save as ExcelToCsv.vbs) is the vba code to convert the excel file to csv. In windows script, tried this one and working. Please see attached file as reference also. Thank you in advance.





Anaplan Connect script (for Windows):


@Echo off
setlocal enabledelayedexpansion

del "filelocation\destinationcsvfile.csv"
ExcelToCsv.vbs sourcexlsFile.xlsx destinationcsvfile.csv

set AnaplanUser="email:password"

set WorkspaceId="workspace id"
set ModelId="model id"
set ServiceUrl=""
set AuthUrl=""
set Chunksize=1

set FileName="destinationcsvfile.csv"
set FilePath="FilePath\destinationcsvfile.csv"
set ProcessName="Update Date"
set DumpName="Client Location for Dumping the Errored data from Import"

set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -chunksize %Chunksize%
set Operation=%Operation% -file %FileName% -put %FilePath% -process %ProcessName% -execute -output %DumpName%

rem *** End of settings - Do not edit below this line ***

setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
set Command=.\AnaplanClient.bat %Credentials% %Operation%
@Echo %Command%
cmd /c %Command%

Best Answers

  • ben_speight
    Answer ✓

    If all you need is the current date then using a large application to produce it is overkill, and can be replaced with a simpler shell script line such as


    echo '"Reporting Date","'$(date -Idate)'"' >destinationcsvfile.csv


    If -Idate does not produce the right date format for you, read the man page for date and adjust accordingly.

  • MarkWarren
    Answer ✓
    rm is the command you need there.
    I'll use Ben's link from above to help here:
  • ben_speight
    Answer ✓

    The rm command is used to remove files, but should always be used with caution, and especially from automated scripts. If a command's output is redirected to a file with a single chevron (>) then it will truncate the file and replace any existing content, so you might not need to remove it (if you use two chevrons (>>) then the output is appended to the file instead)