Importing from Excel

[rick.ca]

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

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.

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! 

[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.

[nostra]

The plugin must always be configure for all fields.
There is no way skip some fields, but maybe I'll add such a possibility in future.

Check your length cells in Excel. Probably Excel made them to have type "Time" and PVD gets wrong data when importing such cells. Change the type to "Text".

After your length cells are ok, just re-import the data and answer "Merge" when PVD asks you what to do with the duplicates.

[rick.ca]

The plugin must always be configure for all fields.

That is, all columns in the Excel file.

Change the type to "Text".

Nostra, does it matter whether the text is in the form "hh:mm:ss" or just "hh:mm" or "mmm"?

If the data in Excel is a number displayed as time, then make a new column using =LEFT(TEXT(TimeCell,"hh:mm:ss"),8). Select the new column, Copy it, and Paste-Special-Values it to another column (or on top of itself, if you like). This column will be text you can import (subject to what nostra may say about format).

[rick.ca]

...and what title you'd like to select for each column.

I think you understand, but this is not literally correct. The plugin will ignore whatever titles you put on the first row of the worksheet. The plugin configuration assigns valid PVD fields to each column. All that matters is that those fields are listed in the same order at the columns appear.

Maybe this should be added to a future help file...

Perhaps. In the meantime, consider this forum to be a help file that TALKS BACK TO YOU. 

SO HOW COULD I GO ABOUT TRYING TO IMPORT ONLY THE LENGTH FIELD FROM MY EXCEL NOW?

Aside from the matter of this data needing to be converted to text, the simplest import of additional information to movies already existing in the database would require two columns—one to identify the record (Title) and one for the additional information (e.g., Length). Remember what I said about this before...

Each time you use the import plugin, include the movie title and year so the correct record in PVD can be identified. It won't matter what other records are in the database at the time, or what order they are in. If you change any titles in PVD, you might have to take care to make the same changes in your Excel file so any future imports will work correctly. I suppose it would be a good idea to refrain from changing titles in PVD until you have finished all your importing.

there is no need to save the data you have enetered into the database, it saves automatically

This is not "autosave." This is how "true" database software works—all changes are immediately written to disk. This is not something that can be "turned off." I suppose some kind of "undo" feature is feasible, but it may not be what you expect. Many changes are going to be so pervasive, it would not be possible to maintain reliable undo information for very long. (When you add a movie, about 20 people—maybe many more—will be added. For each of those, a complete filmography may be included. So adding one movie can easily involve 500 records being added to/updated in the database. You're right, frequent backup is probably the only answer. Perhaps some kind of backup reminder can be added.

[ruffa]

thanks for the replies,
You said if i want to import data to my existing database i need at least 2 columns to import, eg. title, and length, the title so the database can ID where the stick the times.
But i'm still confused, Nostra said i'd need to keep the plugin configured for all fields when importing to an existing database, i understand this and then import it with Merge ticked, then it will fill in the blanks, but i don't understand what you are saying about working only with 2 columns at a time, how would you do this?
If i only selected to import title and length then surley the length would end up in my 'B' column which is my description column.

About the 'Length' columns, i have simply typed in the runtime like this: 60mins, 54mins etc. i have not done it like this 00:01:03:00hrs
I've always manually typed out my database or copied+pasted, i've never altered any settings on excel, does this make any difference?
Thanks for the piece of code you typed which would redo the times but i'm not familiar with how to actually use that sum you typed. But like i said the runtime is typed like 60mins.

[rick.ca]

Nostra said i'd need to keep the plugin configured for all fields...

This really isn't that complicated. What nostra was trying to say, and what I attempted to clarify with my previous post, was you must configure the plugin exactly according to the columns that exist in your Excel file. It doesn't matter whether you have two columns or twenty. The plugin has no way of determining which field the data in each column should be put unless you tell it—for each and every column.

If i only selected to import title and length then surley the length would end up in my 'B' column which is my description column.

Maybe the reason you're not getting this is that you think your Excel file is "fixed." When I suggest you can import just the length field, I'm assuming you will make a copy of your Excel file, delete all the columns except title, year and length, and then save this new file for importing to PVD. If you insist on importing from your original Excel database each time, you will have to be very careful that your plugin configuration is correct. Furthermore—as you have discovered—if anything goes wrong, it's much more difficult to figure out what happened.

About the 'Length' columns, i have simply typed in the runtime like this: 60mins, 54mins etc.

I'm not sure exactly what format the plugin is expecting (i.e., "hh:mm" or "mmm"). I'd be surprised if it understood "60mins" (and, it seems you have already found it does not). I see PVD exports length in minutes ("mmm"), so let's assume the import plugin will recognize that. Since you're not comfortable using formulas, try this: Select your length column, Edit-Replace "mins" with nothing (i.e., delete all occurrences of "mins"), and format the column as text. This is confusing, but... what you want your minutes to look like numbers, but to actually be text.

[nostra]

PVD accepts length as hh:mm:ss or a number of minutes without text after or before it.

[rick.ca]

PVD accepts length as hh:mm:ss or a number of minutes without text after or before it.

Thanks. You saved me from more testing. 

...So, the most effective way to deal with this sort of situation is to convert the form of the data using Excel. In this case, =TEXT(LEFT(Length,LEN(Length)-4),"0") would do the trick (replace Length with a reference to the cell with length in the form "123mins").

"hh:mm:ss" is another matter. If the data is already in this form, in text, the plugin will recognize it. If it is not text, but a time-value the cell is formatted to display like this, it will need to be converted to text with =TEXT(Length,"hh:mm:ss").

Once the new column with the formulae is displaying the data in the correct format, use Copy-Paste Special-Values to convert it to text. Now that it no longer relies on the original data, that column may be deleted.

[ruffa]

if it accepts 'number of mins' without text after it then i will try the method of using the replace the word mins with nothing, then try to import again i guess.thanks for the suggestions

[buah]

Assuming that this is proper topic for my question, that I'm not familiar with databases, and hoping that my question won't arise at 4chan, I'm asking someone to help me:

Which value (formula, expression) I have to enter in a MS Excel, so when it is imported to a referent PVD cell it produces PVD's "Wish" field - checkbox to be checked? Is it possible any way?

[buah]

OK, I figured it out. For others who might have dilemma: value in Excel's cell - "1" for PVD's checkbox checked, and "0" for unchecked.