We're a paid CMSImport customer and we're having trouble importing Excel data from a saved import definition. Specifically, the exact error we recieve says:
Your uploaded file is invalid, the following errors occured:
The uploaded file is invalid make sure it's of the right type
The original file we used to create the definition was a .xlsx. This new (updated) file is also a .xlsx. I don't understand why I'm seeing this error...any help would be greatly appreciated.
Could it be that the Excel has some locked columns. What could help is create a new Excel file copy paste data over. But I'm interested in the Excel sheet, if you can share it that would be very nice. Then I can contact the Component vendor of the Excel component also (Didn't write this component myself) to sort this issue.
At first I could import the Excel file without any issues. Then I deleted the file DTG.Spreadsheet.dll from the bin folder and got the same error as you had. Restored it and it all worked fine. Can you please check if the DTG.Spreadsheet.dll is in your bin folder? I will update the error in case the dll is missing for the next version (2.3) of CMSImport.
Sorry for the delay. Can you try if you can use the file in the normal import wizard? The only thing about the Excel file I noticed that it wasn't opening on my Ipad. What I will do is send the file back removing all Markup so we can see if that was causing the issue.
Can you check if the worksheet name of the original was also "header" Just got an error in a similar case where they renamed the worksheet. Then CMSImport can't find the worksheet anymore and an error occurs.
A few notes: I checked the original file and the tab was indeed called "Header". So the name hasn't changed between the original Excel and the updated Excel.
I was able to create a new definition and load the new Excel file just fine. So I then tried to re-run the definition with the ORIGINAL file and it's not working anymore! Is there some way to export the definition file so you can take a look at it? I have a strong feeling that there's not a problem with the file but with either the saved definition or with something inside CMSImport.
The definition was originally created on Umbraco 4.9 but we've since upgraded to 4.11.1. Do you think it's possible that something has changed internally in Umbraco between 4.9 and 4.11.1 that could be causing this saved definition to not work anymore?
Again, much thanks for your support. I'm sure we'll get this figured out!
That is weird, so the new definition works only when you use the new file? I can change the file every time. But if you can export the cmsimportstate table I can load it into my dev environment but this is really strange, I must admit I never update environments so never tested that scenario and therefore it's not impossible something happened during the upgrade.
Old definition - NEITHER file works anymore (the original or the update)
New definition - BOTH files load just fine
I'm okay with creating a new definition, we're just worried that the next time we get another update from our client we'll run into the same problem, and have to keep re-creating the definition over and over again. As you can see this Excel file has several HUNDRED columns and it takes a long time to map them all (the Umbraco Document Type we're mapping to doesn't have properties with the same names as the Excel columns).
I'll send over the cmsimportstate table. If you don't mind taking a look at it when you get some time that would be great.
Hey, just a quick update: We've been looking at the data in the cmsimportstate table. The ImportState column where all the serialized data is stored is an NVARCHAR(MAX). We think that our import is SO big that it's exceeding the column size and is truncating the mapping data. We noticed that when we Base64 decoded the data in that column that it looks like it's chopping off the end of the string.
That would also explain why we're able to load our file from a NEW definition (since it's running from memory), but unable to import any file from our saved definition (its getting corrupted when we save it).
Would it be possible to change the type of the ImportState column to TEXT? Some SQL functionality like pivoting won't work on a TEXT field, but it would be large enough to store an import regardless of the number of columns.
I'll do an export of the table for you to take a look at yourself. But I think this might get us on the right track to resolving our problem!
Yes you can change it to Text, it was Varchar(Max). But I've just tested to remove a few chars that and gives met a 'Invalid length for a Base-64 char array or string.' error. Would love to have the state data, then I can load that into my development environment since I never had this issue before and it's really weird.
Error importing an Excel file from a saved Import Definition
Hi,
We're a paid CMSImport customer and we're having trouble importing Excel data from a saved import definition. Specifically, the exact error we recieve says:
Your uploaded file is invalid, the following errors occured:
The original file we used to create the definition was a .xlsx. This new (updated) file is also a .xlsx. I don't understand why I'm seeing this error...any help would be greatly appreciated.
Hi Mike,
Could it be that the Excel has some locked columns. What could help is create a new Excel file copy paste data over. But I'm interested in the Excel sheet, if you can share it that would be very nice. Then I can contact the Component vendor of the Excel component also (Didn't write this component myself) to sort this issue.
Hope to hear from you.
Thanks,
Richard
E-mail sent (with Excel file attached). Thanks for your help!
Hi,
At first I could import the Excel file without any issues. Then I deleted the file DTG.Spreadsheet.dll from the bin folder and got the same error as you had. Restored it and it all worked fine. Can you please check if the DTG.Spreadsheet.dll is in your bin folder? I will update the error in case the dll is missing for the next version (2.3) of CMSImport.
Cheers,
Richard
Ok, I have confirmed that DTG.Spreadsheet.dll *is* in our /bin folder. The version is 2.7.0.0
Do you have any other recommendations? Did you notice anything weird in the Excel file?
Hi Mike,
Sorry for the delay. Can you try if you can use the file in the normal import wizard? The only thing about the Excel file I noticed that it wasn't opening on my Ipad. What I will do is send the file back removing all Markup so we can see if that was causing the issue.
Thanks,
Richard
HI Mike,
Can you check if the worksheet name of the original was also "header" Just got an error in a similar case where they renamed the worksheet. Then CMSImport can't find the worksheet anymore and an error occurs.
Thanks,
Richard
Hey Richard,
A few notes: I checked the original file and the tab was indeed called "Header". So the name hasn't changed between the original Excel and the updated Excel.
I was able to create a new definition and load the new Excel file just fine. So I then tried to re-run the definition with the ORIGINAL file and it's not working anymore! Is there some way to export the definition file so you can take a look at it? I have a strong feeling that there's not a problem with the file but with either the saved definition or with something inside CMSImport.
The definition was originally created on Umbraco 4.9 but we've since upgraded to 4.11.1. Do you think it's possible that something has changed internally in Umbraco between 4.9 and 4.11.1 that could be causing this saved definition to not work anymore?
Again, much thanks for your support. I'm sure we'll get this figured out!
-Mike
Hi Mike,
That is weird, so the new definition works only when you use the new file? I can change the file every time. But if you can export the cmsimportstate table I can load it into my dev environment but this is really strange, I must admit I never update environments so never tested that scenario and therefore it's not impossible something happened during the upgrade.
Hope to hear from you.
Thanks,
Richard
Oh, sorry Richard, what I meant to say is:
I'm okay with creating a new definition, we're just worried that the next time we get another update from our client we'll run into the same problem, and have to keep re-creating the definition over and over again. As you can see this Excel file has several HUNDRED columns and it takes a long time to map them all (the Umbraco Document Type we're mapping to doesn't have properties with the same names as the Excel columns).
I'll send over the cmsimportstate table. If you don't mind taking a look at it when you get some time that would be great.
-Mike
Hey, just a quick update: We've been looking at the data in the cmsimportstate table. The ImportState column where all the serialized data is stored is an NVARCHAR(MAX). We think that our import is SO big that it's exceeding the column size and is truncating the mapping data. We noticed that when we Base64 decoded the data in that column that it looks like it's chopping off the end of the string.
That would also explain why we're able to load our file from a NEW definition (since it's running from memory), but unable to import any file from our saved definition (its getting corrupted when we save it).
Would it be possible to change the type of the ImportState column to TEXT? Some SQL functionality like pivoting won't work on a TEXT field, but it would be large enough to store an import regardless of the number of columns.
I'll do an export of the table for you to take a look at yourself. But I think this might get us on the right track to resolving our problem!
HI Mike,
Yes you can change it to Text, it was Varchar(Max). But I've just tested to remove a few chars that and gives met a 'Invalid length for a Base-64 char array or string.' error. Would love to have the state data, then I can load that into my development environment since I never had this issue before and it's really weird.
Thanks,
Richard
is working on a reply...