12 Replies Latest reply on Jan 5, 2011 9:45 AM by bookalaka_1

    Import from Excel problem

    bookalaka_1

      Title

      Import from Excel problem

      Post

      I have a log that I import from Excel daily and recently I decided to add a check box in FM "Updated", that I check after I have logged my changes etc.  My problem occurs when I import the next days log. my script is set to update update existing records, Add remaining, and don't import First field. What happens is my check boxes all move up by the number of new records added and I have played with all the settings doesn't seem to make a difference? Not sure why this would be happening do I need to script something in? The excel document of course does not have a check box field.

      Thanks for your guidance.

      Murray

        • 1. Re: Import from Excel problem
          philmodjunk

          When you add a field to your table, you must then re-map the fields to columns in your import field mapping dialog to align the correct fields with the correct columns in Excel. You can drag the field names up and down to map them to different columns than the default alignment that pops up in the dialog.

          • 2. Re: Import from Excel problem
            bookalaka_1

            I had already done that and as I posted the excel document will only be importing a blank field since there isnt any information. I'm not sure if this has to do with being a check box?

            • 3. Re: Import from Excel problem
              philmodjunk

              Then I'm afraid I don't know what you mean by this statement: "What happens is my check boxes all move up by the number of new records added"

              The fact that you've specified a check box format on the field should have no effect on how the data imports. There's something else not set up right here, but I can't quite picture what is going wrong for you.

              Did you also update which pair of fields serve as the "match fields" so you can correctly update the existing records?

              Do you have all your existing records in your found set when you import your data?

              • 4. Re: Import from Excel problem
                bookalaka_1

                Lets say line item box 8, 9 and 10  are checked then I  do my import of 3 new records and all other records don't need to be updated. What happens is  the checked boxes move up 3 rows to 7,6 & 5. ?

                • 5. Re: Import from Excel problem
                  philmodjunk

                  Update "existing" not update "matching"? this requires that the order of records in your current set match the order of the rows in your spreadsheet.

                  If you can, instead use update matching, you can set up a means to match a given row in your spread sheet to a given record in your table, with the check box field as a way to control which fields get updated.

                  • 6. Re: Import from Excel problem
                    bookalaka_1

                    No success can I send you the file?

                    Thanks for the help.

                    • 7. Re: Import from Excel problem
                      philmodjunk

                      What does "no success" mean? can you elaborate?

                      I'd need both files, your database file and the spreadsheet file to be able to evaluate the entire issue, so let's not go there unless we have to.

                      • 8. Re: Import from Excel problem
                        bookalaka_1

                        Well after many many hours I have resolved some of the issue but could use a hand to finish. The code from excel that I import is as follows Code_PB_pk  example "LSN-100-10-09"    I have a calculation field the code imports into  Substitute ( Code_PB_pk; ["-" ; "" ] ; [" " ; "" ] ) to remove the Hyphens and this is where the problem lies. In order for the match fields to work and my "check box update" to function I need to import in with the hyphens so I created another "ImportUpdateCode" for the match field. However, for the rest of my database to function with the imported codes I need without the hyphens. Can you recommend the best way to handle after the import? A script or another calculation or is there anoher way to handle this?

                        Thanks in advance

                        • 9. Re: Import from Excel problem
                          philmodjunk

                          Does your substitute function correctly remove the hyphens like you want? It should, if you want LSN1001009 as your result, unless the hyphens in the Excel cell aren't the exact same hyphens in your substitute function.

                          If you need this data both with and without the hyphens, I'd use two fields, one can store the data with the hyphens and the other without. Use the field with hyphens included for your import match, use the 2nd "no hyphens" field for your data base functions that require it. THhere are several different ways, using scripts, calculation fields or an auto-entered calculation that can be used to produce the two fields.

                          • 10. Re: Import from Excel problem
                            bookalaka_1

                            yes the substitite works perfect. How do I use two fields with the auto enter calc that seems to be the way to go?

                            • 11. Re: Import from Excel problem
                              philmodjunk

                              Auto enter only works if you enable auto-enter for all fields during the import, that may or may not work for you. It depends on whether you have other settings on the other fields in your table that should or should not be enabled during import.

                              If you don't enable auto-enter options during import, you can use Replace Field Contents in a script that uses the calculation option to load your second field immediately after import.

                              You can also just define a field of type calculation and then the value is computed automatically. This is the simplest but requires that the hyphenated text always appear in the first field and the second, calculation field is not directly editable, so there are trade-offs with this approach only you can evaluate.

                              With all of the above, your calculation expression for field 2 can be:  Substitute ( field 1; ["-" ; "" ] ; [" " ; "" ] ).

                              This leaves Field 1 with the unmodified data and field 2 shows it with spaces and hyphens removed.

                               

                              • 12. Re: Import from Excel problem
                                bookalaka_1

                                Cool Yes that worked feels great. Thank you!