Upload data to CSV file using CPO

[vigneswaram]

Hi Team,

Could any one please help to upload data to CSV file in CPO.

i have some data in DB and i need to extract data and copy to some CSV file in a dedicated path within CPO server.

Regards:

Vignesh                  

[Shaun Roberts]

You can make the CSV file a target and configure it via OLEDB target and then do whatever to it I would think.

You need to install the MS ACE driver @

http://www.microsoft.com/en-us/download/details.aspx?id=13255

Then you can use the Generic DB target in CPO

Make the hostname/datasource the file name, runtime user as a windows  user with access to the file, and the connection string something like  this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\;Extended Properties="Text;HDR=Yes;Format=Delimited";

I used "C:\test.csv"

--shaun

[Svetlana Kryukova · Accepted solution]

Using an OLEDB would work great if you always saving the same file name. However, a single target might be difficult if you have to write different file names in different workflows, or perhaps different columns.

You could also use the following sequence of activities:

1. Select from SQL Server (or Select from Oracle, or Select from DB2 depending on your database type) to select your data from the table in the database.
   
2. XSL Transform (using output of the Select from ...  activity as Source XML... [Workflow.Select from SQL Server.Result Table.Table Data XML] and parsing the XML to form the CSV output)
   
3. And finally Write to Fiile to save the output to a file.
   

Note that if you are trying to save thousands of lines of data this way, OLEDB approach might be faster and easier on the Orchestrator.

[vigneswaram]

Hi Shaun,

Thanks for replying. I will try these options as mentioned above and will send you the update if any issues.