# Extracting data to excel or access **URL:** \ **Category:** RootsMagic\ **Tags:** question\ **Created:** [March 25, 2024, 3:33am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309 "2024-03-25T03:33:00Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Johnco4650](https://avatars.discourse-cdn.com/v4/letter/j/ee59a6/32.png) [@Johnco4650](https://community.rootsmagic.com/u/Johnco4650)\ **Post date:** [March 25, 2024, 3:33am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/1 "2024-03-25T03:33:00Z")
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? ---
**Author:** ![Austin](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@Austin](https://community.rootsmagic.com/u/Austin)\ **Post date:** [March 25, 2024, 12:34pm UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/2 "2024-03-25T12:34:53Z")
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** ---
**Author:** ![TomH](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.rootsmagic.com/tomh/32/30_2.png) [@TomH](https://community.rootsmagic.com/u/TomH)\ **Post date:** [March 25, 2024, 1:07pm UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/3 "2024-03-25T13:07:04Z")
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? ---
**Author:** ![Johnco4650](https://avatars.discourse-cdn.com/v4/letter/j/ee59a6/32.png) [@Johnco4650](https://community.rootsmagic.com/u/Johnco4650)\ **Post date:** [March 26, 2024, 2:30am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/4 "2024-03-26T02:30:17Z")
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) ![excel snip](https://canada1.discourse-cdn.com/flex030/uploads/rootsmagic/original/2X/6/6d0d79997c6c516b821fc617ad77afd35a5bc214.jpeg) ![RM report snip](https://canada1.discourse-cdn.com/flex030/uploads/rootsmagic/original/2X/1/1e13f4fbe6e01bc41dc9f82ca344a957012bc50e.jpeg). also below the criteria for the RM report. ![image](https://canada1.discourse-cdn.com/flex030/uploads/rootsmagic/original/2X/c/cb9c74bc48d6d1bf5c506815476236dfdeefba8c.png) I am using RM9 on a windows pc. ---
**Author:** ![TomH](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.rootsmagic.com/tomh/32/30_2.png) [@TomH](https://community.rootsmagic.com/u/TomH)\ **Post date:** [March 26, 2024, 3:37am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/5 "2024-03-26T03:37:50Z")
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. ---
**Author:** ![kevync1985](https://avatars.discourse-cdn.com/v4/letter/k/3be4f8/32.png) [@kevync1985](https://community.rootsmagic.com/u/kevync1985)\ **Post date:** [March 27, 2024, 1:11am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/6 "2024-03-27T01:11:24Z")
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 ---
**Author:** ![Johnco4650](https://avatars.discourse-cdn.com/v4/letter/j/ee59a6/32.png) [@Johnco4650](https://community.rootsmagic.com/u/Johnco4650)\ **Post date:** [March 27, 2024, 2:06am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/7 "2024-03-27T02:06:09Z")
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. ---
**Author:** ![Johnco4650](https://avatars.discourse-cdn.com/v4/letter/j/ee59a6/32.png) [@Johnco4650](https://community.rootsmagic.com/u/Johnco4650)\ **Post date:** [March 27, 2024, 6:33am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/8 "2024-03-27T06:33:56Z")
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 ---
**Author:** ![kevync1985](https://avatars.discourse-cdn.com/v4/letter/k/3be4f8/32.png) [@kevync1985](https://community.rootsmagic.com/u/kevync1985)\ **Post date:** [April 30, 2024, 1:25am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/9 "2024-04-30T01:25:02Z")
If you are more comfortable with Excel-- Excel Power Queries can do wonders (or power query with Power BI ) (something I started working on this month) ![image](https://canada1.discourse-cdn.com/flex030/uploads/rootsmagic/original/2X/d/d19ddff00b64f0373fcca4a8bada305d28b8a3ae.png) ![image](https://canada1.discourse-cdn.com/flex030/uploads/rootsmagic/original/2X/b/bd5c1f3b07a16324d360f6e84ad665e4c4abb1db.png) ![image](https://canada1.discourse-cdn.com/flex030/uploads/rootsmagic/original/2X/9/9bb811bfa41247b8f2ca28bac17b84066317a867.png) ---
**Author:** ![Johnco4650](https://avatars.discourse-cdn.com/v4/letter/j/ee59a6/32.png) [@Johnco4650](https://community.rootsmagic.com/u/Johnco4650)\ **Post date:** [April 30, 2024, 3:55am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/10 "2024-04-30T03:55:27Z")
Kev, that looks very interesting I will have to explore Power Queries more. Your spreadsheet looks like it may be useful to many users, have you considered sharing it? The main reason I want data in either Excel or Access is to do some analysis on occupations. So far this is showing some interesting results for my UK ancestors. I am able to see the changes over time from starting in mining or textiles to later changes into a wide array of occupations. It is very interesting the way we use different data for our own results. ---
**Author:** ![kbens0n](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.rootsmagic.com/kbens0n/32/80_2.png) [@kbens0n](https://community.rootsmagic.com/u/kbens0n)\ **Post date:** [May 22, 2024, 2:22am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/11 "2024-05-22T02:22:26Z")
The sqlite3.exe utility has an [**.excel** parameter](https://sqlite.org/forum/info/32ada847bf894cc8#:~:text=At%20the%20command%20prompt%3A) that can facilitate some minimal interfacing with Excel/Power BI ---
**Author:** ![garthkh](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.rootsmagic.com/garthkh/32/2358_2.png) [@garthkh](https://community.rootsmagic.com/u/garthkh)\ **Post date:** [December 16, 2024, 12:13am UTC](https://community.rootsmagic.com/t/extracting-data-to-excel-or-access/10309/12 "2024-12-16T00:13:10Z")
I’ve been searching for ways to move a family tree to a spreadsheet, and visa versa. I tried GEDxlate today (it’s free), and it’s really good for converting a GEDCOM into .xlsx format with many options. I’m now trying to find other options to export a Spreadsheet to GEDCOM correctly and easily. I have Excel2GED (also free) that does this. Obviously one has to create the (or have) the family relationships setup by adding ID’s to link records manually. But it does work. I’m needing tools like this to try and recreate GEDCOMs from a broken database I have managed to convert into a large spreadsheet. This may help.