5 Replies Latest reply on May 31, 2011 12:36 PM by LaRetta_1

    Importing text file and modifying text

    DennisPrickett

      Title

      Importing text file and modifying text

      Post

      I am creating a database of my wife's large collection of children's books. Instead of entering each of the 800-ish books manually, I am using Dragon Dictation on my iPad (very cool) to capture the title, author, illustrator, genre and curriculum category for each book into separate fields.  I now have a text file with all of the information for each book on a single line, as follows:

      Alexander who used to be rich last Sunday author Judith Viorst illustrator Ray Cruz genre fiction

      Invasion of the giant bugs author AJ Wood illustrator Wayne Anderson genre fiction category insects

      It's a spoon not a shovel author Carolyn Buehner illustrator Mark Beuhner

      The words "author", "illustrator", "genre", and "category" need to be lost from each line.  Each line does not necessarily have the illustrator, genre, or category.  I also need to correct the capitalization in the title (each word capitalized except 'a', 'and', 'of', 'the', 'to ect unless they start the title), or maybe it would be easier to make the title all caps.

      How to script this?

      Dennis

        • 1. Re: Importing text file and modifying text
          philmodjunk

          I would think that you would want the Title, Author and Illustrator data in separate fields, not all in the same field.

          What exactly do you mean by "lost from each line"? It seems odd to me that you would just want to delete the words Author, Illustrator and Genre from the above fields, so I assume that I am misunderstanding what you want here.

          As to capitalization, you can create a script that loops through the title one word at a time and uses the proper function to capitalize each word except for a, and, of, the, to, etc...

          • 2. Re: Importing text file and modifying text
            DennisPrickett

            In rereading my post I realize that I didn't make great sense.

            I will have separate fields for title, author, illustrator, genre and category.  I anticipate having a import field to enter the data from the text file.  I need a script to parse the date from the import field and insert the correct elements into each of the title, author, illustrator, genre, and category fields.  That's why I don't need the words Author, Illustrator, etc from the original line - those words are just tags to identify what follows on the line.

            • 3. Re: Importing text file and modifying text
              philmodjunk

              Your original post doesnt' give any date examples. If the date is entered as text in the same field, then your script would need additional steps from what I am recommending here. Also, if you could configure Dragon Dictation to insert a tab between Title, author, etc., you might be able to import the data directly into the fields without needing a script.

              To update a set of newly imported records...
              Loop
                 Set Variable [$Author ; Value: Position ( Table::TextField ; "Author" ; 1 ; 1 ) ]
                 Set Variable [$Illustrator; Value: Position ( Table::TextField ; "Illustrator" ; 1 ; 1 ) ]
                 Set Variable [$Genre; Value: Position ( Table::TextField ; "Genre" ; 1 ; 1 ) ]
                 Set Variable [$Len ; value: Length ( Table::TextField ) ]
                 Set Field [Table::TitleField ;
                        Trim ( Case ( $Author ; Left ( Table::TextField ; $Author - 1 ) ;
                                            $Illustrator ; Left ( Table::TextField ; $Illustrator- 1 ) ; 
                                            $Genre ; Left ( Table::TextField ; $Genre- 1 ) ; Table::TextField ) ) ]
                 If [ $Author ]
                    Set Field [Table::AuthorField ; Trim ( Middle ( Table::TextField ; $Author + 7 ; $Len -
                         Case ( $Illustrator ; $Illustrator - 1 ;
                                    $Genre ; $Genre - 1 ;
                                    0 ) ) ) ]
                 End If
                 If [$Illustrator ]
                     Set Field [Table::IllustratorField ; Trim ( Middle ( Table::TextField ; $Illustrator + 12 ; $Len - $Genre ) ) ]
                 End If
                 If [$Genre ]
                     Set Field [Table::GenreField ; Trim ( Right ( Table::TextField ; $Len - $Genre - 6 ) ) ]
                 End IF
                 Go To Record/Request/Page [next ; exit after last]
              End Loop

              Note: This script has not been tested on a sample file. Please test it carefully on a copy of your file to make sure that it really works as these calculations are reall easy to get "off by one" and thus might contain an extra character or be missing an extra character.

              • 4. Re: Importing text file and modifying text
                DennisPrickett

                Thanks Phil.  I'll have to look at this for a while to understand what it's doing.  Oh, I didn't mean 'date', I meant 'data'.  Sometimes I'm not wearing my glasses when I type and miss the typos.

                • 5. Re: Importing text file and modifying text
                  LaRetta_1

                  I might approach this a bit differently.  The data string should be imported (or reside) in a regular text field (we will call it 'text' for this example).  Then I would create calculations for Title, Author, Illustrator, Genre and Category (all result of text).  You can then view the results and make sure everything is fine. Then you can do one of two things: 

                  If you might want to import more books in the future using this method, leave the text field there and leave the new fields as calculations.  They will be indexable so they will be the same as standard fields (except you won't be able to modify them)
                  - or -
                  If you need to eventually modify the data in these new fields or if you are sure you won't be importing using this technique in the future, then go to each new field and change them to standard text.

                  Since the field data is stored, when you change them to regular text, the 'calculations' will appear as regular data.  You can then delete the original string.  Here are the calculations for each of your fields (and you should be able to copy/paste the calculations directly - just be sure your original string is named text:

                  Title
                  Let ( [
                  start = 1 ;
                  end = Case (
                  PatternCount ( text ; "Author" ) ; Position ( text ; "Author" ; 1 ; 1 )  - 1  ;
                  PatternCount ( text ; "Illustrator" ) ; Position ( text ; "Illustrator" ; 1 ; 1 ) - 1 ;
                  PatternCount ( text ; "Genre" ) ; Position ( text ; "Genre" ; 1 ; 1 ) - 1 ;
                  PatternCount ( text ; "Category" ) ; Position ( text ; "Category" ; 1 ; 1 ) - 1  ;
                  Length ( text ) + 1 )
                  ] ;
                  Middle ( text ; start ; end - start
                  ) // END MIDDLE
                  ) // END LET

                  Author
                  Case ( PatternCount ( text ; "Author" ) ;
                  Let ( [
                  start = Position ( text ; "Author " ; 1 ; 1 )  + Length ( "Auhtor " ) ;
                  end = Case (
                  PatternCount ( text ; "Illustrator" ) ; Position ( text ; "Illustrator" ; 1 ; 1 ) - 1 ;
                  PatternCount ( text ; "Genre" ) ; Position ( text ; "Genre" ; 1 ; 1 ) - 1 ;
                  PatternCount ( text ; "Category" ) ; Position ( text ; "Category" ; 1 ; 1 ) - 1  ;
                  Length ( text ) + 1 )
                  ] ;
                  Middle ( text ; start ; end - start
                  ) // END MIDDLE
                  ) // END LET
                  ) // END CASE

                  Illustrator
                  Case ( PatternCount ( text ; "Illustrator" ) ;
                  Let ( [
                  start = Position ( text ; "Illustrator " ; 1 ; 1 )  + Length ( "Illustrator " ) ;
                  end = Case (
                  PatternCount ( text ; "Genre" ) ; Position ( text ; "Genre" ; 1 ; 1 ) - 1 ;
                  PatternCount ( text ; "Category" ) ; Position ( text ; "Category" ; 1 ; 1 ) - 1  ;
                  Length ( text ) + 1 )
                  ] ;
                  Middle ( text ; start ; end - start
                  ) // END MIDDLE
                  ) // END LET
                  ) // END CASE

                  Genre
                  Case ( PatternCount ( text ; "Genre" ) ;
                  Let ( [
                  start = Position ( text ; "Genre " ; 1 ; 1 )  + Length ( "Genre " ) ;
                  end = Case (
                  PatternCount ( text ; "Category" ) ; Position ( text ; "Category" ; 1 ; 1 ) - 1  ;
                  Length ( text ) + 1 )
                  ] ;
                  Middle ( text ; start ; end - start
                  ) // END MIDDLE
                  ) // END LET
                  ) // END CASE

                  Category
                  Case ( PatternCount ( text ; "Category" ) ;
                  Let ( [
                  start = Position ( text ; "Category " ; 1 ; 1 )  + Length ( "Category " ) ;
                  end = Length ( text ) + 1
                  ] ;
                  Middle ( text ; start ; end - start
                  ) // END MIDDLE
                  ) // END LET
                  ) // END CASE