topic Re: Exporting as csv in Alfresco Archive

Exporting as csv

johan1974 — Mon, 28 Apr 2008 11:50:58 GMT

Hello,I like to export some data with a webscript in a csv format. Where can i find a good explanation how to do this?Thnx!

Re: Exporting as csv

zaizi — Mon, 28 Apr 2008 12:17:15 GMT

Hi,

Read through the webscript wiki page at http://wiki.alfresco.com/wiki/Web_Scripts.

Set the response format to text. This is the format of the response data when the web script is called.

In your freemarker template just loop through and output the required fields separated by commas. You can also try using a macro to enclose fields in quotes or escape any commas in the field.

Ainga

Re: Exporting as csv

johan1974 — Tue, 29 Apr 2008 07:35:33 GMT

I did read this article but it gives me little info on how to build such csv file. I found an example called "docaudit" at the content community "My Alfresco > Webinar Slides > Alfresco Webscripts Webinar" but when i execute this example i get the same error i got with my own script.

Alfresco    Web Script Status 500 - Internal Error

The Web Script /alfresco/service/docaudit/User Homes/Administrator/setuplog.txt.csv has responded with a status of 500 - Internal Error.

500 Description:   An error inside the HTTP server which prevented it from fulfilling the request.

Message:   Web Script format 'csv' is not registered

Exception:   org.alfresco.web.scripts.WebScriptException - Web Script format 'csv' is not registered

   org.alfresco.web.scripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:93)
   org.alfresco.web.scripts.WebScriptRuntime.wrappedExecute(WebScriptRuntime.java:364)
   org.alfresco.web.scripts.WebScriptRuntime$1.execute(WebScriptRuntime.java:330)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:241)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:171)
   org.alfresco.web.scripts.WebScriptRuntime.transactionedExecute(WebScriptRuntime.java:341)
   org.alfresco.web.scripts.WebScriptRuntime.authenticatedExecute(WebScriptRuntime.java:284)
   org.alfresco.web.scripts.WebScriptRuntime.executeScript(WebScriptRuntime.java:150)
   org.alfresco.web.scripts.WebScriptServlet.service(WebScriptServlet.java:109)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
   org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
   org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
   org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
   org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
   org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
   org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
   org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
   org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
   org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
   org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
   org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
   org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
   java.lang.Thread.run(Thread.java:619)

Server:   Alfresco Enterprise Network v2.2.0 (159) schema 89
Time:   29-apr-2008 9:53:27

Diagnostics:   Inspect Web Script (docaudit.get)

I did refresh at the service/index page. Do i need to do more registration for an csv file to create?

I would like a simple small working example that i can use to expand and learn from.

Thnx!

Re: Exporting as csv

dinger — Tue, 29 Apr 2008 17:21:54 GMT

You'll need to create your own web script that converts the output into csv.

See the webscripts examples in the wiki!

Rob

Re: Exporting as csv

johan1974 — Tue, 29 Apr 2008 19:08:28 GMT

I tried this and i can get some html and xml output. But i can not get a good working csv output. In the examples you mention are no csv examples.

Re: Exporting as csv

zaizi — Wed, 30 Apr 2008 11:02:37 GMT

Hi,

CSV is just file formatting for what is essentially a text file with mime type 'text/plain'. Quick solution is to thus use .txt format on the webscript to respond with a text file and use freemarker to render the contents comma separated.

If you want the response to have a different mime type such as 'text/csv' or 'application/excel' then you will need to write your own custom java backed web script.

Which one did you have in mind?

Ainga

Re: Exporting as csv

johan1974 — Wed, 30 Apr 2008 11:50:23 GMT

At the moment i am interested in a csv response. Is it possible to see a small example where i can work with ?

Re: Exporting as csv

mikeh — Wed, 30 Apr 2008 12:06:30 GMT

Rename setuplog.txt.csv to setuplog.csv.ftl

Thanks,
Mike

Re: Exporting as csv

johan1974 — Wed, 30 Apr 2008 13:10:48 GMT

Setuplog.txt.csv is not the actual ftl file. It happens to be in the folder i am looking.

For example when i look in the data Dictionary i get the same error but with the name of the data dictionary.

Alfresco    Web Script Status 500 - Internal Error

The Web Script /alfresco/service/docaudit/Data Dictionary.csv has responded with a status of 500 - Internal Error.

500 Description:   An error inside the HTTP server which prevented it from fulfilling the request.

Message:   Web Script format 'csv' is not registered

Exception:   org.alfresco.web.scripts.WebScriptException - Web Script format 'csv' is not registered

   org.alfresco.web.scripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:93)
   org.alfresco.web.scripts.WebScriptRuntime.wrappedExecute(WebScriptRuntime.java:364)
   org.alfresco.web.scripts.WebScriptRuntime$1.execute(WebScriptRuntime.java:330)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:241)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:171)
   org.alfresco.web.scripts.WebScriptRuntime.transactionedExecute(WebScriptRuntime.java:341)
   org.alfresco.web.scripts.WebScriptRuntime.authenticatedExecute(WebScriptRuntime.java:284)
   org.alfresco.web.scripts.WebScriptRuntime.executeScript(WebScriptRuntime.java:150)
   org.alfresco.web.scripts.WebScriptServlet.service(WebScriptServlet.java:109)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
   org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
   org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
   org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
   org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
   org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
   org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
   org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
   org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
   org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
   org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
   org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
   org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
   java.lang.Thread.run(Thread.java:619)

