Custom Table Data Import

by Anonymous
Custom Table Data Import preview

Price

$0

Details

Licence:
Free
Works with:
7.0, 8.0, 8.1, 8.2, 9.0, 10.0
Version:

Summary

Great tool to import data from Excel to a custom table.

Description

It is rare case that someone would like to populate custom table data manually, especially if there is a huge amount of it. This web part comes to solve this problem. This flexible feature allows user to import data from Excel document to a custom table available in your application. It automatically maps Excel columns to custom table fields.


User should select some Excel file, specify sheet name to read data from and select custom table to import data to. Web part maps fields automatically, so column names in Excel should match fields code names as well as columns data type should match.


Note:
Please add reference for WindowsBase assembly into your web.config file:
<add assembly="WindowsBase, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>

It is used to read data from Excel, so no MS Office should be instaled to the server where data import is going to occur.

.NET 4.0 or higher is required

Comments


Muru commented on

I have an interesting story to present here about this web part.
First of all, great idea and its working ...so, thank you.
Now, I had lot of compilation issues with this webpart.
Environment used: Windows Server 2016, Visual studio 2017
1. Imported the web part objects
2. Move the artifacts to CMSWebParts/[custom folder]
3. Encounterd multiple errors.
So, I copied the ascx and the code behind files to some other folder outside of the Kentico folder
created a web part (Kentico gave a long class name -- keep as it is)
copied the ascx code and the code behind (just the logic) seperately into the web part class (DO NOT change the class name)
compiled
Now its working as expected.

Daniela Kubikova commented on

Hi Andre,
have you ticked the "Import code files" check box when importing the web part? (See also my comment from Mar 27, 2015).

Andre commented on

I imported the zip file and when viewing a page that has the web part on the admin area, I get this message:

Error loading the Webpart CustomTableDataImport of type CustomTableDataImport

I get this in the event log:

Message: The file '/CMSWebParts/MyWebParts/CustomTableDataImport.ascx' does not exist.

Exception type: System.Web.HttpException
Stack Trace:
at System.Web.UI.Util.CheckVirtualFileExists(VirtualPath virtualPath)
at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate)
at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate)
at System.Web.UI.TemplateControl.LoadControl(VirtualPath virtualPath)
at CMS.ExtendedControls.ControlDefinition.Load(Page page)
at CMS.PortalControls.CMSWebPartZone.LoadWebPart(Control container, CMSWebPartZone zone, WebPartInstance part, Boolean reloadData, Boolean isVariant)

Does anyone know why?

Gallen commented on

Imported my Excel files for the custom tables with no issues at all. Nice utility!

Daniela Kubikova commented on

Hi Paul and Sanket,

I tried to take a look at the issue you have mentioned and I think I have found a solution. The problem is in the CustomTableDataImport.ascx.cs file on the line 246: attributes.Add(col.Attributes["r"].Value.Substring(0, 1));

For columns with longer names (AA, AB, AC, ...) there should be Substring(0,2)

So you can easily fix this e.g. by adding a columns counter and simple if-else condition in the code. Something like this:

var i = 0;
foreach (XmlNode col in cellNodes)
{
dt.Columns.Add(sharedNodes[Convert.ToInt32(col.InnerText)].InnerText.Trim());
if (i < 26)
{
attributes.Add(col.Attributes["r"].Value.Substring(0, 1));
}
else
{
attributes.Add(col.Attributes["r"].Value.Substring(0, 2));
}
i++;
}

If you find some nicer solution, feel free to share it with others here.

Paul Burns commented on

Hi,

I am also getting the same issue as Sanket Shah. Everything works up A - Z for not afterwards!

Sanket Shah commented on

Hi,

I am trying to export the excel sheet having 100+ columns. It is inserting the first "A-Z" columns properly but "AA" column onwards are not properly inserted.
"AA-AZ" are repeating the value from the A column, "BA-BZ" are repeating the value from B column and so on.
After Debugging, I found that while reading the excel sheet, the "attributes.Add" in "ReadExceldata()"function repeats the values from 26th index onward.

How do I change the attributes at index[26] and make it AA,AB,AC and so onward?

DanielaK commented on

jnarkar-imediainc: I tested the web part on Kentico 7 with .NET 4.0 and it worked properly. I assume that you just need to add the assembly mentioned in the description above into your web.config file.

dani: Just import the web part into Kentico using the Import wizard. For more info, see the Kentico documentation https://docs.kentico.com/display/K82/Importing+a+site+or+objects

ashish: I would be glad to help you, however, I wasn't able to reproduce the error.

dani commented on

Any instruction how to add this to our project? plz

jnarkar-imediainc commented on

I am working with Kentico 7.0 and System.IO doesn't have type or namespace for packaging. I would need alternate for .net 3.0 for kentico 7.

ashish commented on

server not found error in custom table edit option when data option is clicked

Nayeem commented on

hi suppose i have sample .csv file and i want to import into it.please help me out.

Daniela Kubikova commented on

Hi chetan2309, try to import the web part again and in the Step 2 of the import process tick the "Import files (recommended)" and "Import code files" checkboxes. It could do the trick.

chetan2309-gmail commented on

I am getting this error in event log after adding this web part on my page. The webpart was added successfully

I took 8.2 build. kentico I have is 8.2.2 on Azure.

Message: The file '/CMSWebParts/MyWebParts/CustomTableDataImport.ascx' does not exist.

Daniela Kubikova commented on

Marco: Maybe I don't understand properly when exactly you are getting the error. I tested it on both Kentico versions (7.0 and 8) and it worked well. Please, submit the question to our support by filling this form: http://devnet.kentico.com/support
with a note that this ticket can be assigned to me. We can discuss it in more details.

Marco commented on

Hi Daniela,
I am still trying this, sometimes it passes through, sometimes not! I have set existing folders and tested many times, always getting this strange error most of the times. Is this a quick fix for Kent-X?
Thanks,
Marco

Daniela Kubikova commented on

Hi Marco,
you are right, now I got this error in 8.0 version too. It seems that there is some problem with saving macro in the "File Storage Path" field in the web part properties. Remove the macro by clicking the small cross mark next to the field and enter a basic path like "~\foldername" there.

Daniela Kubikova commented on

Hi Mario,
there is no documentation for this web part but it is very simple to use, just read the description here.

MARCO commented on

I am getting this error when setting the Web Part

The entered values cannot be saved. Please see the fields below for details.

File Storage Path: Please enter a value.

Can I get any help?

Mario commented on

Where is the documentation for this ?

Daniela Kubikova commented on

Just your custom fields. Kentico fields like 'Created date' or 'GUID' should be filled automatically just as when you create new item in UI.

Mark commented on

Thanks Daniela. I will look at the property.

Do I need to include standard Kentico fields in the spreadsheet too? Or just my custom field code names?

Daniela Kubikova commented on

Hi Mark, entering virtual path like "~\CMSImportFiles" in the web part properties should solve your problem.

Mark commented on

How do you set where the file is uploaded? I'm getting an error that says "E:/sitesroot/0/CMSImportFiles/<filename>.xlxs is a physical path, but a virtual path was expected."

Do I need to include standard Kentico fields in the spreadsheet too? Or just my custom field code names?

webdeveloper-avastonetech commented on

You all rock, thanks for this!

Leave message
Your rating: