Input array longer than number of columns Error importing csv to sql server using vb.net

I am trying to have this app search through a directory and everything in the directory that is not older than 20 hours import into sql server.  If I use this app with the attached file "test file that works" everything works fine.  If I use it with the attached file "test file that does not work" I get an error "Input array is longer than the number of columns in this table" at the time of filling the datatable.  The only difference I can see is the one that doesn't work has many more rows of data than the one that does work.  I also tryed taking the one that does not work and delete all rows but the first 20 and it worked.
code.txt
testStabilityDetail-501501-1.csv
StabilityDetail-501643-1.csv
jeremyj54Asked:
Who is Participating?
 
Shahan AyyubConnect With a Mentor Senior Software Engineer - iOSCommented:
If you will open your csv files in notePad you will see this in success file: (first three lines)

Analyzer Serial Number,501501,Tools=3.0.32,DPP=2.12.1,PSM=3.65,PCB=3.21,OS=1.10.7,Det=SDD,Area=25,Vendor=1,Temp=-30,CamColl=False,Tube#,57019-04206,Detector#,112829,,,,,,,,,,,,,,
Stability Start Date,8/1/2012,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Stability Start Time,11:29:56,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Open in new window


and failure one has: (first three lines)

Analyzer Serial Number,501643,Tools=3.0.33,DPP=2.12.2,PSM=3.65,PCB=3.21,OS=1.10.7,Det=SDD,Area=25,Vendor=1,Temp=-30,CamColl=False,Tube#,62091-05022,Detector#,107412
Stability Start Date,08/23/2012
Stability Start Time,14:23:27

Open in new window


That is missing commas at the end of lines from failure one.

From the code you have attached the issue seems to be here:

                Using sr As StreamReader = File.OpenText()
                    While Not sr.EndOfStream
                        Dim vals As Object() = sr.ReadLine().Split(","c)
                        If DeltaData.Columns.Count = 0 Then
                            Dim cols(vals.Count - 1) As DataColumn
                            For c As Integer = 0 To UBound(cols)
                                cols(c) = New DataColumn("Columns " & (c + 1)) ' vals(c)
                            Next
                            DeltaData.Columns.AddRange(cols)

                        End If
                        DeltaData.Rows.Add(vals)

                    End While
                End Using

Open in new window


If "vals" (seems to be list of values) do not have same no of elements as compared to "cols" (seems a list of column names) you need to add default values either zero of empty string as per the type. Once your column and values have same length the issue may resolve.
0
 
CodeCruiserCommented:
There must be a row with a extra comma somewhere.
0
 
jeremyj54Author Commented:
There are not, actually what I found is that if I open that file and save it the application works fine but since I am trying to load many files a day that is not an option.  Why is the application seeing it incorrectly.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
CodeCruiserCommented:
It can be encoding. Do you know what encoding the input files are in?
0
 
jeremyj54Author Commented:
I don't, how would I find out, and what would I do with encoding?
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
There are not, actually what I found is that if I open that file and save it the application works fine but since I am trying to load many files a day that is not an option.  Why is the application seeing it incorrectly.

It is also possible that no one have used the cells and cells are empty, so StreamReader can not evaluate if it is a used cell or not and returned less values than the no of columns.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.