English > Support

Importing from Excel

<< < (2/3) > >>

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:

--- Quote ---The plugin must always be configure for all fields.
--- End quote ---

That is, all columns in the Excel file.


--- Quote ---Change the type to "Text".
--- End quote ---

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:

--- Quote ---...and what title you'd like to select for each column.
--- End quote ---

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.


--- Quote ---Maybe this should be added to a future help file...
--- End quote ---

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


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

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


--- Quote ---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.
--- End quote ---


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

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:

--- Quote ---Nostra said i'd need to keep the plugin configured for all fields...
--- End quote ---

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.


--- Quote ---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.
--- End quote ---

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.


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

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.

Navigation

[0] Message Index

[#] Next page

[*] Previous page