11 Replies Latest reply: May 15, 2014 10:05 AM by ccassidy _ RSS

    Text Qualifier in Database

    ccassidy _

      When I open a database I change the text qualifier field from quotes to none.  I save the model. 

       

      The next time I open the database I need to change the text qualifier again.

       

      Is there a way to make none the default setting for the text qualifier field?

        • Text Qualifier in Database
          Grant Perkins

          When I open a database I change the text qualifier field from quotes to none. I save the model.

           

          The next time I open the database I need to change the text qualifier again.

           

          Is there a way to make none the default setting for the text qualifier field?[/quote]

           

          Hello ccassidy and welcome to the forum.

           

          Are you opening the saved model as well as the database? Do you get the same result if you save the combination as a project and open that? (I am assuming that a project would work for your needs.)

           

          I am curious about the need to unset the text qualifier. Presumably you have a specific requirement that does not allow you to leave the default  in place?

           

          Grant

            • Text Qualifier in Database
              ccassidy _

              I am opening the saved model and the database, then I need to change the text qualifier to "none". I have also tried saving as a project with no luck.

               

              I work at a hospital where we use the quote (") to represent a financial class in a column.  If I don't use "none" as the text qualifier in the database I don't get the correct results.

                • Text Qualifier in Database
                  Olly Bond

                  Hello Ccassidy,

                   

                  Quick check - which version of Monarch are you using, and what sort of licence is it (standalone, network, Citrix etc)?

                   

                  And can you see /edit settings in the registry on your local machine?

                   

                  Checking the "Settings" entry in Monarch help suggests that this may be stored in the project, the model or the registry - we may have to do a little detective work for you!

                   

                  Best wishes,

                   

                  Olly

                    • Text Qualifier in Database
                      ccassidy _

                      We're using Monarch 10.0.  We have a network licence.

                       

                      How do I check to see if I have access to the registy?

                        • Text Qualifier in Database
                          Grant Perkins

                          Interesting reason. Seems like an odd character to use - but then I guess it looked like a good idea at the time to whoever decided to do it.

                           

                          These little gems crop up from time to time ...

                           

                          The character selected or is stored in the model file. However that particular value is not part of what is reported in the Model Information screen.

                           

                          If you save the model and then navigate to it and open with Notepad (or other suitable editor) and then search for text_qualifier you should find it near the end of the unformatted XML text. (I see it on the last line.)

                           

                          That then begs the question about why you don't see it used when you open the database and apply the model.

                           

                          I may need to create something to test that as I don't think I will easily fond a suitable test file. Creating something may be quicker.

                           

                           

                          Grant

                            • Text Qualifier in Database
                              Grant Perkins

                              OK,  a quick test with a text file suggests that Monarch ignores that value in the model file and does a dynamic assessment of the 'database' file on opening. Makes no difference if the model is opened at the time or if a Project is opened - except that a Project will not give access to the load parameter options.

                               

                              This is for V10.0. I have not tried 10.01 yet.

                               

                              Are you running 10.0 or 10.01?

                               

                              If not 10.01 has anyone else got 10.01 Pro installed to try it? A simple test with a 1 line csv file will do.

                               

                               

                              Grant

                                • Text Qualifier in Database
                                  ccassidy _

                                  Our financial class field is only one character long, with the flood of new HMOs we ran out of available characters so we started using characters we really didn't want to such as " and ?(which also causes problems).

                                   

                                  We are using Monarch Pro 10.0.

                                   

                                  I do see where the Text_qualifier is when I opened in Wordpad.

                                    • Text Qualifier in Database
                                      Grant Perkins

                                      Our financial class field is only one character long, with the flood of new HMOs we ran out of available characters so we started using characters we really didn't want to such as " and ?(which also causes problems).

                                       

                                      We are using Monarch Pro 10.0.

                                       

                                      I do see where the Text_qualifier is when I opened in Wordpad.[/quote]

                                       

                                      That's a reasonable explanation. Has to be a short term fix though surely? Are there no alternative fields you could use for the purpose (perhaps currently unused) with the potential for Monarch to pull everything back together even if standard system reports don't provide the reporting combinations you require? It might be a way to buy time.

                                       

                                      Meanwhile ...

                                       

                                      I would suggest contacting Datawatch support (in the absence of anyone active on here with a ready-to-test 10.01 installation) snce they should eb able to tell you quite quickly if this is a known problem addressed in the 10.01 release. If nothing is known I would assume that 10.01 would be the same as 10.0. I can't think of a good reason for adding the pparameter to the model and then ignoring it (which does not mean there is anot a good reason ...) so suspect this is a bug. Unfortunately that would not help you with your immediate problem.

                                       

                                      If, as I suspect, the database load rotuine is doing an automatic assessment of likely text qualifiers I doubt that any method of changing the defaults would help. I wonder if there is a way of disabling the auto assessment? If there is it will be a parameter in the registry entries somewhere I would guess. I will try to have a look later when time allows.

                                       

                                       

                                       

                                      Grant

                                      • Text Qualifier in Database
                                        Olly Bond

                                        Hello CCassidy,

                                         

                                        Having a single character database field containing " and ? is, ahem, unconventional. It reminds me of the cartoon: http://xkcd.com/327/[/url]

                                         

                                        However, there's a serious point, Monarch should behave better when opening the data so that you can overcome the problem. I guess it might be using some logic like looking at the first n rows to see if there's a " in the first position of a field, then defaulting to using " as a delimiter if it finds it.

                                         

                                        Four fixes suggest themselves:

                                         

                                        1. find and change the registry setting to stop Monarch from second guessing the delimiter character beyond 0 rows.

                                         

                                        2. accept that it's going to happen, and have a calculated field to handle the null values and replace them as " (or chr(34) when you are using Monarch's expression builder).

                                         

                                        3. Export the database as a CSV file and then opne it as a report not as a database and use Monarch's floating trap or Lsplit functions to extract your data.

                                         

                                        4. Export the database as a report file and trap it conventionally.

                                         

                                        HTH,

                                         

                                        Olly