Monday, November 20, 2006

XML import into Access - Workaround for truncation

I recently had to import some XML files into Access. It worked well, save for text truncation. Here's the process, the workaround is below:
Import or link data and objects - Access - Microsoft Office Online

1. Open a database, or switch to the Database window
2. On the File menu, point to Get External Data, and then click Import.
3. In the Import dialog box, in the Files Of Type box, select XML Documents (*.xml;*.xsd)...
6. To start importing the file, click OK . This completes the import procedure.

Note For most records that cause an error, Microsoft Access creates and adds a row to a table called Import Errors. To view the list of errors that were encountered, open the Import Errors table from the Database window.

7. To set options for importing, select one of the following under Import Options:
* To import just the structure of the table, click Structure Only.
* To import the structure of the table and the data, click Structure and Data.
* To append the data to an existing table, click Append Data To Existing Table(s).
The trick is to first import just the structure, then change the datatype from text to Memo where truncation is occurring, then use the append feature.

13 comments:

  1. Absolute genius!

    ReplyDelete
  2. Thanks for this. Saved me a whole load of work!

    ReplyDelete
  3. It's kind of cool that this 2006 post gets a comment every few years :-).

    ReplyDelete
  4. This worked like a charm!
    You're right JGF ... probably not many people are as bliss as we are lol, or vice-verse.

    ReplyDelete
  5. Just wanted to post my 2012 thanks for this great nugget genius!

    ReplyDelete
  6. After hours of searching, thank you for this solution!

    ReplyDelete
  7. and from 2013, thank you as well

    ReplyDelete
  8. This worked great - thank you!!

    ReplyDelete
  9. I wish that I had thought of it myself. Thanks for spreading the knowledge.

    ReplyDelete