We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

asp.net Import Data Techniques - Best Practice

Steve Krile
Steve Krile asked
on
Medium Priority
787 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.
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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.

Author

Commented:
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

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.