7 Replies Latest reply on Nov 29, 2011 9:05 AM by bookalaka_1

    Strange iMPORT behavior

    bookalaka_1

      Title

      Strange iMPORT behavior

      Post

      I have imported a file from Excel that I need to parse out the codes some have a " in front which causes problems of its own.

      Code imported = BRD3601009 Broadley Pinot Noir Claudia's 12x750 .  I have created an "AcccountCalc Field" Left (AccountCode_Imported ; 10)

      the result of course "BRD3601009". my problem starts when I import the Account Calc into another Filmaker File only 8  characters of the AccountCalc import? I get BRD36010? if I go back and change to 12  "BRD3601009 B" the resulting import gives me the correct "BRD3601009" whats up with this?  Ultimately I only need the code broken out  of which some come in with qoute marks.

      " YRK5001007 York Creek MXB, Zin Field Blnd 12x750"

      Thanks for helping me clarify this import.

      Bookalaka

       

       

       

        • 1. Re: Strange iMPORT behavior
          LaRetta_1

          That is strange indeed.  It might be tied to your other issue where some have a single quote at the beginning.  It sounds like it might have been a csv that went wrong or maybe it contains hidden or invalid characters.  Some things to try:

          1) Create a calculation in your main file with Length ( LeftWords ( AccountCalc ; 1 )  ).  What does it say?

          2) Check the field definition Options and Storage.  At the bottom, what is the default language?

          3) Copy/paste the string into a text editor and reveal all codes and characters.

          4) It is like there might be half spaces ...  Try wrapping your calculation with TrimAll ( your calc ; 0 ; 0 ).  There may be half spaces.  I have never worked with a document with half spaces (I'm true Roman, LOL) so I am not sure how FileMaker counts them.  If you wrap with TrimAll() then it should remove any half spaces if they exist.

          • 2. Re: Strange iMPORT behavior
            bookalaka_1

            OK I found part of my problem I had a hidden import script bringing in the voo doo. The length from your test revealed length at Ten and English language.

            I am still stumped in eliminating the " quotes. I have tried  TrimAll ( AccountCode_Imported; 0 ; 0 ) also TrimAll ( AccountCode_Imported; 1 ; 2 ) as well as filtering.

            Filter ( AccountCode_Imported ; 1234567890 )

            this is the example that I have not been able to trim " YRK5001007 York Creek MXB, Zin Field Blnd 12x750"

            • 3. Re: Strange iMPORT behavior
              philmodjunk

              Trim ( Substitute ( Yourtextfield ; "\"" ; "" ) )

              Should elminate all plain quotes from your text. If you only want to remove this from the first and last characters or have smart quotes, you'll need to use a different expression inside the Substitute function.

              • 4. Re: Strange iMPORT behavior
                LaRetta_1

                TrimAll() will not eliminate the quotes.  TrimAll() was suggested to remove half spaces which might have thrown the length off for your export.  Your Filter example would also drop the three alpha characters on the front.

                To remove the quotes, you need to use:  Substitute ( AccountCode_Imported ; "\"" ; "" )

                ... but the last example you provided has a space after the first quote.  Since the data is not necessarily clean, you can easily grab only the account code by using:   LeftWords ( AccountCode_Imported ; 1 ) 

                This will drop all word-break characters from either side (so it will remove the beginning quote and the space). This will work for your account code but you still have the rest of the string to parse so the second quote will still need to be removed.  What sequence you handle it depends upon what you are trying to accomplish overall.

                • 5. Re: Strange iMPORT behavior
                  bookalaka_1

                   LeftWords ( AccountCode_Imported ; 1 )  does give me part of the information although in retrospect ideally I would like to take the "LOV" and move it to another field.  I receive the information either as; ***LOV*** - CLR3301101 - Calera Pinot Noir Mills - 6x750 or Example 2; . - AJL3401006 - Ajello Furat - 12x750

                  I created a script using the substitute function to replace the ***LOV***  and realized its important informtation I can use.

                  How can I extract reather than just remove?

                   

                   

                  • 6. Re: Strange iMPORT behavior
                    LaRetta_1

                    "***LOV*** - CLR3301101 "

                    I am sorry but I do not understand by this example.  Does the text actually say ***LOV*** or does it vary?  Where in the string would this exist - only immediately before the account code?  Is there additional text before this string?  If you can clarify further, we will help you parse it.  :-)

                    • 7. Re: Strange iMPORT behavior
                      bookalaka_1

                      This is exactly how it comes in the string in front of the item code. LOV is an indicater for "Last of Vintage"

                       

                      . - AJL3101010 - Ajello Nero d'Avola - 12x750
                      . - AJL3401006 - Ajello Furat - 12x750
                      ***LOV*** - ALH1501399 - ALTENHOFEN RIESLING BEERENAUSL - 12x375
                      . - APR10042NV - HITORIMUSUME DAIGINJYO SHIZUKU - 6x720
                      . - APR20035NV - Ichinokura Taru Junmai-Maruaka - 12x500
                      . - APR40035NV - Meibo Yowanotsuki Ginjo - 12x500
                      . - APR41047NV - BISHONEN GINJO (6X1.8L) - 6x1.8
                      . - APR42040NV - Sawanoi Ginjo - 12x720
                      . - APR43042NV - Shiratake Ginjo Josen - 6x720
                      . - APR43047NV - Shiratake Ginjo Josen (6x1.8L) - 6x1.8