1 Reply Latest reply: May 15, 2014 9:57 AM by Data Kruncher RSS

    CSV report issue

    JCC _

      I have the following report below, which is a .csv file, and have not been able to table it with a model. I have tried to open as report and open as database with no luck. Any assistance is greatly appreciated. I am working with Monarch Pro V 7.00.

       

       ¦D a t e   C o l u m n , A l p h a   o n l y   C o l u m n , A l p h a   C o l u m n   A l l   C a p s , A l p h a n u m e r i c   c o l u m n , N u m e r i c   c o l u m n    

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , A L E X A N D E R   C I T Y , R e g i o n   1   -   A L E X A N D E R   C I T Y , 1 . 4 6                                                 

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , A T M O R E , R e g i o n   1   -   A T M O R E , 4 2 . 7 4                                                                               

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , A U B U R N , R e g i o n   1   -   A U B U R N , 6 . 2 4                                                                               

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , D O T H A N , R e g i o n   1   -   D O T H A N , 0 . 7 3                                                                               

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , M O B I L E , R e g i o n   1   -   M O B I L E , 1 2 . 7 7                                                                               

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , M O N T G O M E R Y , R e g i o n   1   -   M O N T G O M E R Y , 1 1 4 . 4 9                                                             

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , S E L M A , R e g i o n   1   -   S E L M A , 1 1 . 5 7                                                                               

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , T R O Y , R e g i o n   1   -   T R O Y , 2 7 . 4 3                                                                               

      2 0 0 7 - 0 6 - 1 5 , A l a b a m a , T U S K E G E E , R e g i o n   1   -   T U S K E G E E , 2 . 4 4                                                                         

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , C A S A   G R A N D E , R e g i o n   1   -   C A S A   G R A N D E , 9 . 5 2                                                             

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , C O C O N I N O , R e g i o n   1   -   C O C O N I N O , 2 1 7 . 7 8                                                                     

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , G L O B E , R e g i o n   1   -   G L O B E , 2 . 0 2                                                                               

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , P A Y S O N , R e g i o n   1   -   P A Y S O N , 0 . 8 3                                                                               

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , P H O E N I X , R e g i o n   1   -   P H O E N I X , 2 8 7 . 6 4                                                                         

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , P I M A   C O , R e g i o n   1   -   P I M A   C O , 2 . 0 4                                                                             

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , S I E R R A   V I S T A , R e g i o n   1   -   S I E R R A   V I S T A , 6 6 . 4 8                                                       

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , T U C S O N , R e g i o n   1   -   T U C S O N , 8 5 . 5 6                                                                               

      2 0 0 7 - 0 6 - 1 5 , A r i z o n a , Y U M A , R e g i o n   1   -   Y U M A , 5 . 6 2                                                                               

      2 0 0 7 - 0 6 - 1 5 , C a l i f o r n i a , B I S H O P , R e g i o n   1   -   B I S H O P , 0                                                                               

      2 0 0 7 - 0 6 - 1 5 , C a l i f o r n i a , E L   C E N T R O , R e g i o n   1   -   E L   C E N T R O , 2 . 4 4

        • CSV report issue
          Data Kruncher

          Hi JCC,

           

          Assuming that the embedded spaces are the problem, here's my solution.

           

          Trap the whole line as a single field (which I called LineText), based on a number in the first column.

           

          Now go to the table. It's time to slice and dice. Build the following calculated fields:

           

          CleanText - character 120:

          [font="courier"]Strip(LineText," ")  /font[/quote]TransactionDate - date:

          [font="courier"]ctod(lsplit(CleanText,5,",",1),"y/m/d")[/font][/quote]State - character 20:

          [font="courier"]lsplit(CleanText,5,",",2)  /font[/quote]City - character 20:

          [font="courier"]lsplit(CleanText,5,",",3)  /font[/quote]Region - character 21:

          [font="courier"]lsplit(CleanText,5,",",4)  /font[/quote]Rate - numeric 2 decimal:

          [font="courier"]val(lsplit(CleanText,5,",",5))  /font[/quote]Hide the original LineText and CleanText fields, and you should be good to go.

           

          Of course, the other option is to determine why the extra spaces are getting in there in the first place. But you may not have any control over that, thus the solution above.

           

          HTH,

          Data Kruncher