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

Posted on 2012-08-24
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.
Question by:jeremyj54
    LVL 83

    Expert Comment

    There must be a row with a extra comma somewhere.

    Author Comment

    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.
    LVL 83

    Expert Comment

    It can be encoding. Do you know what encoding the input files are in?

    Author Comment

    I don't, how would I find out, and what would I do with encoding?
    LVL 19

    Accepted Solution

    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)
                            End If
                        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.
    LVL 19

    Expert Comment

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

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now