5 Replies Latest reply: May 15, 2014 9:53 AM by Grant _ RSS

    Importing from Excel

    DavidS _

      Hello,

       

      I frequently have a problem when importing Excel spreadsheets: if I have a column that contains text codes (for example, vendor codes), and some of those codes are numerical, they come over as blank fields into Monarch.

       

      For example, I have a spreadsheet where the first 6 rows contain the following values:

      [font="courier"]1

      2

      3

      4

      AA_01

      ABCC

      /font[/quote]Once imported, the first 4 rows contain blank values, while the other rows contain the proper values. This happens even when I define the Excel cells to be in text format.

       

      All advise will be greatly appreciated.

        • Importing from Excel
          DavidS _

          Hello Again,

           

          I saved the spreadsheet as a CSV file, and created a new project using the CSV file. All the fields that were previoulsy left blank are now properly populated.

           

          As a bonus, when importing from a CSV file, you are allowed to specify the field type, which you can't do when importing from XLS.

           

          On the other hand, if anyone has suggestions as to how to resolve the problem without having to resort to the CSV file, I'm still interested in your advice.

          • Importing from Excel
            RalphB _

            Hi David,

             

            I have the same problem with some Excel files.  The solution I use is convert the column in Excel to text.

             

            To do this, select the column or range of cells in the column and then select "Data" from the menu bar. Select "Text to Columns" option.  Click on Next twice and on step 3 of the wizard, click on the Text option under Column data format.  Then click finish.  This will convert the entire column data to text.

             

            I have this scripted since I use this on a daily basis and have multiple spreadsheets.  From there I apply my usual Monarch Model and go from there.

             

            Let us know if you have any problems.

             

            Ralph

            • Importing from Excel
              Data Kruncher

              Hi David,

               

              You've come across an known problem with mixed types within a field in an Excel database. There have been quite a few discussions here about this challenge.

               

              Fortunately, you've also discovered one of the solutions already!

               

              While there have been some other suggested solution (XL -> Access -> Monarch, being one), I think the CSV solution works as well as anything, perhaps better at times, and is pretty straightforward.

               

              Kruncher

               

              Edit: My turn to be beat to the post. I'd agree that converting the column to text is likely the easiest solution as it doesn't require the file format conversion and is really quick.

              • Importing from Excel
                DavidS _

                Ralph and Kruncher,

                 

                Thanks for the advice. I tried the "Text to Columns" option, and it worked like a charm.

                 

                Regards,

                 

                David

                • Importing from Excel
                  Grant _

                  I am having similar issues with Monarch/Datapump and Excel.  Taking the data directly from XLS is near impossilbe so I thought I would use Datapump to do the conversion to CSV and then have a second Monarch model open the CSV, this is not working as for some reason Monarch will no save the info to the model not to use the first row as column heading/field names so my filters, etc won't work or I get all <NULL> data.  Any suggestions?  Thanks