title: Importing xml file into Access database with defined id
body: <p>I am strugling in order to import an enormous amount of data from xml file into Access.
  The problem I am facing is that files I want to import does contain the first row with id</p>
  <pre><code>&lt;vin id=&quot;11111111111111111&quot;&gt;
  &lt;description&gt;Mazda3 L 2.0l MZR 150 PS 4T 5AG AL-EDITION TRA-P&lt;/description&gt;
  &lt;type&gt;BL&lt;/type&gt;
  &lt;typeapproval&gt;e11*2001/116*0262*07&lt;/typeapproval&gt;
  &lt;variant&gt;B2F&lt;/variant&gt;
  &lt;version&gt;7EU&lt;/version&gt;
  &lt;series&gt;Mazda3&lt;/series&gt;
  &lt;body&gt;L&lt;/body&gt;
  &lt;engine&gt;2.0l MZR 150 PS&lt;/engine&gt;
  &lt;grade&gt;AL-EDITION&lt;/grade&gt;
  &lt;transmission&gt;5AG&lt;/transmission&gt;
  &lt;colourtype&gt;Mica&lt;/colourtype&gt;
  &lt;extcolourcode&gt;34K&lt;/extcolourcode&gt;
  &lt;extcolourcodedescription&gt;Crystal White Pearl&lt;/extcolourcodedescription&gt;
  &lt;intcolourcode&gt;BU4&lt;/intcolourcode&gt;
  &lt;intcolourcodedescription&gt;Black&lt;/intcolourcodedescription&gt;
  &lt;registrationdate&gt;2012-07-20&lt;/registrationdate&gt;
  &lt;productiondate&gt;2011-11-30&lt;/productiondate&gt;
  &lt;/vin&gt;
  </code></pre>
  <p>so the result of my import is all the lines except from the VIN number of vehicle that is actually defined as id.</p>
  <p>I was trying to manually replace characters like:
  
  "> etc. with 
  etc.</p>
  <p>to get rid of that id but I have actually dozens of files and hundreds of thousands records in each file so it is quite a pain...</p>
  <p>so I thought about concatinating all files together with a script in python:</p>
  <pre><code>import os 
  import csv
  import pandas as pd
  import numpy as np
  
  ver='2011'
  
  dirName =r'C:\Users\dawid\Desktop\DE_DATA\Mazda_DE\VINs_DE\Mazda\xml'.format(ver);
  
  out_file=r'C:\Users\dawid\Desktop\DE_DATA\Mazda_DE\VINs_DE\Mazda\Output.xml'.format(ver);
  
  
  def getListOfFiles(dirName):
      # create a list of file and sub directories 
      # names in the given directory
    
              listOfFile = os.listdir(dirName)
              allFiles = list()
      # Iterate over all the entries
              for entry in listOfFile:
          # Create full path
                 
                  fullPath = os.path.join(dirName, entry)
          # If entry is a directory then get the list of files in this directory 
                  if os.path.isdir(fullPath):
                      allFiles = allFiles + getListOfFiles(fullPath)
                  else:
                      allFiles.append(fullPath)
                  if os.path.isdir(fullPath):
                      allFiles = allFiles + getListOfFiles(fullPath)
                  
              return allFiles
  
  listOfFileOut=getListOfFiles(dirName)
  
  #filenames = allFiles
  with open(out_file, 'w',encoding='ANSI') as outfile:
      for fname in listOfFileOut:
          with open(fname,encoding='ANSI') as infile:
              for line in infile:
                  outfile.write(line)
                  
  print(&quot;Done&quot;)
  </code></pre>
  <p>But this completely destroyed structure of the xml file and I cannot import it anymore.
  Could anyone suggest if it's possilble to use python to get rid of all those ids to be able to import the whole Database in access?</p>
  <p>Thank you in advance.<a href="https://i.stack.imgur.com/N90Yc.jpg" rel="nofollow noreferrer">enter image description here</a></p>