4 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Lost data from Excel

    AmyNRA _

      I'm getting my data from Excel.  There is a field with employer Zip code that is ending up Null every time.  This field is usually[/I] empty.  When it's not empty, it contains only numerals.  I don't even find any spaces in the ones I've spot checked.  But Monarch is choosing to make it a character field, so I don't know why it would exclude any data.


      On these same lines that have employer data, the street address and city/state fields import just fine. 


      Any ideas why it would not be picking up the Zip code?

        • Lost data from Excel
          Data Kruncher

          Hi Amy,


          When Monarch reads in your Excel, it scans the first 250 rows or so to determine what the field types should be for each column.


          You'll probably find that you'll have better results if you first sort the Excel file in Excel so that the numeric zip codes appear at the top.


          Right now it's finding blanks so it prefers to go with the Character field type by default.




            • Lost data from Excel
              AmyNRA _

              Thank you.  Good idea.


              I don't suppose (in case I run into a larger file where sorting by one field will make another one import incorrectly) there's a way to force the type Monarch uses?

                • Lost data from Excel
                  Data Kruncher

                  It wouldn't be the most elegant solution ever devised, but you could cheat a bit and fill the first rows in the Excel file with dummy data of the correct field type that you want Monarch to use.


                  Then filter out your dummy records. Fill one of the character fields with "DUMMY" or some such other unique text, then be sure to build a filter to exclude those records.


                  That should work.

                    • Lost data from Excel
                      Grant Perkins



                      V9 allows you to force a field to be character as part of a database import but it sounds like you are already getting that but with null entries, which is odd since I would normally expect a null to be a character value in a numeric field.


                      You can also set NULL, when it occurs, to be presented as something else.


                      Kruncher's solution is a god catch all for a first read of a database when you are not applying an existing model. Also can be used to ensure you obtain the field sizes you will need to cover any possible data input when using delimited files.