SQLTeam.com | Weblogs | Forums

Export CSV to json with C# script component


#1

I need to export csv file to jason using script component. can anyone guide me how to do it.
I haven't do this trasaction so less knowledge of C# component. I cannot use T-SQL (JSON AUTO) also to convert the file to jason.

I tried but getting below error..

T.I.A

my data in csv
image


#2

why not try it out via powershell?

import-csv "SampleInput.csv" | ConvertTo-Json -Compress | Add-Content -Path "output.json"

#3

getting err
[Execute Process Task] Error: In Executing "PowerShell.exe" "import-csv "C:\New folder\convertcsv.csv" | ConvertTo-Json -Compress | Add-Content -Path "C:\New folder\output.json"" at "", The process exit code was "1" while the expected was "0".


#4

what are you running this from?


#5

or this

Get-Content -path "SampleInput.csv" | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json | Out-File "Booya.json"

#6

I need to use SSIS service only ..no t-sql option for me..so i am executing this on execute process task


#7

same err


#8

Can you show us exactly how you have added that command in your SSIS

Also check this out

http://www.sqlservercentral.com/articles/execute+process+task/129996/


#9

command -
Get-Content -path "C:\New folder\convertcsv.csv" | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json | Out-File "C:\New folder\Booya.json"


#10
  1. please open a PowerShell window as Admin
  2. cd to C:\New folder
  3. run
    Get-Content -path "C:\New folder\convertcsv.csv" | ConvertFrom-Csv -Delimiter ',' | ConvertTo-Json | Out-File "C:\New folder\Booya.json"

what happens?


#11

it succeeded when ran in PowerShell..but failed in SSIS Execute procedure task


#13


#14

Thank you very much!! it worked!!!!


#15

I have below json file data

first i need to convert it to csv and then need it again in json..
I tried online converter for json to csv it gives me below csv file

then I ran execute process task and got json file but its incorrect


not sure first jason to csv was wrong or execute process task went wrong..
plz help..


#16

Is this honework or real life? Why do you have to convert back and forth?

What isbyour final goal in this ssis


#17

Actually I am trying to build package that convert csv to json. and for that I need test file that's reason I am manually converting json file to csv first.. and to validate also..


#18

how did you try to convert that file from json to csv? what tool did yo use? anyways the result you are looking at are actually correct. look at your csv no column names.


#19

I found this: http://raghavendravadde.blogspot.com/2012/03/c-code-to-convert-csv-file-json-file.html

It shows how to convert a CSV file to JSON and is fairly simple. The Powershell code is also quite simple but I would call it differently. It is actually easier to call Powershell from a script component...you can do something like this:

using (PowerShell powerShell = PowerShell.Create())
{
    // Source functions.
    powerShell.AddScript(PsScript);
    powerShell.Invoke();

    // Call function contained in sourced script above.
    powerShell.AddCommand("My-Function");
    powerShell.AddParameter("ParamA", varA);
    powerShell.AddParameter("ParamB", varB);
    powerShell.AddParameter("ParamC", varC);
    powerShell.AddParameter("ParamD", varD);

    results = powerShell.Invoke();
}

When you add the script - it can be an actual file or the actual code with parameters defined...


#20

may be.. thank you for helping me !! appreciated!!