13 Replies Latest reply: May 15, 2014 9:55 AM by Gareth Horton RSS

    creating a tab delimited file

    Lanette _

      What is the correct extension to use if you are exporting data to a tab delimited file?  I have data from a report that I need to export as a tab delimited file so that I can use it in an external lookup of another model.  I have tried exporting the file with the extension .tab, .csv, and .tsv.  Each time I do this I get an error reading the file when it gets to line 129.  Below is the line causing the error in my file (in bold), with the two preceeding lines displayed as well:

       

      1725     7991659     VEHICLES FERC 165     5283

      1725     6001661     OTH/ADMIN COMP.     5530

      1725     9000000     AUC - GT     900000000393[/b]

       

      The error I get says "An error was encountered while reading the sample data.  The value at row 129, column 4 could not be read.  This and subsequent bad values will be treated as nulls".

       

      The only difference is that column 4 in this particular row goes from a 4 digit # to a 12 digit #. Otherwise, looking at the sample data in the preview window before you select the delimiter character, the data looks fine, with each column of data seperated in the row with a '     ', as below:

       

      1725     7991659     VEHICLES FERC 165     5283

      1725     6001661     OTH/ADMIN COMP.     5530

      1725     9000000     AUC - GT     900000000393[/b]

       

      Why should this cause an error?  I've tried making the data type for that field in my table window both Numeric and Character, but the same issues occurs. I need to be able to use tab delimited versus a comma or other character, because the data may contain any kind of special character in text fields.

       

      Any suggestions?

        • creating a tab delimited file
          Grant Perkins

          Hi,

           

          I think what you are getting is an error warning which may not be too much to worry about so long as the field defined in your model for the 4th field in you .tab output is either large enough to hold the full size of the biggest possible data string in that field OR will not be imported to the report anyway.

           

          If the model is not defined for link will try  to assess how big each field needs to be (and of what type and so on). As I recall this analysis uses information form the first xx rows where xx is less than 129!

           

          You could test my theory by moving row 129 to row 1 or cutting the first 100 rows or so from your .tab file and seeing if you get a different result.

           

          I get similar messages with some of my link files where what should be a numeric field is empty or has alpha characters for example. Sound like the same sort of problem - but I could be wrong so do let us know one way or the other please!

           

           

          Grant

          • creating a tab delimited file
            Lanette _

            Grant,

             

            Right now I am allowing 15 characters for the data in column 4 in my model.  I am not sure what you meant by "If the model is not defined for link", but I did test what you suggested by removing the first 100 rows from the file and the external lookup works fine.  If I ignore the error and try to use the original file, I get additional error messages. The first one says "The selected source columns do not forma a unique key to the external table..." and the second one says "An external lookup data source 'c:Program Files...' is either missing or invalid.

             

            The only way I am able to get the original file to work is to:

            1) change the data type on column 4 from character to numberic, and

            2) apply a descending sort to that column before I export it to make sure the largest values are at the top.

             

            Is there another way around this, or a way to get the model to analyze more than 128 rows of data?  Also, does it matter if you save a tab delimited file as .csv rather than .tab?  When I save it as a .tab file, it automatically associates my file as a SQL Table Script file.

            • creating a tab delimited file
              Grant Perkins

              Lanette,

               

              I haven't got my head round the need to do a descending sort as yet (is it just to ensure that the auto parse finds the big fields?) but for the other points here are my thoughts.

               

              If you change the field to numeric it will default to 18 chars (from memory) - have you tried changing your character field to 18?

               

              I don;t think there are any published setting to specify how many rows of data Monarch should parse during its auto data assessment phase. However the manual setting of field values should override any problems the automatic assessment fails to deliver on. (It is meant to be an assist rather than a total analysis so that the whole thing loads with reasonable speed.)

               

              I think your problem with the .tab file is that is subject to windows 'association' to a specific program which, in this case, is not the one you want to associate it with.

               

              One might have expected that whichever standard came second could have avoided the repetition of using the .tab extension but it appears not in this case.

               

              You could disassociate the .tab extension from everything or reassign the association. I would go the former route I think but it depends on how your local systems need to work.

               

              I would not save a tab separated file with a .csv extension. Probably confusion all round.

               

               

              I hope this helps.

               

              Grant

               

               

              Originally posted by Lanette:

              Grant,

               

              Right now I am allowing 15 characters for the data in column 4 in my model.  I am not sure what you meant by "If the model is not defined for link", but I did test what you suggested by removing the first 100 rows from the file and the external lookup works fine.  If I ignore the error and try to use the original file, I get additional error messages. The first one says "The selected source columns do not forma a unique key to the external table..." and the second one says "An external lookup data source 'c:Program Files...' is either missing or invalid.

               

              The only way I am able to get the original file to work is to:

              1) change the data type on column 4 from character to numberic, and

              2) apply a descending sort to that column before I export it to make sure the largest values are at the top.

               

              Is there another way around this, or a way to get the model to analyze more than 128 rows of data?  Also, does it matter if you save a tab delimited file as .csv rather than .tab?  When I save it as a .tab file, it automatically associates my file as a SQL Table Script file. /b[/quote]

              • creating a tab delimited file
                Lanette _

                Grant,

                 

                You are correct...the descending sort was intended to ensure that the rows with the largest numbers in that column would rise to the top, therefore being picked up by the auto data assessment phase.

                 

                I tried changing the field in question back from a numeric to character data type in my model and gave it a Display Width of 18.  I then exported all four fields in my table again to a .tab file, then used the file in an external lookup.  I did not apply a sort this time, so the first long # again shows up in Row 129...I get the same error message.  That indicates to me that the manual setting of field values is not overriding the problem discovered by the automatic assessment.  Perhaps this is something I should report as a possible bug?

                 

                BTW, you are correct on the association of the .tab file.  I have VS.Net on the machine I was working on, so it kept opening .Net when I opened the file.  This doesn't happen on a machine without .NET...it opens with notepad.

                 

                Thanks!

                 

                Lanette

                • creating a tab delimited file
                  Grant Perkins

                  Lanette,

                   

                  Did you set the DISPLAY width or the DATA Length?

                   

                  Data length is the important one here.

                   

                  And just to correct my earlier note in case it has misled you (sorry!). Numeric (calculated) fields default to 15 digits and an option decimal point and negation sign. The normal DISPLAY width would be 18 characters (or more). A field from a lookup table counts as 'calculated' unless otherwise set by the automatic parse, sort of, in this situation.

                   

                  Character fields will default to a Data width of 8 (or the parsed value) but you can display more than that or a smaller portion of the entire field.

                   

                   

                  Sorry if I confused previously.

                   

                  Grant

                   

                  Originally posted by Lanette:

                  I tried changing the field in question back from a numeric to character data type in my model and gave it a Display Width of 18. 

                  Lanette /b[/quote]

                  • creating a tab delimited file
                    Lanette _

                    Grant,

                     

                    I set the Display Width in the Field Properties window. I only see Display Width and Template Width as options here. Are you referring to the Data Length column in the Field List window?  Regardless, I have my Display Width set to 15 in the Field Properties window, so that is what I get in my Data Length column of the Field List window, regardless of whether I have the type set as numeric or character. When I export, I still get the same issue.

                     

                    Lanette

                    • creating a tab delimited file
                      Nigel Winton

                      Lanette

                             Do you need to use a .tab file, I found that exporting to excel or access then linking it back in was much less painful and works.

                       

                      Nigel

                      • creating a tab delimited file
                        Lanette _

                        Nigel,

                         

                        Unfortunately, I can't use Excel due to the limit in the number of rows you can have (65,536) in a file, although believe me I would prefer to.  However, our report size vary, so while most are well within the Excel range, some are not.

                         

                        Lanette

                        • creating a tab delimited file
                          RalphB _

                          Lanette

                           

                          I have the same problem with some of my reports I work with.  I export to an Access database and link back to it to get the final info I need.  I don't know what the limit for Access is, but it is far greater than Excel.  I have also noticed an increase in speed when linking to Access db compared to an Excel spreadsheet.

                           

                          Ralph

                          • creating a tab delimited file
                            Lanette _

                            Unfortunately Access is not an option for me either.  The models I am creating will be used by DataPump to mine report data monthly on a production server.  I am not able to put Access on this server.

                            • creating a tab delimited file
                              Grant Perkins

                              Hi Lanette,

                               

                              Well, I have attempted to create a tab delimited file that might simulate your problem but cannot get the same results.

                               

                              Oddly, using Excel to create a worksheet that I then save as a tab delimited .txt file, I can enter a 9 character numeric field OK but a 12 character reverts to scientific format no matter what I try. This then results in a blank field from the lookup.

                               

                              No error messages though.

                               

                              Suddenly thought - I am trying this with V7.01. You mention Data Pump - does that mean V6 (or 6.02) ? But I don't seem to get the problem with 6.02 either.

                               

                              Any chance of using copies of your files to try the analysis with?

                               

                               

                              Grant

                               

                                Originally posted by Lanette:

                              Unfortunately Access is not an option for me either.  The models I am creating will be used by DataPump to mine report data monthly on a production server.  I am not able to put Access on this server. /b[/quote]

                               

                              [size="1"][ July 12, 2004, 07:33 PM: Message edited by: Grant Perkins ][/size]

                              • creating a tab delimited file
                                Lanette _

                                Grant,

                                 

                                I am using Monarch Pro v7.01 to try to do this.  I will be using Data Pump v7 to automate the tasks using the models.  Right now I have DP v6 but I have not tried scheduling a task with this model and report to see if the error is duplicated.  I have noticed the same thing with the long number converting to a scientific format in the text file and was wondering if that contributes to the problem.

                                 

                                I would be happy to send you the model and the report file I am working with when I get this error.  Just let me know how you would like me to send it.

                                • creating a tab delimited file
                                  Gareth Horton

                                  Lanette

                                   

                                  From reading your initial post, you seem to exporting from Monarch.  You can export to Access files without needing the Access application installed on the server, then you can simply consume them as external lookups.

                                   

                                   

                                  Gareth

                                   

                                  Originally posted by Lanette:

                                  Unfortunately Access is not an option for me either.  The models I am creating will be used by DataPump to mine report data monthly on a production server.  I am not able to put Access on this server. /b[/quote]