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:

Anonymous said...

Absolute genius!

Anonymous said...

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

JGF said...

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

Anonymous said...

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

Anonymous said...

Big !

Anonymous said...

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

njs said...

After hours of searching, thank you for this solution!

njs said...

and from 2013, thank you as well

Clay Fulgham said...

PERFECT SOLUTION!!

John C King said...

Just what I needed

Kim Philpot said...

This worked great - thank you!!

Sara C Dustin said...

And from 2016, thank you!

Stephen Rathborne said...

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