Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Javascript - output csv file

Posted on 2016-11-20
18
Medium Priority
?
58 Views
Last Modified: 2016-11-20
Hi there,

I need a Javascript that will be called from a batch file and output the content of a csv file; but only the requested columns defined by an argument; it the order defined in the argument.

I need to define the output columns defined as an argument in the command line because the needed columns will change.

Examples:

"dataToRead.csv" CONTENT:
serial,content,url,date,nb,city,where
12,"wine, cheese",http://www.website.com/posting.do?page=138&d=50&fet=2,2016-11-20,6545565,Montréal,sky

COMMAND LINE:
cscript exportcsv.js "dataToRead.csv" "C:\Data\dataOutput.csv" "1,2,6"
EXPECTED OUTPUT to "C:\Data\dataOutput.csv":
"12","wine, cheese","Montréal"

COMMAND LINE:
cscript exportcsv.js "dataToRead.csv" "C:\Data\dataOutput.csv" "1,2,6,7"
EXPECTED OUTPUT to "C:\Data\dataOutput.csv":
"12","wine, cheese","Montréal",sky

COMMAND LINE:
cscript exportcsv.js "dataToRead.csv" "C:\Data\dataOutput.csv" "4,3"
EXPECTED OUTPUT to "C:\Data\dataOutput.csv":
"2016-11-20","http://www.website.com/posting.do?page=138&d=50&fet=2"

Thanks for your help,
Rene
0
Comment
Question by:ReneGe
  • 9
  • 9
18 Comments
 
LVL 83

Expert Comment

by:leakim971
ID: 41894854
why javascript ? what is your OS ?
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894860
Because i need to run it from a Windows batch file.
0
 
LVL 83

Expert Comment

by:leakim971
ID: 41894861
and your OS is ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 10

Author Comment

by:ReneGe
ID: 41894867
Windows 8.1 Pro
0
 
LVL 83

Accepted Solution

by:
leakim971 earned 2000 total points
ID: 41894876
You may us Powershell :
$columns = 'serial','content','city'
Import-Csv 'dataToRead.csv' | Select $columns | Export-Csv -Path 'result.csv' –NoTypeInformation

Open in new window

https://msdn.microsoft.com/en-us/powershell/scripting/setup/starting-windows-powershell

PowerShell.exe -Command script.ps1
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894894
Thanks for your solution.
By right clicking on it then clicking on "Run with PowerShell" it works.

1) I'm unable to run it from a batch file:
Command line: PowerShell.exe -Command OutputCSV.ps1
Error message: OutputCSV.ps1 : The term 'OutputCSV.ps1' is not recognized as the name of a cmdlet, function, script file, or operable program.
2) Is there a way to exclude data rows when a field is empty?  For example, in 'serial','content','city', if 'city' is empty, do not output this data row.

Thanks and cheers,
Rene

:)
0
 
LVL 83

Expert Comment

by:leakim971
ID: 41894899
in your batch, set path before running the command else it don't find the ps1
0
 
LVL 83

Expert Comment

by:leakim971
ID: 41894900
for your second question, no
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894930
Its working :)
Is there a way I can output it in my batch file windows instrad to a file?  This way, I'll clean up its content in a FOR /F command.
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894935
The following works and is output to my shell window.  But i need it to be formatted as csv.
Import-Csv 'C:\BatchFiles\E.E\OutputCSV.csv' | Select $columns
0
 
LVL 83

Expert Comment

by:leakim971
ID: 41894936
aha you want your batch modify itself ?
forget it.
just use del command if needed...
you can do lot more on this with powershell, be sure to create any ne question on this zone
0
 
LVL 83

Expert Comment

by:leakim971
ID: 41894940
you omit the second part of the original commande, the export-csv part
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894941
No, that not what I meant

Forget about the batch file.  How can I output "Import-Csv 'C:\BatchFiles\E.E\OutputCSV.csv' | Select $columns" in the shell window and in csv format?

Cheers mate :)
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894945
Import-Csv 'C:\BatchFiles\E.E\OutputCSV.csv' | Select $columns | Export-Csv

I get the following error message:
Export-Csv : You must specify either the -Path or -LiteralPath parameters, but not both.
0
 
LVL 83

Expert Comment

by:leakim971
ID: 41894946
Check my original post, scroll to the rigth, you miss this part
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894949
But your original post exports to a file, not the shell window!
Am I missing something?
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41894972
Here is the final Powershell command line that works, by also expluding the rows where "city" is empty.
Import-Csv 'dataToRead.csv' | Select $columns | Where-Object {$_.city -ne ""} | Export-Csv -Encoding utf8 –NoTypeInformation -Path 'result.csv'

Thanks you sooo much for your help :)
1
 
LVL 83

Expert Comment

by:leakim971
ID: 41894994
great!
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Loops Section Overview

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question