The process explained here needs to be performed by program evaluated at least on an annual basis.
The reason for all the following hassle is that EndNote does not provide API at the time of this writing to connect to Qlik Sense app directly. Part of it is that EndNote does not have any cloud base solution. So, a REDCap project has been created to carry the data and that app is connected to Qlik Sense.
Import EndNote XML File into Excel
EndNote has dissemination data and the library is saved under the following place in N drive:
"N:\ADMIN\WI Partnership Program\Evaluation & Planning\Dissemination Library and EndNote\
In the same folder, there is an xml file that we need for our import.
NOTE: If the xml file isn't up to date or doesn't exist, then whoever has access to EndNote should be able to create it by choosing "All References" from the right hand side menu bar > on the main area where the list is showing select all (Ctrl+A) > From top menu bar select "File" > "Export ..." > In the popup from the dropdown below the file explorer area, for "Save as Type" choose "XML" > make sure the location is correct > Save
"N:\ADMIN\WI Partnership Program\Evaluation & Planning\Dissemination Library and EndNote\EndNotePublications.ALL Records.xml"
The xml file needs to be imported into an excel. To do so, open a new excel sheet and from the menu choose
Data > Get External Data > From Other Sources > From XML Data Import
Click OK if any pop up appears.
Note that it will take a few minutes to load as the file is very big. The data in this sheet is what needs to be copied into the next step.
Reformat The data
In N drive the following folder has the macros we need to run in order to format the data properly and also other templates we need for our next steps:
"N:\ADMIN\WI Partnership Program\Tools\Dissemination Macros and Templates\
We need to open the following spreadsheet:
"N:\ADMIN\WI Partnership Program\Tools\Dissemination Macros and Templates\Dissemination_Macros.xlsm"
We have to copy the data from the spreadsheet we created in previous step and copy it entirely into the second tab of this sheet.
The first tab has instructions and steps:
Some steps have buttons that run a macro and some requires you to manually do something. Macros take a long time to run (several minutes) since we are dealing with a large dataset which has more than 150k rows. Please make sure you wait until it is done to run the next step (macro).
After each step is finished, a success message will pop up:
Ignore the following warning if it pops up:
After running through all 4 steps, we need to import data into REDCap.
Import Data into REDCap
Whoever is importing into REDCap, has to have access to the project "WPP Dissemination". They have to have at least "Data Import" privileges.
From the same tools folder in N drive:
"N:\ADMIN\WI Partnership Program\Tools\Dissemination Macros and Templates\"
Open the "REDCap_Dissemination_Template.csv" file. Copy the data only (without headers, just rows) from second tab of "Dissemination_Macros.xlsm" into the "REDCap_Dissemination_Template.csv" template below the headers:
Make sure to save it. The file can now be imported.
Login to redcap:
Choose the "WPP Dissemination" project.
From the left hand side menu bar, under "Application" choose "Data Import Tool" and then scroll down to the bottom to choose and then upload the template file we just created in our previous steps.
Since we are dealing with a large dataset, it takes a bit for the data to load.
The result shows what is being changed and what is new. Scroll all the way to the bottom. and click the button to import.
After the next schedule load on Qlik, please check the data in Qlik to make sure everything looks fine.
Technical Support
Do not Run the same button twice. It won't work and it will error out on you. Although, it will not damage anything.
I will attach a copy of the macro enabled file here for scenarios that the macro-enabled file is damaged, then replaced it with the one attached here.
If that doesn't work either:
Reboot before moving to the next step.
Create a new empty excel enabled macro file
Copy the subs and functions from the visual basics of the damaged file. They are all saved under module 1.
Create a new module in the new xlsm file and paste the copied subs in there.
Create the first tab exactly as it is supposed to be. You can refer to a screenshot earlier on this page. Please be careful to assign the proper macro to proper buttons. These are regular buttons and not ActiveX ones.
Create a second tab also for the data.