5 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Importing data from an excel sheet

    varun _

      I have to extract data from an excel sheet using Monarch(I know it sounds stupid). The problem is, when I open the database, character values that appear in columns with (predominantly)numeric values are treated as null(and vice versa). I believe Monarch sets the data type of the majority of cells as the data type of the column. Also I do not have the option of changing the data type.

      How do I extract such characters?

        • Importing data from an excel sheet
          Grant Perkins

          Hi Varun,

           

          I think your analysis is correct. I have been having a look at this using V8 and the import engine version of MS Jet that it uses for Excel seems to assume one header row exists and then sets the column values based on what it finds in subsequent rows. I'm not sure how many it checks - 250 comes to mind but that may be wrong. So if I have 10 lines, 5 numbers and 5 text, I get numeric. If there are 11 lines, 5 numbers and 6 text it gives Character fields but skips the numerics (it seems).

           

          Have a look at this post from a much earlier question and version but probably still holds true - I have not tried this myself.

           

            [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000032#000000"]http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000032#000000[/url] 

           

          It may help.

           

          The other options seems to involve exporting the Excel sheet to either a fixed width 'report' file and then reading that as a 'normal' report using  Monarch. (Printing the Excel worksheet columns to a file as a report would have the same effect.)

           

          Or, if you have V8 Monarch, export the Excel sheet as a delimited file - .csv for example - and then use the exported file as your database rather than the Excel worksheet. V8 should then allow you to set the properties of the import columns to your preferred values.

           

          I hope this is of some help but wonder if others have found alternative ways to get around this issue.

           

          It is also possible that the year 2002 post is subject to more recent updates in the Microsoft knowledge base so a search their could also be useful.

           

           

          Grant

           

          Edit for some typos.

           

          [size="1"][ May 19, 2006, 03:45 PM: Message edited by: Todd Niemi ][/size]

          • Importing data from an excel sheet
            Nigel Winton

            Varun

            A quick fix I use is to sort the Excel sheet by the offending column descending. This puts the alpha fields at the top and Monarch treats the whole column as text.

             

             

            Edit

            Sorry forgot the other bit, before the sort, I convert the Excel columns to text using the formula =Text('Cell',0)in another column and then copy that back to the original column as values. You can accomplish this with a macro if you have to do it frequently.

            Alternativley you can use the Text formula in another column of your spreadsheet. Monarch will still tell you that it has found bad data in the first column, but you can ignore that one on the import and bring in the column with the Text formula as your data. YOu can then reposition the columns within the Monarch Table as required.

             

            Regards

             

            Nigel

             

            [size="1"][ July 05, 2005, 09:54 AM: Message edited by: Nigel Winton ][/size]

            • Importing data from an excel sheet
              varun _

              Hi,

              Thank you for your replies. I have finally decided to convert the excel into html and then process it. Using tab delimiter is not giving the desired result and using csv interferes with the data in the excel sheet.

              varun

              • Importing data from an excel sheet
                baghu _

                I'm using Monarch version 8 and I wanted to compare 180 excel sheets to prepare a new report. All the excel sheets with same column name and I would like to open muliple excel files as a multiple database input files.

                 

                Please advice.

                 

                Regards

                Baghavathy

                • Importing data from an excel sheet
                  Grant Perkins

                  Originally posted by baghu:

                  I'm using Monarch version 8 and I wanted to compare 180 excel sheets to prepare a new report. All the excel sheets with same column name and I would like to open muliple excel files as a multiple database input files.

                  /b[/quote]I think you would need to export the Excel files (I assume a single worksheet for the data you require) to a report file and then have Monarch open the 180 report files. Or maybe export from Excel to an accumualted data set in an Access file?

                   

                  Using Monarch 9 you could export to multiple .csv or .tab files or PDF files and then get Monarch to open all those at the same time.

                   

                  HTH.

                   

                   

                  Grant