Accessing NVPX data with SQL Queries on Mac

[lyf3wat3r]

Hi Johnathan @jschultz ,

Thank you so much for developing this!! NVIVO GUI is so cumbersome a pain to learn, and not very flexible. I would like to be able to run SQL queries to understand my data.

I followed the steps you provided in the Wiki for accessing Nvivo for Mac files on Mac. I installed python, SQL Anywhere 17, and all packages including sqlalchemy-slqany. I've also converted the windows .nvp file from Nvivo 12 into a Mac .nvpx file.

I don't understand how I can directly access the contents of the .nvpx file from a SQL query client. I normally use JetBrains Data Grip as my client, and connect to Postgres server where I create a DB and then import MS Excel data or .csv data.

I have 0 experience with python but I am fairly skilled at creating structure from raw data in SQL.

Any thoughts on how I can access the contents of the .nvpx file and run my own queries?

Thank you so much,
lyf3wat3r

[jschultz]

What nvivotools (currently) allows you to do is convert your .nvp or .nvpx NVivo project file into a .norm (normalised) file. This is an SQLite file which if you're familiar with SQL you won't have any trouble with. If you want to use a different database engine it's pretty easy to use any one supported by SQLAlchemy, which includes Postgres, MSSQL and plenty of others.

Is that helpful?

[lyf3wat3r]

That makes a lot of sense. The wiki mentions that SLQAnywhere necessitates the use of NormaliseNVPX.sh due to some parameters that need to be set. I'm not seeing that script or the equivalent denormalise SH script in the code.

Is that something that is still necessary?
Do the helper scripts slqanysrv.sh and sqlanyenv.sh also need to be run?

Many thanks

[lyf3wat3r]

Update: I went ahead and tried to run the NormaliseNVPX.py file in OSX terminal, which returned the following error:

File "NormaliseNVPX.py", line 177 os.chmod(tmpinfilename, 0777) ^ SyntaxError: invalid token

I then took the time to read your code in the helper script files.I decided to try and run the helper files sqlanyenv.sh and sqlanysrv.sh, given that the NormaliseNVPX.py returned errors. Running the sqlanyenv.sh script in osx terminal returns the following error:

Found SQLAnywhere installation at /Applications/SQLAnywhere17/System/bin64 sqlanyenv.sh: line 78: syntax error near unexpected token ('
sqlanyenv.sh: line 78: comm -13 <(sort /tmp/sqlanyenv.1) <(sort /tmp/sqlanyenv.2)'

and the sqlanysrv.sh script returns the following error:
sqlanysrv.sh: line 21: : command not found

I did some googling and read that turning on/off globbing in terminal might help (which it did not). The other thing I read was escaping the parenthesis characters with the \ character (which did not work either). These two approaches I tried were for the sqlanyenv.sh scripts. The other two scripts I could not wrap my head around.

Any thoughts on the solutions to these errors?

Thanks again for your insight & support.

[lyf3wat3r]

Update 2: I was able to ge try hands on a windows machine. Ran the NormaliseNVPX.py in command line and returned the errors in the attached doc Python Script Error.txt

I read your note about dbcapi on OSX and its potential relation to SIP, however this error returned on a windows machine. One potential issue that I found was that python cannot location the dbcapi dll from a 64 bit install of SQL Anywhere. So I downgraded both Python and SQL Anywhere to the 32 bit versions but still no luck.

[jschultz]

File "NormaliseNVPX.py", line 177 os.chmod(tmpinfilename, 0777) ^ SyntaxError: invalid token

I suppose this must have worked with an earlier version of Python but it is no good now. I've committed a quick fix update which should fix the problem.

Found SQLAnywhere installation at /Applications/SQLAnywhere17/System/bin64 sqlanyenv.sh: line 78: syntax error near unexpected token (' sqlanyenv.sh: line 78: comm -13 <(sort /tmp/sqlanyenv.1) <(sort /tmp/sqlanyenv.2)'

I don't know why this line of code should be a problem. I believe the piping from a sub-process output is a bash feature so are you running a full bash implementation? But I'm clutching at straws there it doesn't seem very likely. Can you try typing comm -13 <(sort /tmp/sqlanyenv.1) <(sort /tmp/sqlanyenv.2) at a command prompt. It doesn't matter whether the files `/tmp/sqlanyenv.?' exist - it's just to see whether the syntax is accepted.

and the sqlanysrv.sh script returns the following error: sqlanysrv.sh: line 21: : command not found

That is no surprise. That script is for internal use only and is not intended to be used by an end user.

The errors you found with Windows I'm having trouble figuring out right now.

See how you go. It should work under both MacOS and Windows so it would be good to see what's happened here in both cases.

[lyf3wat3r]

Thanks @jschultz. To answer your question I was running using Git Bash to run the SH script.

comm -13 <(sort /tmp/sqlanyenv.1) <(sort /tmp/sqlanyenv.2)
Returned an accepted syntax so that doesn't seem to be problematic.

The fix you committed seems to have resolved the issue with the python script!! I've only had the chance to test it on OSX terminal at this point but I was able to convert my .nvpx project file to a .norm file.

For anyone else who might be reading this, I used JetBrains DataGrip SQL client to connect directly to SQLite .norm file. It was a breeze. I was then able to copy the tables into a Postgres DB without a hitch.

Many thanks @jschultz for the outstanding work! Nvivotools has been hugely impactful to the work that I am doing.

[lyf3wat3r]

Hi @jschultz. Does the .norm file outputted by NormaliseNVPX.py provide the actual chunk of text that was tagged to a code? I see a 'Fragment' column in the Tagging table but it has numerical values which I'm not sure I understand. Is this an issue for Nvivo12 or am I looking in the wrong place? Thank you again.