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

dominic.literal
Contributor

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.

 

Regards,

Dom

 

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="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
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%
pause

3 ACCEPTED SOLUTIONS

Accepted Solutions
ben_speight
Super Contributor

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

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.

View solution in original post

MarkWarren
Expert

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

rm is the command you need there.
I'll use Ben's link from above to help here:
http://www.gnu.org/software/coreutils/manual/html_node/rm-invocation.html#rm-invocation

View solution in original post

ben_speight
Super Contributor

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

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)

View solution in original post

8 REPLIES 8
MarkWarren
Expert

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

It's not something I've tried, my colleague @christophe_keom might have done.
The script below for the conversion will only work on Windows.
I've heard that it can be done with LibreOfiice and Python but here's a few ideas:
5 Methods to Convert xlsx Format Files to CSV on Linux CLI (linoxide.com)

dominic.literal
Contributor

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

@MarkWarren 

 

Hi,

 

Thanks for your input.

Will check also this one with client. Thank you.

 

Regards,

Dom

ben_speight
Super Contributor

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

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.

View solution in original post

MarkWarren
Expert

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

kudos for reading the whole question, I missed this
dominic.literal
Contributor

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

@ben_speight 

 

Hi,

 

Thank you for the input.

We are still checking this code you provided. Will update you on this also.

Aside on this, could you please share any code to delete/remove the csv file (e.g. destinationcsvfile.csv) in Linux.

 

Thanks in advance.

 

Regards,

Dom

MarkWarren
Expert

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

rm is the command you need there.
I'll use Ben's link from above to help here:
http://www.gnu.org/software/coreutils/manual/html_node/rm-invocation.html#rm-invocation

View solution in original post

ben_speight
Super Contributor

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

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)

View solution in original post

ballzpop
Not applicable

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

I have a excel file in unix machine and have to convert it into a .csv file.I have to do this from a unix script.How do we do this? tomzpot mobdro