Invalid AC XML File.

[jdpatterson74]

Hello all,

I exported the complete original template store database using the Data Utility program and copied my new data into the appropriate columns.

I saved the file as a xml spreadsheet 2003.

When I try to import using the data utility program, I receive this error:

Invalid AC XML file. There is an error in XML document (3,2)

Can someone point me in the right direction? Thanks. Joshua

[jmestep]

Did you try to open it in a browser or something to see if there were any validation errors? Can you post the first hundred or or so lines so I can check it against a valid file?

[jdpatterson74]

Thank you! Here is a copy of the first 75 lines, any more and the file will exceed the limit of 2MB.

Joshua

[sohaib]

Hello all,

I exported the complete original template store database using the Data Utility program and copied my new data into the appropriate columns.

Seems like you made some changes so that the XML file is no more AC7 compatible.

I saved the file as a xml spreadsheet 2003.

I am not sure what this means.

When I try to import using the data utility program, I receive this error:

Invalid AC XML file. There is an error in XML document (3,2)

Do you have the detailed exception in the log?

[jmestep]

Your file is nothing like the format it should be because of what Excel has done to it. I've uploaded a sample of what Able is looking for- you can open it in notepad. If you are going to use Excel to edit it and are concerned with products only- no options, kits, etc., then do the .csv download, edit that and use that for your upload.

[Naveed]

I can recommend you a good free xml editing tool, that you can use to edit your AC7 xml with a lot much ease. The tool shows the Selected XML nodes in tabular format like Excel spreadsheet, so, when you will select Store->Products node then all the products will be shown as a product table. And you can edit those easily. You can even create new rows (i.e new product nodes etc. easily).

You can download this XMLMarker tool from:
http://xmlmarker.com/
or
http://symbolclick.com/download.htm

[jdpatterson74]

I am using Excel 2007, would that make a difference?

@Developer - I am not sure what this means.

This is the only way I can save the excel sheet.

If I export the sample data database, or even the SampeDownload file given by jmestep, excel will ask to open this file as a:

• 1. Xml Table
  2. Read only workbook
  3. XML source task Pane

I would select as a XML Table. Excel would warn me that:
The specified XML source does not refer to a schema and it will create one. Ok.

Without making any changes, I tried to save it as a "XML DATA" file, Excel would warn this:
Cannont save or export XML data. The XML maps in this workbook are not exportable.

@ Developer - Do you have the detailed exception in the log?

The error appears immediately after selecting the file in the complete store import page. I am guess because I forced to save it as a xml spreadsheet 2003.

@jmestep - If you are going to use Excel to edit it and are concerned with products only- no options, kits, etc.,

I am finished with 10 data Tables to support 4,000 products.

• 1. Products
  2. ProductVariants
  3. ProductOptions
  4. OptionChoice
  5. Options
  6. Categories
  7. CatalogNodes
  8. CatalogParents
  9. UpsellProducts
  10. Manufacturers

So I am guessing that I need to make it XML to import the complete store function.

@Naveed - I can recommend you a good free xml editing tool,

Thank you, I will look into that. Can I use the data I already have in my excel sheets.



Thanks for your response. This is the only thing holding me back from getting my store running.

[jmestep]

I've worked with the xml in Excel 2007 and you will have the same problem. You really have to do a lot of work in Excel if you are trying to work with relational data. Here is one post I found concerning it and after I sent that to a merchant he decided not to try it.
http://msdn.microsoft.com/en-us/library ... rios_intro

[jdpatterson74]

Ok....let me ask this....

This is really starting to get confusing. So besides adding the data into excel, I will need to write xml maps or xml codes when using the data utility program?

I used the sample template database and added data accordingly to the layout. I was told by ablecommerce that I do not have to any type of coding. Just use the complete store import and export.

What do I need to do to make the data I have entered in Excel to the proper XML format required to use the Data Utility Program? See attached file (Sample Master Table.xml), if needed. Thanks!!

[jmestep]

Well, let's wait for an answer from Mike on how to do it. So far there has been no easy way unless you are going straight product data with no options, etc or you are using an export from an existing Able 5.5 site.
I've looked at it off and on because of so many people wanting to do it and the link I post above was the clearest explanation I had been able to find. I just don't have the time to pursue experimenting with it. You have to get your import file into the format that Able is looking for and it's just not going to happen trying to get Excel to do it automatically.
I don't think you can do it with separate xml files based on the tables you are talking about because the dataport is looking for things in a certain hierarchy, like product with category info inside the product tags and options the same way. I think Naveed has given the only way you can do it without a lot of coding/manipulating even within Excel to get the export to work. Try taking the entire export file you made and opening it up in the xml editor Naveed suggested. That might not even be a lot of benefit if you have a lot of data to input- it will still be one product at a time.
I know it's frustrating, but the problem is that you are dealing with relational data going into separate tables and Excel is not set up that way. It might be possible in MS Access if you set up your tables the same way Able has them set up and then try to export to xml using a schema that has been generated from the xsd for the Able import format.

