Importing XML File - Some Data Is Truncated

[JaysonWard]

I'm importing an XML file into an Access data base. One of the xml
tags that will be imported as a column of data typically has more than
255 characters.

When I import the XML into Access, it creates a table with Fields all
set to Text, which only allows 255 characters.

Is there a way to specify what you want the data type to be for each
thing when importing into Access.

The text data type works fine for all of the other columns, but I need
a memo data type for the wordy one.

Thanks for your help.

Jayson

 

[JaysonWard]

For those who have the same issue, I discovered the answer that fit my
needs so I thought I would reply to my own add.

The solution is to use some simple VB to append the xml file to a table
with already defined data types within your database.

The article is located at:

http://www.microsoft.com/technet/scriptcenter/resources/officetips/oct05/tips1020.mspx

Hope this helps.

Jayson

 

[chgeiselmann]

Hi Jayson,

Thank you for posting the question AND the answer. It helped me to solve a similar problem I had with importing XML to Access.

Here one more hint for people who start from where I started:

The code mentioned in the various tutorials is nothing to be used inside a VBA Macro in Access. Rather, we are speaking here about Windows scripting.

Basically, you produce a txt file with the file name extension ".vbs" instead of "txt", so say, MyFirstScript.vbs.

You can, then, trigger the execution of this file by entering in the system prompt screen ("Start --> Run...") :

cscript MyFirstScript.vbs

and it will run.
To this end, you must first browse to the folder where you have stored the script (i.e. the .vbs-file). E.g. I stored it in C:\Scripting\, and in order to start the script with the command "cscript" I must be in this folder, the entire line being

C:\Scripting> cscript MyFirstScript.vbs

There are other ways to start the script, but this is the one which I used, and with the right script I indeed could fill an Access database (mdb) with data from an xml file and avoid (!) field truncation of large fields.