Hi, I am trying to find a way to extract data from RM and view it in either Excel or Access. I have created a custom report that shows individuals occupations for each census date (I use the census description for their occupation). The RM report generates ok but when saving it to excel format the data combines and corrupts. If an individual has more than one census record and one or more occupations (census description) the dates appear in one Excell column like 184118511861 etc the descriptions do the same. The result being a mess in excell which whilst it could be worked out would take ages.
Anyone found a better way to extract RM data?
If you save the report as a Comma-separated Values (.csv) file, with the new file open, right click the column heading with the dates in and select Format Cells. Then choose Date and your required Type as the format.
This should display the years as separate entities.
EDIT:
for the census descriptions, format the cells as Text
One can use sqlite queries directly on the database or connect Excel or Access to it through ODBC. However, your custom report should save to XLSX correctly. The question is, why not? Is there a bug in RM or is it something about your report settings? Can you post a screenshot each from the report and from the spreadsheet of corresponding records having this issue? That might help us understand better what you are describing. Presumably you are on the latest RM9. And which OS?
1 Like
Hi, and thank you for your reply. I will have to did deep into my grey brain cells to work out sqlite again or ODBC.
I am attaching screenshots the files for the RM report as well as the spreadsheet results. (RM community will not allow the actual files)
.
also below the criteria for the RM report.
I am using RM9 on a windows pc.
Right. Iād forgotten how poorly RM exports what looks like a table from Custom Reports. Whether to XLSX, CSV or TXT, the results are unusable when there are blank cells. The same was true for TXT files in RM7.
As @TomH mentioned ODBC connector & Excel is quite easy to use if you have reasonable Excel skills, and have a basic understanding of tables.
It also depends on your objectives of using joins and look ups.
Kevin
Hi, Yes my excel and access skills are pretty reasonable. However I am having trouble in getting the ODBC link to work, even after trying to follow the instructions on other links here. I get as far as Access asking for a DSN and then get stuck. Any help to get this working will be appreciated.
Thanks to all who replied. My persistence has paid off and I am now able to view RM tables in Access. The problem was my own fault, I was initially using sqlite 32 bit and the links were not showing in Access. Once I downloaded the 64bit version all fit together well. Now I am sure I will be able to manipulate the data to correctly do my project on occupations of ancestors over time.
Your replies encouraged my perseverance, after all a computer only understands 0 and 1! Just had to get them in the right order. Thanks again.
John
1 Like