11 Replies Latest reply: May 15, 2014 9:52 AM by RalphB _ RSS

    Database column format

    debijo _

      I have a XLS that I did a database Open on.  I went through the little wizard and get to the table view.  One of the fields "domain" is coming through as numeric.  While some of the data in the xls column is numeric, most is alpha characters.  The alpha's are all showing up as (Null).  I tried going through the wizard again, but did not see where I could override monarch's decision of this being a numeric column.  I also tried going to field properties and changing it, but the "numeric" is grayed out.  Any way (short of modifying the XLS sheet) that I can get Monarch to believe it's really a character field?

       

      Thanks in advance,

      Debi

      8.01 pro

        • Database column format
          debijo _

          I have a XLS that I did a database Open on.  I went through the little wizard and get to the table view.  One of the fields "domain" is coming through as numeric.  While some of the data in the xls column is numeric, most is alpha characters.  The alpha's are all showing up as (Null).  I tried going through the wizard again, but did not see where I could override monarch's decision of this being a numeric column.  I also tried going to field properties and changing it, but the "numeric" is grayed out.  Any way (short of modifying the XLS sheet) that I can get Monarch to believe it's really a character field?

           

          Thanks in advance,

          Debi

          8.01 pro

          • Database column format
            Data Kruncher

            Debi,

             

            You're correct: the only way to resolve this is to make changes to the xls file.

             

            Obviously, the preference is to make this work within Monarch. Since we cannot (I don't see anything in the Options to override this behaviour), there's an easy way to get what you need by adding to the xls file.

             

            Insert a new column next to the Domain column. Supposing that the Domain is in column B, and the new column is A, in column A type the formula

            [font="courier"]=TEXT(B1,"#")[/font][/quote]for each record.

             

            The other point worth mentioning is that in my test Monarch seemed to determine the data width for the field from a sample of the first few rows, so your data may be cropped. To force Monarch to set the proper field width, add yet another column with the formula

            [font="courier"]=LEN(A1)  /font[/quote]for each record. Now sort your data based on this column in a descending order, so that the longest fields are at the top of the list.

             

            You don't need this column now, so you can delete it if you'd like.

             

            You should find that Monarch now reads the xls database properly.

             

            The field width being set on some sample data is a bit concerning, though, because other fields could get shortchanged.

             

            When defining the columns to import, choose the one with the LEN formula, and don't import the other.

             

            If I'm missing something or just plain wrong, I'd appreciate it if someone were to point Debi and I in the right direction.

             

            HTH,

            Kruncher

            • Database column format
              Data Kruncher

              Debi,

               

              You're correct: the only way to resolve this is to make changes to the xls file.

               

              Obviously, the preference is to make this work within Monarch. Since we cannot (I don't see anything in the Options to override this behaviour), there's an easy way to get what you need by adding to the xls file.

               

              Insert a new column next to the Domain column. Supposing that the Domain is in column B, and the new column is A, in column A type the formula

              [font="courier"]=TEXT(B1,"#")[/font][/quote]for each record.

               

              The other point worth mentioning is that in my test Monarch seemed to determine the data width for the field from a sample of the first few rows, so your data may be cropped. To force Monarch to set the proper field width, add yet another column with the formula

              [font="courier"]=LEN(A1)  /font[/quote]for each record. Now sort your data based on this column in a descending order, so that the longest fields are at the top of the list.

               

              You don't need this column now, so you can delete it if you'd like.

               

              You should find that Monarch now reads the xls database properly.

               

              The field width being set on some sample data is a bit concerning, though, because other fields could get shortchanged.

               

              When defining the columns to import, choose the one with the LEN formula, and don't import the other.

               

              If I'm missing something or just plain wrong, I'd appreciate it if someone were to point Debi and I in the right direction.

               

              HTH,

              Kruncher

              • Database column format
                RalphB _

                Debi,

                 

                I have the same problem from a different department that I could not add or delete columns in the spreadsheet.  The solution I came up with is in Excel, select the column that has both, then on the Tools menu select Data, then select "Text to Columns" option. Then select "Delimited", click next twice and on the third tab select the column data format of "text" and then finish.  This will convert the entire colum to text. 

                 

                I found that this solution works when you don't or can't add columns to the spreadsheet.

                • Database column format
                  RalphB _

                  Debi,

                   

                  I have the same problem from a different department that I could not add or delete columns in the spreadsheet.  The solution I came up with is in Excel, select the column that has both, then on the Tools menu select Data, then select "Text to Columns" option. Then select "Delimited", click next twice and on the third tab select the column data format of "text" and then finish.  This will convert the entire colum to text. 

                   

                  I found that this solution works when you don't or can't add columns to the spreadsheet.

                  • Database column format
                    Grant Perkins

                    Here is a reference to an earlier post on this problem. Basically the solution is as Kruncher suggested with a variation or two offered. I quite like the sound of Ralph's solution as well.

                     

                    I guess thay all depend on whether one has proper access to the Excel worksheet to make use of the 'modifications'.

                     

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

                     

                    The idea of sorting on the offending column to get the text to the top is nice  - so long as that does not cause problems in another column I suppose. Some spreadsheets just don't want to be analysed I have found!  

                     

                     

                    HTH.

                     

                     

                    Grant

                    • Database column format
                      Grant Perkins

                      Here is a reference to an earlier post on this problem. Basically the solution is as Kruncher suggested with a variation or two offered. I quite like the sound of Ralph's solution as well.

                       

                      I guess thay all depend on whether one has proper access to the Excel worksheet to make use of the 'modifications'.

                       

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

                       

                      The idea of sorting on the offending column to get the text to the top is nice  - so long as that does not cause problems in another column I suppose. Some spreadsheets just don't want to be analysed I have found!  

                       

                       

                      HTH.

                       

                       

                      Grant

                      • Database column format
                        debijo _

                        I do appreciate all the input.  My main problem here is I am trying to export the automation of the export.  It's not automated if I have to manually modify the spreadsheet each time.  

                         

                        I did get it to work with my manual modification. 

                         

                        Thanks again,

                        Debi

                        • Database column format
                          debijo _

                          I do appreciate all the input.  My main problem here is I am trying to export the automation of the export.  It's not automated if I have to manually modify the spreadsheet each time.  

                           

                          I did get it to work with my manual modification. 

                           

                          Thanks again,

                          Debi

                          • Database column format
                            RalphB _

                            Debi,

                             

                            I have mine scripted to format the column to text.  This is a sample of my code in excel vb.  

                             

                             

                            Workbooks.Open Filename:=pathandfile

                                    SheetName = "Sheet1"

                                                Columns("I:I").Select

                                        Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _

                                            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

                                            SemiColon:=False, Comma:=False, Space:=False, Other:=False, fieldinfo:=Array(1, 2)

                                  Range("A1").Select

                                         

                                    ActiveWorkbook.Close (True)

                             

                             

                            Hope this helps.

                             

                            Ralph

                            • Database column format
                              RalphB _

                              Debi,

                               

                              I have mine scripted to format the column to text.  This is a sample of my code in excel vb.  

                               

                               

                              Workbooks.Open Filename:=pathandfile

                                      SheetName = "Sheet1"

                                                  Columns("I:I").Select

                                          Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _

                                              TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

                                              SemiColon:=False, Comma:=False, Space:=False, Other:=False, fieldinfo:=Array(1, 2)

                                    Range("A1").Select

                                           

                                      ActiveWorkbook.Close (True)

                               

                               

                              Hope this helps.

                               

                              Ralph