[jdpatterson74]

Did you save the sampledownload as a xml file by using excel?

I did not change anything to your file and I can not saved it as a XML data file. Maybe I am not saving the file correctly?

I can see the XML map source in your sampledownload file with excel, and I can also view the entire store XML map source (in a hierarchy format) when I look at the Sample Master Table file I attached. See attached picture for clarification

I also attached what I see with that xml editor.

[Naveed]

Using the XML Marker tool the "Tree Selection Browser" and "Table Selection Browser" panels can let you edit the XML easily.

[jdpatterson74]

Ok. I have 10 tables in excel to support 4000 products, did I just waste 4 months of entering the data into excel? Because now, I need to copy and paste all the data again into this program. If this is the case, can I copy more than one cell, or have to do it one by one? Where would be a good starting template for the entire store?

Again, I was told I do not need to code anything when I use the entire store import/export.

Is there a better way?

[jmestep]

Let's back up. How did you get your data into excel? Did you import the sample Able export from 7 and add data? Is there one excel file or more than one? Can you upload a part of it here when you have saved it in Excel format?
The xml file you have uploaded has all the Excel appearance formatting code in it.
Are you familiar with connecting a schema in Excel to the data? Not one that Excel has created, but one that is created from the Able xml file. If so, I posted the schema here. I just haven't had time to try to get it to work with a spreadsheet yet.
viewtopic.php?f=42&t=7412&p=31133&hilit=schema#p31133

[jdpatterson74]

Did you import the sample Able export from 7 and add data?

I started with the sample data loaded when you first initialize the database. Then using excel, I would open an original table, (opened as a XML Table and does not come with a schema), learn how it was structured and edit the data accordingly. See Entering Data Picture.

I did not perform any type of coding, maping, schema, or anything else other than entering the data into each cell. What you see in the picture is the type of data I entered.

Is there one excel file or more than one?

I have "KEY" excel sheets for each of the 10 tables (products, categories, options, upsell, etc..), which allow me to enter and orgainize the data in a logical way.

Next, I would copy all the data from my "KEY" sheets into the entire store excel sheet, as shown in the Sample Master Table file.

Can you upload a part of it here when you have saved it in Excel format?

Attached is my categories excel sheet. This is an example of what I did for all my "KEY" Tables.

Are you familiar with connecting a schema in Excel to the data?

Not yet, going off the idea that once I had the data entered into my excel (cells, no coding, mapping, or anything else), then I could use the Data Port Utility to work the magic.

[jmestep]

Do you want to try this in steps and get it in any way you can or are you wanting to do it in only one xml import?
First of all, your Excel file needs to be stripped of all the formatting. I opened it in Excel, then saved as .csv. I then uploaded it to a test site and I think everything went it fine. Try that for the categories only.

Categories.gif

[jdpatterson74]

I will do whatever gets me there.

I am able to import only the products and categories because of the .csv data port function. I have tried that and it works.

My problem is using the .csv function, I can only do those two tables. I have ten tables so using the xml (upload entire store function), is the best way to go, so I have been told. Every product has an option or variant, which is forcing me to use that function.

My tables are:
1. Products
2. ProductVariants
3. ProductOptions
4. OptionChoice
5. Options
6. Categories
7. CatalogNodes
8. CatalogParents
9. UpsellProducts
10. Manufacturers

[jmestep]

Your categories should get in there OK with the .csv.
Manufacturers would pick up OK from the product import from what Naveed says.
I was just trying to eliminate categories so you could concentrate on products only.
Can you post sample data for the other tables relating to products?

[jdpatterson74]

Here is a sample up to Alfred Sung for the Option Table. Products will be in next post.

[jdpatterson74]

Sample Product Tables...listed below.

All data for all tables goes up to Alfred Sung Products.

Thanks for looking at this.

[jmestep]

I'll look at it tomorrow morning, but it might be Friday before I can do anything serious with it.

[jdpatterson74]

Thank you!

[jmestep]

I've spent a couple of hours working on this and I don't think you can get there from here using Excel standard commands. I even took the samples from the msdn article posted above and they imported OK into Excel, but half of them wouldn't export-- I made no changes after I imported, just tried to re-export. It is because of the relational data structure going past two pieces of data. I'm going to work more on it from the approach of using Visual Studio (you can do it there) or VB code within Excel.

[jdpatterson74]

Is this normal? Should I have entered the data in a different way?

Thank you for looking into this and for your time.

[jmestep]

Yes, it's normal. The only thing your files have in them that should be there is all the appearance formatting that Excel put in. You really did a great job of figuring out the data structure and putting it all in. The problem is that Excel is designed to work with flat data and not relational data as in a database. I'm wanting to come up with a means where someone can generate Excel files like you did with those specs and then the utility can parse those all out into xml. I know it can be done with a web page made in Visual Studio, but it looks like it might be able to be done with VB code in Excel. I'm just a lot more familiar with Access than Excel, but most people that have Excel don't have Access.