9 Replies Latest reply on Aug 28, 2016 5:57 AM by beverly

    Trying to import a text file & one field won't import

    VictoriaHerring

      I have about 1000 records and 8 fields to import.  It's from an exported txt file [exported from Neofinder] and imports just fine into FMP - however one field won't import == Size.

       

      I have set Size field to be a number and it wouldn't import [comes in as a dash only] then thinking that the fact it has kb or mb behind the number in Neofinder, I set it to be a text field - and it still wouldn't import. 

       

      Any suggestions???

        • 1. Re: Trying to import a text file & one field won't import
          beverly

          I will typically import into a TEXT field and then have an auto-enter NUMBER field to take the numerical value of that text field.

           

          However, you say it will not import into a text field either. Can you show a small screen shot of the column (header included) and a few values?

           

          beverly

          1 of 1 people found this helpful
          • 2. Re: Trying to import a text file & one field won't import
            VictoriaHerring

            Thanks so much -- I think I may have found the culprit due to  your pointing me in the right direction = I looked at the FMP database and the Size column is empty = then I went to the text file which I imported and despite the fact I chose to export Size [and the data is in Neofinder] it didn't export Size at all -- so it's the fault of my initial export....I'll keep working with it...

             

            Your suggestion to import to text and then use an auto enter Number field = I hadn't known or thought of that before = I've never done that actually = so you import to text and then have a separate field that pulls in the text data and deletes the text info and keeps the number??? 

            • 3. Re: Trying to import a text file & one field won't import
              VictoriaHerring

              AH! and many thanks -- It's my fault [obviously] - I found the data in Neofinder and exported it but only exported the first level - I selected 5 levels and that ended up including the Size information .  Thanks!@!!

               

              See the import of a test run =

              Screen Shot 2016-08-28 at 6.59.32 AM.png

              • 4. Re: Trying to import a text file & one field won't import
                beverly

                Yes!

                 

                myNumberAsText (TEXT) - import directly

                myNumberFromText (NUMBER) - Auto-enter, calculation: GetAsNumber(myNumberAsText)

                 

                these are the simplest form. You text may be a bit more complex and need another function to extract the number

                 

                GetAsNumber ( "123 5th St, Apt 4B" )

                (for example), returns the number:

                12354

                correct, but probably not what you wanted.

                HTH,

                beverly

                1 of 1 people found this helpful
                • 5. Re: Trying to import a text file & one field won't import
                  VictoriaHerring

                  Actually the text info imported is simple -- 2.3 kb  - a number, space and then kb or mb -

                   

                  I would think I'd want to have both kb and mb in a field so I can do a calculation that makes the kb and mb fields have the same type of data [ie 1000kb = 1mb etc]

                   

                  I'll run a test run and see what happens.  I imagine I can't just do it in the new database but add these fields and reimport?  Or since I do have the 2.3 kb as a text field already just create a new field with a calculation in it or something??  and run that??

                   

                  as you can tell, I'm a newbie = have used FMP but not constantly and still learning [and on ver. 11 as well!]

                   

                  again many thanks.

                  • 6. Re: Trying to import a text file & one field won't import
                    beverly

                    Thank you for the screen shot!

                    The GetAsNumber ( Size ) should come in correctly as it only has the number and " KB".

                     

                    Your "dates/times" are coming in as text?

                    "March 19, 2009 at 2:08:00 PM" (Created/Date Created)

                    (for example) would not directly become a timestamp type field (date and time) and may also need 'translation'.

                     

                    beverly

                    • 7. Re: Trying to import a text file & one field won't import
                      beverly

                      You can have another field that would auto-enter the KB or MB as well. You then successfully separate the number from the unit! These can be recombined in reports (a Merge Field, perhaps?)

                       

                      beverly

                      1 of 1 people found this helpful
                      • 8. Re: Trying to import a text file & one field won't import
                        VictoriaHerring

                        The dates/times are irrelevant to me = it just tells me when the Neofinder indexing was done = not really all that important for what I need = I only want to know the amount of [size of][ data so I can make sure of what I need to work with and not = and make sure that if there is a 0 amount [empty folder] that there is a valid folder that is the same but has data in it = I am pretty sure there always is but just being careful.

                         

                        thanks again

                        • 9. Re: Trying to import a text file & one field won't import
                          VictoriaHerring

                          The size data is in as a Text item = Then, I can create a new field that is a calculation field??  Based on that data -?

                           

                          so I would create a new field [number from text] and make it a number and then AutoEnter and calculate - but that's where I fall short....I am sure I'm not getting the function right...

                          Screen Shot 2016-08-28 at 7.24.23 AM.png

                          • 10. Re: Trying to import a text file & one field won't import
                            beverly

                            the new field is not a calculated field with number result, it is a NUMBER field with auto-enter as you have.

                            does it work for you? If you have already imported data, it will not "update". In that case use Replace() -carefully! to change the date for the new field with the same calculation you used in the auto-enter. You only need do this for old data already imported. the auto-enter will handle new imports.

                             

                            beverly