3 Replies Latest reply: Aug 13, 2014 2:31 PM by Olly Bond RSS

    The value at row 1, column 2 could not be read!

    David Zelward


      I've experienced this error a couple of times and have not been able to resolve the problem no matter what I tried doing to the CSV file from excel. Has anyone else encountered this problem and managed to resolve it, and if so, how?

       

      Short history on the file(s). I work with contract files that are in an Excel "XLS" format with several columns of information. Column "B" contains a vendor part number and another column has current pricing information. The Monarch model I'm working with loads the Item Master file and then compares the data loaded via the CSV file to produce a list of "our" item numbers - with other information. This process works correctly 99% of the time, but there's always the odd file that produces the error reading the sample data - row 1, column 2. It doesn't matter if I delete the first row and start over, the error remains. Also, there is nothing unique about the files that produce the error in that the part number is generally Apha-Numeric. Plus, I use a "trim" command in Excel to remove any blank spaces and then paste the "values" back to the column.

       

      Any help or suggestions will be appreciated.

       

      Thanks,

       

      Dave

        • Re: The value at row 1, column 2 could not be read!
          Olly Bond

          Hello Dave,

           

          Excel and Monarch generally guess whether a column like Column 2 is pure numeric or alpha-numeric by looking at the first 250 or so rows. Defining the field to be character solves the issue - Monarch also has powerful trim functions - so one option is to take the external lookup from Excel, into Monarch as a separate project, convert column 2 to character, force it to be UPPER() and TRIM(), then export it as an Access mdb table. This preserves the field type and avoids any risk of error when using it as a subsequent lookup.

           

          The Row 1 aspect of the error suggests you might not be ignoring field names in the first row - there's an option in the external lookup dialog to say that "first row contains field names" which might help.

           

          Best wishes,

           

          Olly

            • Re: The value at row 1, column 2 could not be read!
              David Zelward

              Hi Olly,

              I was actually doing what you suggested in Excel and formatted the column as "TEXT, before saving the file. Since my post, I have resolved the problem and perhaps discovered an anomaly in Monarch. My initial thought about the issue was that column "B" had corrupt data elements that I could not see, even after using the trim command. The offending data is/was an Alpha Numeric  part number, consisting of one letter and 9 digits. I decided to strip the letter, which happens to be a capital "H", and try loading the file. It worked! I didn't have any matches, of course, but it was a hint as to the nature of the problem. I put the "H" back on the number and tried the file again - failure! Next, I replaced the "H" with an "A" and loaded the file. It worked again! So, my solution was to replace the "A" with a lower case "h" and load the file. It worked just fine and I had results!

               

              So now I'm wondering if an "H" followed by a number has a special meaning to Monarch? Anyway, I was able to complete what I had to do and have a solution (hopefully) for the next time it happens.

               

              Regards,

               

              Dave

                • Re: The value at row 1, column 2 could not be read!
                  Olly Bond

                  Hello Dave,

                   

                  The letter H, and all the other letters in ASCII, ANSI, and Unicode, are all treated the same. You only have to watch out for the trap characters ÃßÑØ| when you are working with report data (not Excel data), and there are alternates to use if you stumble across these (Portuguese, German, Spanish, and Scandinavian users, and SAP users working with Dynamic List Display data - please use v10 or above with the Alternate Trap Characters)...

                   

                  Sounds like a case matching issue - "h" isn't "H" to a computer's eyes - and a preparatory step in Monarch to force both source and lookup to upper or lower case should work fine. If you need to automate it, and need to verify that all records are matched when you run it with the lights out, that's easily done in Automator.

                   

                  Best wishes,

                   

                  Olly