9 Replies Latest reply: May 15, 2014 10:10 AM by Scott Eshleman RSS

    Tab Delimited File with Null values

    DD _

      I have a tab delimited file that contains a null character between every character. I am opening the files by opening monarch and then "open database". I choose my file, use tab as delimiter character, then I "select all" columns, I check the box "Replaces nulls with default values",  then monarch opens the file beautifully..... almost.  What is happening is in the file there are some numbers on separate rows but same column that the "-XXX" is getting omitted.  if I open the file using Excel the numbers appear fine. I have 20+ files like this that I will have to manipulate ever month. I am trying to you monarch projects to easy my pain of this repetitive task. Has anyone else experienced this and found a solution????  

       

      Before import minus the nulls

      Bill      12345

      Steve  12345-100

      Mark   12345-200

      Larry   12345-300

         

      After import in the table that monarch has created.

       

      Bill      12345

      Steve  12345

      Mark   12345

      Larry   12345

        • Tab Delimited File with Null values
          Joe Berry

          Just a thought - after import into Monarch is the field that contains the 12345 a numeric field type?  If so, try changing it to a character field type.

          • Tab Delimited File with Null values
            DD _

            Thanks for the suggestion but I already checked that and it is "character". Maybe that is it... just like excel assumes certain fields are numeric during import. Maybe Monarch is doing the same thing but then stuffing the results of thinking it is numeric into a character field.

              • Tab Delimited File with Null values
                Olly Bond

                Hello

                 

                Opening tab-delimited data as a database seems to be the cause. Two solutions: one, use MSRP.exe from the downloads on the Datawatch site to clean up the problem characters so the data imports cleanly. Two, open the file as a report instead of a database, and use the floating trap to get the data you need.

                 

                Hope this helps,

                 

                Olly

              • Tab Delimited File with Null values
                DD _

                OK, I think I found the real issue... When opening a file using the "Open Database" and monarch auto populates the table it is assuming that the column in question is only 5 characters wide because the 9 character wide number in that column is not reached until row 4,500. I tested this by manually adding additional characters to this column on row 4 and then the table was populated properly.  Does anyone know how to stop it from doing this? This is a "tab" delimited file. And why doesn't Monarch just populate the data in between each tab into its own column? Monarch identifies the file as being "tab" delimited. PLEASE HELP!!!!

                  • Tab Delimited File with Null values
                    Data Kruncher

                    When opening a file using the "Open Database" and monarch auto populates  the table it is assuming that the column in question is only 5  characters wide because the 9 character wide number in that column is  not reached until row 4,500.[/QUOTE]

                     

                    I just tested a 9,000+ record tab separate file that I created with Excel. All values for the first column were eight character, but I set the last one to 19 character. Monarch detected it properly and set the field width to 19, which was a bit surprising.

                     

                    And why doesn't Monarch just populate the data in between each tab into its own column?[/QUOTE]

                     

                    It really should. How does Excel behave opening the same file? Any unexpected gaps? I suspect that there will be if Monarch isn't displaying what you expect to see.

                      • Tab Delimited File with Null values
                        DD _

                        I just created a tab separated file in excel just like you did but made my 32K 8 characters wide. I then expanded record 16k to 14 characters. Monarch did not pick it up properly. Kruncher can you try doing the same and see it it works properly. If it does maybe it is something in my setup.

                          • Tab Delimited File with Null values
                            Grant Perkins

                            From your description I read it that you have one (or more) columns that present data on 2 rows but between the same tabs - is that correct? Not something I can recall seeing before.

                             

                            If you concatenate a dummy first row to the input file and make that define the maximum field widths you expect for the input file might that solve the problem? It's a technique I have employed before to standardise field sizes for models where input files may present differently each time they arrive.

                             

                            Also, using the file as a database input, can you you revise the model for the same effect - setting field sizes - and then save and re-use for the next iteration of the report? Remember if you are changing a field size that you need to adjust both the Data and the display sizes.

                             

                            You may already have tried this - in which case ignore the ideas - but I didn't see it mentioned above so thought it worth the suggestion.

                             

                             

                             

                            Grant

                              • Tab Delimited File with Null values
                                Scott Eshleman

                                Form your description I read it that you have one (or more) columns that present data on 2 rows but between the same tabs - is that correct/ Not something I can recall seeing before.

                                 

                                If you concatenate a dummy first row to the input file and make that define the maximum field widths you expect for the input file might that solve the problem? It's a technique I have employed before to standardise field sizes for models where input files may present differently each time they arrive.

                                 

                                Also, using the file as a database input, can you you revise the model for the same effect - setting field sizes - and then save and re-use for the next iteration of the report? Remember if you are changing a field size that you need to adjust both the Data and the display sizes.

                                 

                                You may already have tried this - in which case ignore the ideas - but I didn't see it mentioned above so thought it worth the suggestion.

                                 

                                 

                                 

                                Grant[/QUOTE]

                                 

                                I've used this method as well - even concatenating a row containing field names in some circumstances.

                                 

                                I was also wondering what version of Monarch you're using...

                        • Tab Delimited File with Null values
                          DD _

                          @ Data Kruncher - Excel opens the file fine.  Maybe I will try the same with excel and put the odd length record in the middle somewhere.