English > Support

Importing from Excel

(1/3) > >>

rick.ca:
Time to start a new topic—continued from Where do i begin??? Help File???


--- Quote ---I don't think I've seen this behaviour before, but maybe I never tried importing something with blanks in it. Can you run a test with a small sample (e.g., three records—two "good" ones, with a "problem" one in between) to isolate the problem? Maybe it's a bug. And maybe you'll discover a workaround is just to put something (like a "-") in otherwise blank fields.
--- End quote ---

I tried testing the Excel import plugin—to see if I could replicate ruffa's problem and to answer some of my own questions about how it works. This is what I have learned...

It seems the plugin is not dependent on headings in the Excel file. As long as the configuration has the correct sequence of columns specified, it will work even if there is no heading row. On the other hand, it does recognize the first row is headings, if that's what they are. So I'm not sure what the implications are of excluding it. My preference is to include headings to help keep track of what I'm doing. Attached is a screenshot illustrating how I view my Excel file while entering the configuration. The Excel headings are not necessary for the plugin, but I do need to list the fields in the configuration in exactly the same order as the columns in Excel.

When importing additional information to existing records, the attached confirmation dialog appears. It may seem straightforward, but can be a little confusing when one appreciates the import works on a field level, not just for each record as a whole. This is what each option does:

Update adds source (Excel) field if destination record exists (as determined by matching ID, Title/Year or URL); otherwise does nothing.

Merge adds source (Excel) field if destination field (PVD) is null; otherwise leaves destination data intact.

Add adds all the source fields to a new duplicate record; the existing record is unaffected.

Do not add skips the record; continues the import with the next record.

Overwrite replaces the destination field with the source field; if the source field is null, the destination field is left unchanged.

Cancel terminates the import.

This dialog will appear for each record already in the database unless "do not show again is checked"—then the response is applied to all records imported.

It's easy to assume all records will be "updated" when choosing Overwrite. It can be important to recognize blank fields in import records will not cause existing data to be "deleted." If this is what is intended, blank cells in Excel should be changed to "null" or "delete me," which can be deleted in PDV after importing (if available for the field, using multiple movie editor).

Now, back to ruffa's problem: I was unable to replicate the problem with any combination of blank cells or special characters (like common field delimiters) in the data. Once the import was properly configured, it worked without error both initially, and when used again to add or change data. I only found this kind of error (data in wrong field for some records, not others) when changing columns the Excel file and not making the same change in the plugin configuration (or vice versa). For the reasons already mentioned, the corruption resulting from this can be difficult to interpret because some records are affected and others are not (e.g., a record is not updated because the import field is null, or the destination is not). Furthermore, for the same reasons, if the configuration is then fixed, running the import again will not fix the corruption unless all the affected fields are included, none of them are null, and the overwrite option is used. In most cases, it would be easier to restore from backup or start a new database than to "undo" an import that has run amuck.

[attachment deleted by admin]

nostra:
Great research, Rick :) and very helpful for other users! Thanks.

rick.ca:
It may take me years, but eventually I'll reverse-engineer this masterpiece of yours!  ;D

ruffa:
thanks for the help rick, i'm going to have another go using only a small number of rows in my database this time just to see how it goes, i'm unsure as to why the producer and year fields got mixed up but some where ok and some where not which was what made it strange, i will look into it some more and see if it works next time i try.
I'll let you know how i get on, and thanks again for the help.

ruffa:
Ok, so i just tried importing from excel again, this time using only 27 rows of data,
Problems i found.....

The 'Length' category was not imported at all, even though i asked it to while configuring the excel plugin.


This time the data does appear in the correct fields but the 'length' column is missing completely,

IF I'M INCORRECT IN WHAT I SAY BELOW PLEASE CAN SOMEONE TELL ME

I also now realize that the spelling of the column titles does not matter, and i see that regardless of the title of your colums in excel, the excel plugin configuration function is just used to select the column titles in order 1-2-3... of your new database. I previously thought that the plugin configuration was asking for words to recognise as titles in your excel database but this is not the case, the plugin configuration is just a way of selecting how many columns to import and what title you'd like to select for each column.


Maybe this should be added to a future help file, explaining that in the excel plugin configuration, the order in which you select what fields to import corresponds to the Column position,
for example:
If in the configuration i selected to import Title, Description, and Year in this order, then this would import my excel Column A to 'TITLE', B to 'Description' and C to 'Year' etc.

BUT WHAT I NOW DON'T UNDERSTAND IS, IT THE ABOVE IS CORRECT, THEN IF I WANTED TO IMPORT LETS SAY MY 'LENGTH' COLUMN AT A LATER DATE TO MY PVD. SO I'D CONFIGURE THE PLUGIN TO ONLY IMPORT THE LENGTH, BUT SURELY IF THE ABOVE WAS CORRECT THIS WOULD THEN IMPORT NOT THE LENGTH BUT COLUMN 'A' OF MY EXCEL DATABASE INTO THE PVD.

SO HOW COULD I GO ABOUT TRYING TO IMPORT ONLY THE LENGTH FIELD FROM MY EXCEL NOW?
(i just tried to import only the LENGTH field but nothing happened, probably because it see's the first column full of titles i guess)


i noticed that there is no need to save the data you have enetered into the database, it saves automatically i guess, this is a good thing in some ways but what do you do if you make a big mistake to your database then it autosaves! is there an undo or back button, i guess u just need a backup to hand which u backup regularly, it would be nice to see a save button with the option to turn off+on autosave function.

Navigation

[0] Message Index

[#] Next page