Page 1 of 1

Parsing sync'd datafile

Posted: February 20th, 2018, 3:34 am
by Marc
Hello everyone,

I am a new user to CSPro (2 weeks) as I was recently brought on as a programmer in a department that uses it. I am really blown away by how easy it is to use, and have already created some stellar surveys that utilize android's GPS and Camera, and also successfully sync back to dropbox. I doubt I could have accomplished all this so quickly and painlessly with another program.

That being said, I wanted to reach out to the community here to see if someone has already done what I would like to do. At our shop, we feel it would be easier for our reporting to occur if we use a DB to house the data after we are done using CSPro as our front-end to capture that data. To this effect, I would like to create a script (python?) on our server that runs and checks for a recently added data file from the field and then parses that data file for insert into the DB.

This script would run periodically quite often in an attempt to achieve as near to real time data as possible. Understanding CSPro's usage, I'm very aware such a parsing script would be 2 fold - 1 to actually parse the file, and 2 to act as a "key" to explain the dictionary (Field Name/start/length) to that parse.

Has anyone created or explored a parsing script that can be passed a dictionary definition sheet to act as a key for parsing?

Or perhaps, are there any plans upcoming to include the likes of an XML data file as an output sync option?

I know I could download the file manually and use the numerous export options that are available in the CSPro software itself, but my initial aim is to try and automate this process and again achieve a seamless, real-time quality from the data entry back to the central server.

Thank you all for your time. I look forward to continuing to develop in this software and to become a helpful member of this community.

Cheers,
Marc

Re: Parsing sync'd datafile

Posted: February 20th, 2018, 5:57 am
by htuser
Hi Marc,
Cspro already store data also in MySQL using Csweb. But unfortunately, until now, the Cspro system (Csentry and Csweb) doesn't parse questionnaire in multiple columns, lines and tables. However, Csweb already have all classes for parsing.
csweb\src\api\src\CSPro\Data\Parser.php
csweb\src\api\src\CSPro\Dictionary\Dictionary.php
csweb\src\api\src\CSPro\Dictionary\Parser.php

Normally, in PHP,the way provided by Josh is to Query the database to get the dictionary as a text string and then use CSPro\Dictionary\Parser to create a dictionary object. For an example see LoadDictionary() in helpers.php. Once you have the dictionary create a CSPro\Data\Parser from the dictionary and the blob ($row['questionnaire']). Once you have that you can call its getItemValue() method for each of your items to get the values from the blob.

Once you understand the process in PHP, i hope you will be able to translate it in python codes. Since i worked on it last year,with a precious and uncredible support of Josh, and i'm also interested by your project, i'm available for further help.
I would like to create a script (python?) on our server that runs and checks for a recently added data file from the field and then parses that data file for insert into the DB. This script would run periodically quite often in an attempt to achieve as near to real time data as possible.
Please, take a look on this ready to use Github project: https://github.com/mauroIstat/CsPro2Sql
It's a similar completed project, but written in Java. I hope you'll be able to understand and transform it in Python codes.

Waiting for Greg, Josh for more support!

Hope this help you.

Re: Parsing sync'd datafile

Posted: February 20th, 2018, 6:58 am
by josh
The closest that we have seen to what you want is the CSPro2Sql project (Java). It is meant to be run in a cron job to update a truly relational database used for reporting from the CSWeb database. It has been developed by ISTAT (Italian Statistics) as part of a joint project we are both working on.

We don't have anything in Python but if you really want Python it should not be too hard to port either the PHP or Java code that Gregoire posted. There is also a .NET dictionary parser/data reader posted here: https://github.com/csprousers/tools/tre ... /CSPro.NET although that one only reads the dictionary, it doesn't parse the data.

Re: Parsing sync'd datafile

Posted: February 21st, 2018, 4:30 pm
by Marc
Thank you everyone.

I'll be looking into this on my end in the coming weeks and will keep you posted. I appreciate your feedback and the helpful links.

Re: Parsing sync'd datafile

Posted: June 5th, 2018, 9:55 pm
by sdos_bas
Hello everyone, CSPro2SQL loader returns an error. There were no files loaded. what should be the code on the database.properties?

Re: Parsing sync'd datafile

Posted: June 6th, 2018, 3:15 am
by chean
Hi! I am trying to use the CsPro2Sql to convert our dictionary and data to MySQL. I have successfully created the schema using this step ' CsPro2Sql -e schema -p fies.properties –o microdata.sql'. However, this step 'CsPro2Sql -e loader -p fies.properties –cc' does not load our data to the new mysql schema. Below is the fies.properties:

dictionary=../../LTS-WS(Sample)/FIES/FIES17DE.dcf

# Source CsPro database
db.source.uri=jdbc:mysql://localhost:3306
db.source.schema=fms
db.source.username=root
db.source.password=
db.source.data.table=fies15_dict

# Destination microdata MySQL
db.dest.uri=jdbc:mysql://localhost:3306
db.dest.schema=fms
db.dest.username=root
db.dest.password=

I would appreciate if someone can help me resolve this problem. Thank you in advance.

Re: Parsing sync'd datafile

Posted: June 4th, 2021, 7:26 am
by parthsaha
Dear Josh,

Hope you are safe & taking care of you and your family!

I'm literally struggling to convert the cspro dictionary (mysql blob field) to mysql text fields using CSPro2Sql.

Can you please, list out the steps to follow to convert the mysql blob data to mysql tables using CSPro2Sql ???

It would be of great help as it eliminates the manual data download and report preparation.

Thanks in advance and in anticipation!

Parth Saha
josh wrote: February 20th, 2018, 6:58 am The closest that we have seen to what you want is the CSPro2Sql project (Java). It is meant to be run in a cron job to update a truly relational database used for reporting from the CSWeb database. It has been developed by ISTAT (Italian Statistics) as part of a joint project we are both working on.

We don't have anything in Python but if you really want Python it should not be too hard to port either the PHP or Java code that Gregoire posted. There is also a .NET dictionary parser/data reader posted here: https://github.com/csprousers/tools/tre ... /CSPro.NET although that one only reads the dictionary, it doesn't parse the data.

Re: Parsing sync'd datafile

Posted: June 7th, 2021, 8:50 am
by Gregory Martin
That tool, CSPro2Sql, is maintained by the Istat Cooperation Team. It was designed for older version of CSPro. Your best option now is to:

1) Upload your data to a CSWeb server, even if it is locally run on your machine.

2) Use the steps here to break out the cases to MySQL tables: https://www.csprousers.org/help/CSWeb/d ... tings.html