asked on

Add unqiue key into tables from XML import - Access Database

I am importing an XML file generated by a third party application into Microsoft Access, the process works but each of the tables it creates does not import with a unique reference for me to tie everything together, so now I'm left without a way to query the data.

Each XML import is the result of a 'company lookup' which brings in data relating to businesses (directors, names, company details, balance sheet etc.). I will be importing lots of different company data into the same tables, so I need an identifier which will be in each table which identifies which company the data is for - i.e. company registration number. The company registration data is held within each XML import.

<CompanyRegistrationNumber>01981238</CompanyRegistrationNumber>

I have attached an example XML file (don't worry this is all publically accessible information so there's nothing confidential), I have also attached how this imports into Access.
Example:01981238.xml

Can someone help me here to explain how I can get a unique identifier (preferably the company registration number) into all of the tables so that I may link it all together?

Many thanks in advance!
Kindest,
Darren

ste5an

What does the documentation of that API says about the ID?

<?xml version="1.0" encoding="UTF-8"?>
<company xmlns="http://companycheck.co.uk/api/company" id="01981238">
  <CompanySummary>
    <CompanyRegistrationNumber>01981238</CompanyRegistrationNumber>

Open in new window

I would guess, that this should do it.

My normal workflow for importing data is to import data always into a local (temporary) staging table first. Then normally follows simply data validation often in addition with a sight check. And now the data is imported into the "real" tables. Well, this process is call ETL.
Use a surrogate key in your "real" tables. This key is assigned in the last step of the import process (the load stage).

Jim Dettman (EE MVE)

The alternative is to open the file in VBA first and grab the CompanyRegistrationNumber. Then use that to tag the records.

You could also use an "ImportID" number to group the records, which you would assign and what ste5an was talking about in the last part of his comment.

Jim.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.