Import XML to Access

[skorpionkz]

Hi guys,

I have 34 XML files provided by outsourcers.
Most of them can be imported to access without any problem, but few causing the problem.

I am importing XML file (Structure and Data) and getting an error "Not all of your data was successfully imported... etc.".
There are 7 tables imported.

The main table has 40889 rows and other tables have additional information.

For example if I have list of transactions with product codes then another table has all products with the Product description.
So I can create query to have 1 Table with information inline from all 7 tables.

My question is:
If there is a way to check what was lost or if there was lost anything at all and if so why this error pop up?

Thanks
Andrew
</SPAN>

 

[Namestaken]

A table should have been created with _Error on the end which contains all of the rows which did not get imported along with the field that caused the error and a general code to help you identify the problem (Type_Conversion, for example).

 

[skorpionkz]

Thank you. Basically if I don’t have any tables or columns which have _error on the end then I should not worry that I will loos some information even though I got the error massage. Is that correct?</SPAN>

 

[Namestaken]

I made a mistake, if you are importing to table "ExampleTable" then you should see a table called 'ExampleTable$'_ImportErrors or something very similar.

Not to get an error table but still get a message is very strange. Are you able to count the rows in your original XML file and then count the rows actually put into the table? If there is no error table and the rows match, I would assume you are fine.

 

[skorpionkz]

I cant actually check what is in XML file before I import it. I tried to import it to Excel, but this is mess. Excel actually merging all table to 1 huge table. The way Excel doing this is:
taking Table 1 and put it say A1-C3 then taking Table to and add it to D4-F5 and so on. Some data Excel just adding to each row. Another problem with excel is, if I have file larger that 30MB it would import it forever (I gave up after 3 hours).

So answering the question: No I can't check what data suppose to be in XML file and what is imported to Access. And there is no Tables imported with tag ImportErrors.

Thank you for your help.</SPAN>

 

[xenou]

As far as I know XML imports either succeed or don't succeed. Well formed XML doesn't fail, invalid XML doesn't import. But if there is not an XML schema then you may have issues with data types (numbers and text mixed). I'm not sure you get the Import Errors table here at all - maybe in the later case you would. Or the import may just fail. (personally I've never seen this happen but all my XML are good for me).

Make a backup of your database. Then if there are errors check if anything is imported at all (row count before, row count after). Also consider importing to a brand new table to check if the import succeeds that way.

 

[skorpionkz]

Hi xenou,

I am importing to the brand new table. I getting window where I can see what tables will be imported. When I press ok it takes a while and the erro pop up (as per first post). All tables which were imported contains data.

thanks,
Andrew

 

[xenou]

Okay. Your XML is bad. No can import! You'll have to inspect the XML for the problem (but you will probably find this hard to do if you don't understand XML). Could be anything - data type issues, invalid xml, invalid xml characters, faulty encoding.

 

[xenou]

Probably what I would do first is load the xml in an XML editor and check it for well-formedness. A pretty good XML editor that is free is XML Copy Editor

XML Copy Editor

Notepad++ also has an XML plugin that I believe should work well too (I used to use it but now I use XML copy editor instead for all my XML work):
http://www.notepad-plus-plus.org/