[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-24
6
Medium Priority
?
2,225 Views
Last Modified: 2013-07-27
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
0
Comment
Question by:jeremyj54
  • 2
  • 2
  • 2
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38329808
There must be a row with a extra comma somewhere.
0
 

Author Comment

by:jeremyj54
ID: 38330137
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38331533
It can be encoding. Do you know what encoding the input files are in?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jeremyj54
ID: 38331548
I don't, how would I find out, and what would I do with encoding?
0
 
LVL 19

Accepted Solution

by:
Shahan Ayyub earned 2000 total points
ID: 38331871
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
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 38331873
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

873 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