reading an excel file in c#

Hi,
i m Jay working for USDA in Kansas city, KS. i m working on project where i need to upload an excel file which is actually a CSV file with column headers. on upload button click i need to read each row and depending on some perticular value in each row i have to insert that row into 3 different tables(depending on the value) in database.
so i need to check the columns first to see if any of the columns are missing, then i need to read each row and while reading each row i need to check if cofips is 999 and district is 99 then i have to insert that row in one table, if cofips is 999 and district is not 99 then in to another table and if cofips is not 999 and district is not 99 then into a different table ( u can see in below data).
i m using ASP.net as front end and C# as back end and Microsoft enterprise library to connect to Database.
please anyone help me with the sample code ...i have been struck with this for the past 2 days in my work.
below is the sample CSV file which is actually in Excel file. please replay me back to my mail id: [email protected]

 crop type year state cnty stfips CRD cofips commcode prac plnt plntharv harv [lnyld haryld yldunit prod produnit
107 997 1993 WA all 53 99 999 0 997 20000 500 LBS 10000000 LBS
107 997 1994 WA all 53 99 999 0 997 17000 559 LBS 9500000 LBS
107 997 1995 WA all 53 99 999 0 997 15000 580 LBS 8700000 LBS
107 997 1996 WA all 53 99 999 0 997 13000 685 LBS 8900000 LBS
107 997 1997 WA all 53 99 999 0 997 13000 769 LBS 10000000 LBS
107 997 1998 WA all 53 99 999 0 997 18000 778 LBS 14000000 LBS
107 997 1999 WA all 53 99 999 0 997 19000 716 LBS 13600000 LBS
107 997 2000 WA all 53 99 999 0 997 17000 794 LBS 13500000 LBS
107 997 2001 WA all 53 99 999 0 997 14500 793 LBS 11500000 LBS
107 997 2002 WA all 53 99 999 0 997 12000 858 LBS 10300000 LBS
107 997 2003 WA all 53 99 999 0 997 12500 816 LBS 10200000 LBS
107 997 1993 WA all 53 21 999 0 997 1500 533 LBS 800000 LBS
107 997 1994 WA all 53 21 999 0 997 1500 567 LBS 850000 LBS
107 997 1995 WA all 53 21 999 0 997 1500 560 LBS 840000 LBS
107 997 1996 WA all 53 21 999 0 997 1000 750 LBS 750000 LBS
107 997 1997 WA all 53 21 999 0 997 900 767 LBS 690000 LBS
107 997 1998 WA all 53 21 999 0 997 1000 610 LBS 610000 LBS
107 997 1999 WA all 53 21 999 0 997 1000 630 LBS 630000 LBS
107 997 2000 WA all 53 21 999 0 997 1000 700 LBS 700000 LBS
107 997 2001 WA all 53 21 999 0 997 700 714 LBS 500000 LBS
107 997 2002 WA all 53 21 999 0 997 900 722 LBS 650000 LBS
107 997 2003 WA all 53 21 999 0 997 700 786 LBS 550000 LBS
107 997 1993 WA all 53 25 999 0 997 4000 625 LBS 2500000 LBS
107 997 1994 WA all 53 25 999 0 997 3500 714 LBS 2500000 LBS
107 997 1995 WA all 53 25 999 0 997 4300 674 LBS 2900000 LBS
107 997 1996 WA all 53 25 999 0 997 4500 733 LBS 3300000 LBS
107 997 1997 WA all 53 25 999 0 997 4700 651 LBS 3060000 LBS
107 997 1998 WA all 53 25 999 0 997 6000 638 LBS 3830000 LBS
107 997 1999 WA all 53 25 999 0 997 5500 655 LBS 3600000 LBS
107 997 2000 WA all 53 25 999 0 997 5000 800 LBS 4000000 LBS
107 997 2001 WA all 53 25 999 0 997 4500 800 LBS 3600000 LBS
107 997 2002 WA all 53 25 999 0 997 3400 838 LBS 2850000 LBS
107 997 2003 WA all 53 25 999 0 997 4500 778 LBS 3500000 LBS
107 997 1993 WA all 53 71 999 0 997 13000 438 LBS 5700000 LBS
107 997 1994 WA all 53 71 999 0 997 10500 490 LBS 5150000 LBS
107 997 1995 WA all 53 71 999 0 997 8000 525 LBS 4200000 LBS
107 997 1996 WA all 53 71 999 0 997 6300 619 LBS 3900000 LBS
107 997 1997 WA all 53 71 999 0 997 5800 828 LBS 4800000 LBS
107 997 1998 WA all 53 71 999 0 997 9200 922 LBS 8480000 LBS
107 997 1999 WA all 53 71 999 0 997 11000 765 LBS 8410000 LBS
107 997 2000 WA all 53 71 999 0 997 10000 810 LBS 8100000 LBS
107 997 2001 WA all 53 71 999 0 997 8300 819 LBS 6800000 LBS
107 997 2002 WA all 53 71 999 0 997 6400 922 LBS 5900000 LBS
107 997 2003 WA all 53 71 999 0 997 6000 892 LBS 5350000 LBS
107 997 1993 WA all 53 77 999 0 997 1000 700 LBS 700000 LBS
107 997 1994 WA all 53 77 999 0 997 1000 800 LBS 800000 LBS

to Read a file from Web Brower first u need to save that file on server
To Save that file on server u this
On Button click------------
strFileName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") + 1)
strFileType = File1.PostedFile.ContentType
intFileSize = File1.PostedFile.ContentLength
If intFileSize <= 0 Then
' Response.Write(" Uploading of file " + strFileName + " failed ")
lblError.Text = "Select the Input File First"
Else
lblError.Text = "File Processing ....."
File1.PostedFile.SaveAs(Server.MapPath("InputFile" + "\\" + strFileName))
End If
Call ProcessInputFile()

Sub ProcessInputFile()
Dim ArrForScanItem() As String
dim str1,Str2,str3 as string
count = 0
InputFilePath = New StreamReader(Server.MapPath("InputFile" + "\\" + strFileName))
lblError.Text = "File Processing ........."
While (1)
InputLine = InputFilePath.ReadLine
If InputLine = "" Then
Exit While
End If
ArrForScanItem = InputLine.Split(",")

Try
str1= ArrForScanItem(0)
str2 = ArrForScanItem(1)
str3 = ArrForScanItem(2)

Call InsertData() '''Here u write ur code to insert data wht u want to insert
Catch es As Exception
lblError.Text = strSearchCode + " : Error in Input File."
lblError.Text &= es.Message

End Try
End While
InputFilePath.Close()
End Sub

///////If Any Problem Mail me [email protected]

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rohan Varekar17y ago
to Read a file from Web Brower first u need to save that file on server
To Save that file on server u this
On Button click------------
strFileName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") + 1)
strFileType = File1.PostedFile.ContentType
intFileSize = File1.PostedFile.ContentLength
If intFileSize <= 0 Then
' Response.Write(" Uploading of file " + strFileName + " failed ")
lblError.Text = "Select the Input File First"
Else
lblError.Text = "File Processing ....."
File1.PostedFile.SaveAs(Server.MapPath("InputFile" + "\\" + strFileName))
End If
Call ProcessInputFile()

Sub ProcessInputFile()
Dim ArrForScanItem() As String
dim str1,Str2,str3 as string
count = 0
InputFilePath = New StreamReader(Server.MapPath("InputFile" + "\\" + strFileName))
lblError.Text = "File Processing ........."
While (1)
InputLine = InputFilePath.ReadLine
If InputLine = "" Then
Exit While
End If
ArrForScanItem = InputLine.Split(",")

Try
str1= ArrForScanItem(0)
str2 = ArrForScanItem(1)
str3 = ArrForScanItem(2)

Call InsertData() '''Here u write ur code to insert data wht u want to insert
Catch es As Exception
lblError.Text = strSearchCode + " : Error in Input File."
lblError.Text &= es.Message

End Try
End While
InputFilePath.Close()
End Sub

///////If Any Problem Mail me [email protected]
Subramanyam Rompicherla17y ago
hi Jay,
check the below links

http://209.85.129.132/search?q=cache:UW2H7LcmLtcJ:www.experts-exchange.com/Programming/Languages/C_Sharp/Q_21678691.html+how+to+read+the+excel+file+data+thru+c%23.net&cd=10&hl=en&ct=clnk

http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx
if not working please let me know i will write a sample application ([email protected])