1 Reply Latest reply on Nov 28, 2018 9:42 AM by Patrick Van Der Hyde

    DateTime and currency formats in schema.ini

    Nigel Henry

      Hi -

      New to Tableau.  This is a sample of my text file:

      Date 10/20/18                       REVENUE                          Page     1

       

       

      --------------------------------------------------------------------------------

      TS-Transaction  T       TS-Total         TS-Tot  TS-Transaction  TS-Transaction

      Date            N     After Disc   Contract Cst  Time            Nbr

      --------------------------------------------------------------------------------

       

       

       

       

      04/28/10        1          30.00          20.00  08:21:24AM      00000001

      04/28/10        1            .00            .00  08:37:04AM      00000002

      04/28/10        1           8.00           6.00  08:39:07AM      00000003

      04/28/10        1            .00            .00  08:42:55AM      00000004

      04/30/10        1           8.00-          6.00- 01:06:19PM      00000005

      04/30/10        1            .00            .00  01:22:37PM      00000006

      04/30/10        1           8.00           6.00  01:24:43PM      00000007

      10/19/18        2       4,240.56          30.07  05:59:35PM      01950567

            * GRAND TOTAL

                         63,254,141.73  58,090,695.85

       

       

      *** END OF REPORT ***

       

       

      I have tried several versions of specifying the time and currency formats. Here's one that I think should work (and doesn't).

       

      [revenue.txt]

      Format=FixedLength

      ColNameHeader=False

      CurrencySymbol=" "

      CurrencyNegFormat=" 1-"

      DateTimeFormat=hh:nn:ssAM

      Col1= TransDate Date MM/DD/YY

      Col2= StoreNum Integer Width 1

      Col3= Revenue Currency Width 16

      Col4= Cost_POS Currency Width 15

      Col5= TransTime DateTime Width 10

      Col6= TransNum Char Width 8

       

      I then limit rows by the "StoreNum" field being valid, which does work.

      Please help.

        • 1. Re: DateTime and currency formats in schema.ini
          Patrick Van Der Hyde

          Hello Nigel,

           

          This is fairly easy to do with Tableau Prep.  See the attached Prep flow file and the included .hyper file for Tableau to check out for your self.    A slight modification of this file should work for you for you noting that the filter is set on the N field currently and you may have more values than 1 and 2 for that field.

           

          I also added a full Datetime() field to the output.  You will need a creator license for full time use of Prep but I imagine you already have one based on the work you were doing here. 

           

          Let us know if you have any questions and I hope this works well for you. 

           

          Patrick