Solved

asp.net Import Data Techniques - Best Practice

Posted on 2009-07-01
4
731 Views
Last Modified: 2012-05-07
I'm on the precipice of a pretty important design consideration and thought I'd ask some advice.  I am designing a web application that will contain a table that holds "people".  The users of my site will want their site to be filled with their people.  The hard way to do this is to give them an interface on my site that allows them to view, add, edit, delete - all things I will likely do.

But, the "easy" way (at least from a customer's perspective) is to give them an importing tool.  I have seen many ways of doing this over the years, and could likely figure each one out.  But here are my assumptions about my design:
  1.  Not all people have Excel, making a software-dependent upload tool a bad idea
  2.  Comma-separation is pretty good, but not if the data has commas in it and the user doesn't properly plan for this
  3.  XML is nice and structured, but is there an easy way for non-tech types to get data into my schema?


What I would like is this approach:  A user wants a list of 10,000 people in their company to be available on my web site.  They go to their HR and get that list in *SOME FORMAT*.  They bring that file to my website and upload it.  My program inspects the file and reports back anything bad that might happen before committing the changes.  The user accepts/rejects, and the data is synchronized.

Where I'm really getting hung up is the SOME FORMAT part.  I realize that a web interface may not be the only way to get this done (i.e. the user could stick it on an FTP site and I could consume from there), but let's assume I want that method.  Can someone point me in the right direction.  I'd really like some simple examples of schema or approaches that have worked well for others.
0
Comment
Question by:Steve Krile
  • 3
4 Comments
 
LVL 4

Accepted Solution

by:
j_s_kelley earned 500 total points
ID: 24754847
In order to get data in "SOME" format you are going to need "SOME" software.  If Office products are out, then you know most people will have a notepad editor.  if that is the case, then a delimited or fixed width file is going to be pretty much your only option.  I wouldn't have non-technical types try to create XML, especially in Notepad.  Your choice of delimiting character is up to you.  I can be a comma, tab, or even something like the vertical bar ( | ).  If you use comma delimiting, you can wrap text in quotes so that the commas inside the quotes won't matter.  Also, with a delimited format, someone who is using EXCEL can generate that format easily.  The problem will be mistakes.  If data is typed in by hand, the format may be off so you will really need some good validation on the other end.  With your parameters, your choices are limited.  As far as the mechanism, you could just use an Upload control to get the file to the web server.  From there, the server code can parse it, put it in temp storage and display for syncronization.  Sorry I couldn't be more help.
0
 
LVL 16

Author Comment

by:Steve Krile
ID: 24754898
You are right...I will need some type of software on the client side.  I guess that's the bit that's scaring me.  And, no, I was not expecting non-technical types to edit XML by hand.
0
 
LVL 16

Author Comment

by:Steve Krile
ID: 24804649
So, after much search and kvetching, I've decided to go with a .csv approach.  This makes j s kelly correct.

Here is the code I'm using.

The key bits for me were:
  1.  FileIO.TextFieldParser - didn't know this exists and it works with a normal IIS/Server setup....read, you don't need to have Excel installed for this to work
  2.  using System.IO.Stream to read the posted file directly rather than saving the file to some folder somewhere then opening it and reading.
        Try

            Dim ds As DataSet = New DataSet()

            Dim dt As DataTable = ds.Tables.Add("TheData")
 

            Dim myStream As System.IO.Stream

            myStream = FileUpload1.FileContent
 
 

            Dim afile As FileIO.TextFieldParser = New FileIO.TextFieldParser(myStream)

            Dim CurrentRecord As String() ' this array will hold each line of data

            afile.TextFieldType = FileIO.FieldType.Delimited

            afile.Delimiters = New String() {","}

            afile.HasFieldsEnclosedInQuotes = True

            Dim i As Integer = 0

            ' parse the actual file

            Do While Not afile.EndOfData

                Try

                    CurrentRecord = afile.ReadFields

                    If i = 0 Then

                        For x As Integer = 0 To CurrentRecord.GetUpperBound(0)

                            dt.Columns.Add(CurrentRecord(x).Trim())

                        Next

                    Else

                        Dim dr As DataRow = dt.NewRow()

                        For x As Integer = 0 To CurrentRecord.GetUpperBound(0)

                            dr(x) = CurrentRecord(x).Trim()

                        Next x
 

                        dt.Rows.Add(dr)

                    End If
 

                Catch ex As FileIO.MalformedLineException

                    Stop

                End Try

                i = i + 1

            Loop
 

            grvResults.DataSource = ds

            grvResults.DataBind()
 

        Catch ex As Exception

            ErrMsg = ex.Message

        End Try

Open in new window

0
 
LVL 16

Author Closing Comment

by:Steve Krile
ID: 31598786
I had to a lot of digging to find element of the code to make the approach you suggest work.  That's OK, and not an insult...just the facts.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Read about why website design really matters in today's demanding market.
I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now