4 Replies Latest reply: May 15, 2014 9:53 AM by Glen Nickerson RSS

    XLS Database Input Problem

    Glen Nickerson

      Using v9.0 Pro and verified against v8.2 Pro also, when I open an XLS worksheet as the input and select a character field (i.e. Part Description) if the character values in the field are all numeric, the Monarch Table shows the field as a Null value.  Is there a setting that I'm missing? Any help would be appreciated.

        • XLS Database Input Problem
          Data Kruncher

          Hi Glen,

           

          This is definately a problem with mixed data types.

           

          Have a look at  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000922#000000"]Ron's similar post[/url] from awhile back. Grant, Dee and myself proposed different possible solutions at the time.

           

          Does anything there help you at all?

           

          Kruncher

          • XLS Database Input Problem
            RalphB _

            Glen,

             

            I've had the same problem and what I ended up doing is converting everything in the column to text.  How I do it is select the column in excel and then go up and click DATA on your menu bar, and then select "Text to columns".  You can select either option and then click next twice.  On the 3rd screen of the wizard, in the column data format box, check the "text" button.  This will convert everything to text format quickly and easily. 

             

            I have about 4 spreadsheets that I have to do this daily so I have scripted the process so Monarch can read everything.  This is easy to script.

             

            Hope this offers another solution to your problem.

            • XLS Database Input Problem
              Grant Perkins

              Glen,

               

              V9 allows you to change the field type when you read the XLS file at the import stage and then save the result in a model for re-use.

               

              Now I'm guessing that you have already tried experimenting in that area and found the problem still bothers you and I have to be honest and say I have not played that much with Excel files as databases read into V9 so I may easily have missed something that gives a contra indication but in theory V9 should help to resolve the problem you describe.

               

              Won't apply to V8 though so if you are developing for a mixed environment this may not help anyway.

               

              Save model and create a project for the next opening (if that will work with the workflow you need) and things should improve.

               

              Or not, if I have missed the point ...

               

              HTH.

               

               

              Grant

              • XLS Database Input Problem
                Glen Nickerson

                Thanks for all the input, the solution offered by RalphB appears to be the best but I would have thought that Monarch could have handled this.

                 

                Nick