18 Replies Latest reply: May 15, 2014 10:08 AM by Grant Perkins RSS

    Monarch Registry Settings

    alexcn _

      I am getting an error when setting up an External Lookup:

       

      "An error was encountered while reading the sample data.  The value at row 1, column 20 could not be read"

       

      The column used to be a numeric column (forced by Monarch) so I decided to prefix it with a character to push it the other way.  Now it comes up with this error, however before because the column SHOULD be alphanumeric but at the moment only contains numeric values Monarch is wrongly interpretting it.  I have tried changing the XML definition in the MOD file but it doesnt work.

       

      I seem to remember there being a registry setting controlling the interpretation method, but cant find details in the forum.  Can I trouble someone for their thoughts?

       

      Thanks,

       

      Alex

        • Monarch Registry Settings
          alexcn _

          And the "column 20, row 1" field only contains "S0" and I have even investigated with Word to see if there are any control characters present, and not ...

            • Monarch Registry Settings
              Olly Bond

              Hello Alex,

               

              Just wondering if you could help us out a little - what version of Monarch are you using? Your profile says v8 Pro - is that still right? And what format is this external lookup file - I'm guessing Excel (.xls) format?

               

              Do you have control over the xls file - could you convert it to comma separated (.csv) instead?

               

              Best wishes,

               

              Olly

                • Monarch Registry Settings
                  alexcn _

                  Thanks Olly,

                   

                  Oops sorry I didnt realise the forum details were out of date: it is Monarch Pro V10

                   

                  The format of the file is CSV, but I seem to remember someone telling me it worked off the same interpretation rules as Excel.  Clearly the error is centred around it thinking the column is or was a numeric, and now it isnt ....

                   

                  Its been bugging me ALL afternoon and I am at the "throw it out the window" stage.  I now have tried changing the values on the keys to be prefixed by characters, and also tried removing the external lookups and adding again ... but to no avail.

                   

                  Please help me Obi Wan, youre my only hope

                    • Monarch Registry Settings
                      alexcn _

                      Even if I create a model from scratch, reading it a file it says that it cant read row 1, column 9 however again column 9 contains the value "S0" so perhaps because it is 50:50 alpha and numeric MOnarch is confused:

                       

                      DateProc,AcctClr,AcctSrc,ExchSrc,ExchDest,SymSrc,SymDest,SymExpDate,SymStrike,SymCPF,SymTypeMain,SymType

                      2010-07-23,"NEWHK","V-808-A0001","27","EURFKT","BM","EURBUND","M20100","S0","F","FUT","F"

                      2010-07-23,"NEWHK","V-808-A0002","27","EURFKT","IT","EURSMI","M20100","S0","F","FUT","F"

                      2010-07-23,"NEWHK","V-808-A0003","27","EURFKT","BM","EURBUND","M20100","S0","F","FUT","F"

                      2010-07-23,"NEWHK","V-808-A0004","27","EURFKT","BM","EURBUND","M20100","S0","F","FUT","F"

                      2010-07-23,"NEWHK","V-808-A0005","27","EURFKT","BM","EURBUND","M20100","S0","F","FUT","F"

                        • Monarch Registry Settings
                          alexcn _

                          I just tried opening the file in Edit.exe in DOS and it doesnt throw up any errors, and also doing a File, Save As from Excel on the same file and opening the file in Monarch and it still happens.  Was thinking perhaps there were some hidden control characters I couldnt see ...

                            • Monarch Registry Settings
                              alexcn _

                              Thanks Olly,

                               

                              Just to answer the points raised:

                              1) I have tried a fresh new model, and it wont even open the original csv file: it finds a problem with the same value "S0" (zero) in the first row.  Obviously the column is different than in the external lookup.

                              2) The file does have a header row, there is no way to override the interpretation in the preview window when first importing a database or linking to a database on external lookup.  3) Like with you, it just says it will set the subsequent bad values to null, which is NOT what I want.

                              4) I have it as a text field in that it is surrounded by double quotes.  I can understand Monarch getting confused if it was exspecting a number and it got X12345, but it is quoted "S0" as written.  Just wont work, even on an empty model and using the csv file as the main database (i.e. not linked)

                              • Monarch Registry Settings
                                Olly Bond

                                Hello Alex,

                                 

                                You can check out the hidden characters here:

                                 

                                http://www.datawatch.com/_support/downloads_updates.php#MonarchUtils[/url]

                                 

                                File Strip is the tool for the job. But I'd be surprised if that was the cause of this issue.

                                 

                                Best wishes,

                                 

                                Olly

                                  • Monarch Registry Settings
                                    alexcn _

                                    I just tried search/replace all the quotes OUT from the file in Notepad, and still it wont load.  Same error.  I have been using Monarch since it first started (yes v1) and I have never ever encountered this kind of problem

                                      • Monarch Registry Settings
                                        alexcn _

                                        Hi Olly,

                                         

                                        I have a formula to create and append the "S" at the beginning just to "Kid" it to look like a char when it just contains numeric data so its not PURE source data as it were:

                                        "S"+If(Val(SymStrike1)>0,SymStrike1,"0")

                                         

                                        Okay I have tried running the FIle Strip utility, and get the following, as you can see I even tried renaming the file to avoid any DOS file name issues:

                                         

                                        FILSTRIP creates a new file replacing the non-printable characters contained

                                        in your original file with spaces.  Only characters with ASCII values of 32 an

                                        above

                                        and CR(13), LF(10), TAB(09), and TOF(12) are retained.

                                        Usage is: FILSTRIP

                                          • Monarch Registry Settings
                                            alexcn _

                                            I have just emailed a copy of the file to you, to see if you can recreate the same problem your side. With and without surrounding quotes ...

                                            • Monarch Registry Settings
                                              Olly Bond

                                              Hi Alex,

                                               

                                              It looks like something's going on that I can't replicate here with standard (Betty's Music Store) data. Is there anyway you can share the data with me? I've got a Huddle space if you'd like to avoid email and password-protect it.

                                               

                                              Drop me a line at olly@greenbar.info[/email] if you'd like to explore this route.

                                               

                                              Best wishes,

                                               

                                              Olly

                                                • Monarch Registry Settings
                                                  alexcn _

                                                  Thanks Olly,

                                                   

                                                  I owe you one, I have emailed the two original emails to your olly@greenbar.info[/email] account.

                                                   

                                                  Thanks,

                                                   

                                                  Alex

                                                    • Monarch Registry Settings
                                                      alexcn _

                                                      I am very keen to see if you can replicate the problem: the data originally came off a machine in Hong Kong, so that could explaijn the zero of the "S0" as I simply append the leading S to the beginning, hence the 0 is from source.

                                                        • Monarch Registry Settings
                                                          alexcn _

                                                          Just to save you trying it: if I "delete" the contents of the column SymStrike in excel and then save the file, it opens perfectly fine, so there MUST be something that Monarch doesnt like in the "S0" field.  I also tried opening the file and manually refilling the values S0 in the SymStrike column, then when I reopen in Monarch: same error.

                                                           

                                                          The help file doesnt help either as the file DOES have header rows AND I have selected to use them ....:

                                                          An error was encountered while reading the sample data. The value at row , column could not be read. This and subsequent bad values will be treated as nulls.

                                                          This message occurs when you attempt to open a delimited text file (via the File, Open Database menu option) which has no field names in the first row, and you clear the "First row contains column names" option on the Sample Data Screen of the Open/Join Database Wizard. Either select the "First row contains column names" option or try using another delimited text file.

                                                           

                                                          Also just now tried replacing the S0 with SS0, and THAT works perfectly.

                                                  • Monarch Registry Settings
                                                    warlok

                                                    Hi,

                                                     

                                                    Just something I noticed which might lead you on the right path. If not, I apologize.

                                                     

                                                    There seems to be a problem with the input data you had posted. When I take your information save it as a .csv file and read it back into Excel, I noticed under the SYMCPF column on the second data row that the F wasn't in quotes like it was for all other rows. When I looked at the input you had given there is a space between the " and the F. It looks like it's being interpreted different way which could be causing Monarch to grumble about the data.

                                                      • Monarch Registry Settings
                                                        Sue DeWitt

                                                        It seems that there is a problem with external sources that start with S#.

                                                         

                                                        I have a simple table with 2 columns, 18 rows

                                                         

                                                        S000  Default

                                                        S071  Toronto

                                                        S081  Montreal

                                                         

                                                        I was getting the same error on row 1, column 1.

                                                         

                                                        Thought maybe it was a problem with S000. So removed that line.  Nope, not it.

                                                        Maybe S0xx - removed all those lines.  Nope, not it.

                                                        Changed all the S characters to D - no error.

                                                         

                                                        So what's wrong with S-followed by a number??

                                                      • Monarch Registry Settings
                                                        Grant Perkins

                                                        For some reason this sounds ever so familiar - specific character causing problems  - but just at the moment I can't recall the when or the why. Nor think how to search for it.

                                                • Monarch Registry Settings
                                                  Olly Bond

                                                  Hello Alex,

                                                   

                                                  (Just seen your post, try removing all the quotes, which should be safe as your data doesn't look like it contains commas?)

                                                   

                                                  I've rebuilt an example from Classic.prn and tested this in 10.5 Pro on Vista. It should be OK - I didn't get an error message, just a load of Null values in the (old) numeric field. I double clicked on the field and set it to Character and the lookup behaved perfectly.

                                                   

                                                  My data has "" around the character field, and a header row. When I edited the CSV in Notepad to add an X before the numeric data, it looked like:

                                                   

                                                  Customer,NumAccount

                                                  "Betty's Music Store",X11887

                                                  "Big Shanty Music",X17959

                                                  "Bluegrass Records",X10929

                                                  "Musique du Monde",X18635

                                                  "Fandangos Records",X17658

                                                  "Die Melodie",X13487

                                                  "Hope's Sweet Notes",X10609

                                                  "Canciones",X12705

                                                  "Classic Exchange",X11433

                                                  "Chez Rudy",X15091

                                                  "Das Piano",X18172

                                                  "Mo Town Tunes",X10073

                                                  "The Record Store",X15844

                                                  "Reiner's Symphonic",X16284

                                                  "Spinning Records",X15403

                                                  "Musique Royale",X19764

                                                  "The Glass Harmonica",X12014

                                                  "Die Harmonie",X11860

                                                  "The King's Place",X18917

                                                  "Notas Musicales",X14162[/CODE]

                                                   

                                                  It still worked after that - but perhaps the lack of a header row is causing your issue?

                                                   

                                                  (Generally, it's not advised, or supported, to poke around in the source XML of a model file... Do you have a backup you can revert to?)

                                                   

                                                  Best wishes,

                                                   

                                                  Olly