Setting up a function or user control to allow the user to import csv or excel files RRS feed

  • Question

  • Hi guys.

    Before I start, I'm very new to asp .net development and I'm having to learn as I go. I've got the task of creating a quite large reporting and assessment tracking application for the school I work for.

    I've looked on asp.net site, through my books (all 3 of them) and on here and other forums for the solution to my problem. I want to be able to learn how to do this, not just download someone else's example. Smile

    I'm going to use an sql server database for the main data in my app, but I need to do automated daily imports of data that comes from another database. I cant connect to the other database, its restricted. There's an exe I've used that can export reports at schelduled times from the other database, this works great and produces csv files. I've also used sql server's dts import export function to schedule an import every nighjt that updates the data in my database with the data found in the daily exported csv. This works fine I think, so all is good here.

    What I want to do is to create an import function so that I (or another 'admin' user) can import data whenever they want from a spreadsheet or csv and this can update a specified table with the new data. I would ideally like the user to be able to specify where the source csv is, and match it's columns to columns in my sql database, and also provide exception handling, in case the csv isn't right for whatever reson, or the data doesnt match up.

    Any ideas on how I go about learning how to do this would be great. Many thanks in advance

    Tuesday, June 19, 2007 8:12 AM

All replies

  • no ideas? (bump)
    Tuesday, June 19, 2007 3:16 PM
  • You have to break the problem down.  There are (it seems to me) four different parts of the problem:
    1. Reading the CSV data into some temporary data structure
    2. GIving the user a way of mapping the elements in the temporary structure to table columns
    3. Validating the data against the type information and constraints in the table
    4. Inserting the data into your database.
    The first problem is one of parsing, and it's probably already been solved somewhere if you poke around for it.  (In fact, typing "csv parse regular expression" into Google gets us http://www.codeguru.com/cpp/cpp/string/net/article.php/c8153/, which is exactly what you're looking for.)

    The second problem's one of UI design.  Ideally you'd display the parsed CSV data in, say, a DataGridView, and then give the user some way of assigning destination columns to each column in the DataGridView.  (Also, since you probably want to use a DataGridView to display the data, that suggests that the temporary data structure that you want to read the CSV into is a DataTable, one where all of the columns have a data type of System.String and an arbitrary length.) 

    A trivial way of doing the mapping in a desktop app would be to create a ContextMenuStrip listing all of the as-yet-unassigned destination columns and hook it up to the column headers of the DataGridView, so that to map a column the user would right-click on it and pick the destination column.  Then you'd update the column header in the DataGridView and remove the mapped column's name from the ContextMenuStrip's items.  I'm not up on all of the UI features in ASP.NET, but I'd be surprised if there isn't something analogous in it.

    The third problem's straightforward once you've done your mapping:  you iterate through the rows in your temp table, and for each, verify that the data in each column doesn't violate the type and length requirements of column's destination.  (In fact, if the amount of data you're dealing with is small enough, you can do this check on a column-by-column basis as the user maps them.)

    The fourth problem's straightforward too:  you iterate through your temp DataTable again, adding rows to your real DataTable, and then use the real DataTable's adapter to update the database.

    This all assumes that the CSV doesn't come to you with half a million rows of data in it.  If you're processing large amounts of data, you probably have to keep the temp table in your database instead of just using an in-memory DataTable.
    Tuesday, June 19, 2007 6:42 PM
  • thanks for your reply!

    this is a great start, just what I need I think. See my problem is in the first place I wasn't aware that I needed to parse! I'll have a see what I can do and post back soon. Thanks.
    Wednesday, June 20, 2007 7:51 AM