Server:   Alfresco Enterprise Network v2.2.0 (159) schema 89
Time:   30-apr-2008 15:06:19

Diagnostics:   Inspect Web Script (docaudit.get)

Re: Exporting as csv

mikeh — Wed, 30 Apr 2008 15:15:44 GMT

Ok, then the question should be: why are you trying to execute the .csv file as a webscript? That is why you're getting the exception - do you mean to pass-in the path to the .csv to the webscript?

Mike

Re: Exporting as csv

johan1974 — Wed, 30 Apr 2008 19:22:03 GMT

hmm it looks you are right. The starting line is

http://localhost:8080/alfresco/service/docaudit/Data%20Dictionary.csv?format=csv&d=&u=&s=userIdentifier

So it seems the demo is not working correctly. I am getting lost here now. Any suggestions?

Re: Exporting as csv

mikeh — Wed, 30 Apr 2008 21:05:51 GMT

I'm not familiar with that particular example webscript, but it sounds like it's not really an avenue worth exploring unless you're already comfortable developing webscript of your own.

I would start off at the beginning: create a "hello world" webscript, e.g. johan.get.csv.ftl with the content

"hello", "world"‍

Once you've got that foundation in place, you can build on adding functionality without also having to understand somebody else's work.

Thanks,
Mike

Re: Exporting as csv

johan1974 — Thu, 01 May 2008 11:19:08 GMT

Ok i am trying a basic helloworld example. The html works fine but still has some trouble with the csv. I have now this :

helloworld.get.desc.xml 

<webscript>
  <shortname>Hello World</shortname>
  <description>Hello World</description>
  <url>/HelloWorld</url>
  <format default="html">argument</format>
  <authentication>user</authentication>
  <transaction>required</transaction>
</webscript>
‍‍‍‍‍‍‍‍‍‍‍

helloworld.get.html.ftl

<html>
<HEAD></HEAD>
<BODY>
<h1>Hello World</h1>
</BODY>
</html>
‍‍‍‍‍‍‍‍‍

helloworld.get.csv.ftl

"hello", "world"

‍‍‍‍‍

When i use the url http://localhost:8080/alfresco/service/HelloWorld i get perfectly the message hello world, no problem there. But when i want to call the csv i use http://localhost:8080/alfresco/service/HelloWorld?Format=csv i get the default html output.

I also tried to change the helloworld.get.csv.ftl to helloworld.get.txt.ftl and then call http://localhost:8080/alfresco/service/HelloWorld?Format=txt but with the same results.

What am i forgetting here? :?

Re: Exporting as csv

mikeh — Thu, 01 May 2008 11:36:11 GMT

Try this URL: http://localhost:8080/alfresco/service/HelloWorld.csv

If you want http://localhost:8080/alfresco/service/HelloWorld to bring back the csv template instead of html, update the description file to:

<format default="csv"></format>‍

Thanks,
Mike

Re: Exporting as csv

johan1974 — Thu, 01 May 2008 14:00:25 GMT

Still no luck, i tried both your suggestions and got this error again.

Alfresco    Web Script Status 500 - Internal Error

The Web Script /alfresco/service/HelloWorld.csv has responded with a status of 500 - Internal Error.

500 Description:   An error inside the HTTP server which prevented it from fulfilling the request.

Message:   Web Script format 'csv' is not registered

Exception:   org.alfresco.web.scripts.WebScriptException - Web Script format 'csv' is not registered

   org.alfresco.web.scripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:93)
   org.alfresco.web.scripts.WebScriptRuntime.wrappedExecute(WebScriptRuntime.java:364)
   org.alfresco.web.scripts.WebScriptRuntime$1.execute(WebScriptRuntime.java:330)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:241)
   org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:171)
   org.alfresco.web.scripts.WebScriptRuntime.transactionedExecute(WebScriptRuntime.java:341)
   org.alfresco.web.scripts.WebScriptRuntime.authenticatedExecute(WebScriptRuntime.java:284)
   org.alfresco.web.scripts.WebScriptRuntime.executeScript(WebScriptRuntime.java:150)
   org.alfresco.web.scripts.WebScriptServlet.service(WebScriptServlet.java:109)
   javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
   org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
   org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
   org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
   org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
   org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
   org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
   org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
   org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
   org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
   org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
   org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
   org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
   java.lang.Thread.run(Thread.java:619)

Server:   Alfresco Enterprise Network v2.2.0 (159) schema 89
Time:   1-mei-2008 15:58:19

Diagnostics:   Inspect Web Script (helloworld.get)

Re: Exporting as csv

mikeh — Thu, 01 May 2008 16:04:48 GMT

Sorry, my bad.

You'll either have to go with "txt" instead of "csv" or add csv as a webscript format. See this article on the wiki for details on how to do that.

Thanks,
Mike