Sunday, October 17, 2010

Bulk Import CSV, XLS, and XLSX

Code On Time applications now support bulk import of data files in CSV, XLS (Microsoft Excel prior to Office 2007) and XLSX (Microsoft Office 2007, 2010). The feature is easily activated from Actions menu in grid view.

Menu Actions with selected option Import From File is presented in the screenshot.

image

The next screen shot shows the initial state of the import dialog.

image

If you select the link “Download import file template” then a CSV template file download prompt will show up. Here is the sample import template opened in Microsoft Excel 2010.

image

Save the template to your hard drive and enter several sample values. The following screenshot shows five sample products. Notice that lookup values Supplier Company Name and Category Name are entered as text.

image

Click Browse to selected and upload the data file.

The Import dialog will present the field map that allows mapping fields from the spreadsheet to the fields in the data controller. Data file fields are displayed as static labels. Data controller fields are rendered as selectors. The import processor will try to automatically match the data file and data controller fields and have the data controller field names pre-selected. The dialog will indicate the number of records detected in the import file.

image

Click import to start the process. The file will be processed asynchronously. The prompt is displayed if the import process has started successfully. If the number of records is relatively small then chances are that the new records will show up in the grid view immediately after you dismiss the alert. If the number of records is large then the new records will start showing up if you refresh the grid view or the page.

image

Here is the list of imported records that were shown in the example. The import processor automatically matches foreign key field values to the corresponding text samples in the uploaded files.

image

Import feature  uses components from  System.Data.OleDb namespace to interact with the uploaded data files.

Download and install Microsoft Data Access Database Engine 2010 Redistributable components on your development machine and your server to allow OleDb components to read the contents of the uploaded import files. Notice that if you are using a 64-bit operating system then the corresponding database engine must be installed.

Bulk import feature requires a Premium subscription.

Several enhancements are planned for this feature already. Users will be able to specify an email address that will receive a message with the import log upon completion of the import file processing. Developers will also be able to write code in Shared Business Rules class to capture import progress.

3 comments:

Bobsov said...

With standard edition purchase, will I (a developer) will be able to create my own import logic to get data out of excel and into database?

Customer Service said...

Web applications created with Code On Time are standard ASP.NET apps. You can create new actions and place then in action groups. That includes the action group "Actions".

Add command "Import" and set Command Name to "Navigate" and Command Argument to "~/Pages/MyImport.aspx". Implement import in any way you want.

Bobsov said...

Thank you. I am getting a license for myself now.

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.


© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at http://codeontime.com