Can't get Excel import to work at all - any ideas?

[Roy22]

OK, this is probably some thing stupid, but I just can't get Excel import to work. 

I've read the help file guide, configured PVD via Preference\Plugins\MS Excel general import\ to read 'Path, Actors' and created an Excel sheet with data to add as Path then Actors.  I've tried with & without headings in Excel.  I've tried substituting ID for path.  Every time, I see a message down low saying ' Excel Importing records, please wait' but I get no dialogue re update/merge etc, and I find nothing has changed in PVD despite there being data to update.

Any ideas?

[Roy22]

OK, with much fiddling I've got is as far as the 'Update/Merge/Add....' dialogue appearing.  Whichever I choose, it goes away for some time (updating over a 1,000) or seconds if I cut it down to just one or two.  But nothing changes.  I've tried endlessly, and I know that the ID field is correct, all I'm adding is a simple Actor's name, but the record appears unchanged.  I did try downloading the latest MS Excel plugin from the download page.

Does this ever work for anyone?

[rick.ca]

I'm not sure what bearing it has on the problem, but the task sounds odd. Are you really importing one actor for each of 1,000 records?

If never tried matching records with [File Path], so I'm not sure that works. All I've ever used for matching is [Title] and [Year]. And I've used that to import [ID], which now has me wondering if [ID] is really used for matching (if it can also be added).

If you're being prompted for a field update method, it must be matching something. Is it matching the first item in the list? Are you selecting Update when prompted?

[Roy22]

It may seem like an odd application, but it's a real one.  Back before the days of PVD, I used to append the names of actors onto the end of filenames, in a systematic way.  I could then find films with a certain actor through a simple file browser wildcard.  Sometimes there's one name, sometimes a few separated by commas, a format PVD doesn't mind when I've manually pasted such data into the Actors field.

Now, I'd like to extract these appended actors names and update the bare imported movie names with them.  I've managed the Excel extraction bit OK, but after several frustrating hours yesterday, have come to the conclusion that PVD isn't working as described in the manual.

I'd have hoped that an exactly matching ID (name) was enough for a match, but I'm starting to think that it isn't, even if the manual says it is.

I'm not sure if PVD should be changed, or the manual.  Perhaps someone could try adding a dummy movie to their database (title only) then try running an Excel import with just 'ID' and 'Actor's columns, to replicate the fault?

[rick.ca]

Now, I'd like to extract these appended actors names and update the bare imported movie names with them.

What I find odd is these are apparently movies for which there is no external source of actors. If there is, you won't be able to add them without overwriting what you're importing now.

I'm not sure if PVD should be changed, or the manual.

Neither. It works as described. I imported Actors (a comma-delimited set of two or three for each movie) using ID for matching records. Then I imported the same data to Producer using Title for matching records. It worked flawlessly.

If you're using Excel 2007 or 2010, are you saving the file in XLS format?

[Roy22]

If there is, you won't be able to add them without overwriting what you're importing now.

I'm not looking to overwrite this Actor data in future, simply trying to automatically add what I've got now as a one-off.

Neither. It works as described.

Well I can promise you that I wouldn't be wasting my time (and yours) if it worked flawlessly for me.  I'm curious that you did your experiment with CSV data, when my post was about Excel problems and the current manual says:

The best tool for doing that is a spreadsheet. If you have Excel, use that and the Excel import plugin.

I did briefly try CSV, amid hours of variations, but I don't think I even saw the 'merge options' dialogue appear so reverted to Excel.  I stopped trying to use 'Path' early on and changed to ID followed by Actors as the only two columns in the Excel file, both with & without headers in row 1.

I'm using Excel 2003, and saving in the default .xls format in the absence of any other instructions in the manual.  It did cross my mind that Excel versions might matter, but it was one variable I didn't try.  Is it likely to matter?

[rick.ca]

I'm curious that you did your experiment with CSV data

I did not use a CSV file, although I'm sure that would have worked as well. I was only pointing out the names in my actor data were separated by commas—as you said yours are—to rule that out as a possible cause of your issue.

It did cross my mind that Excel versions might matter, but it was one variable I didn't try.  Is it likely to matter?

Not if you're using the 2003 XLS format.

Have you configured the Excel plugin, specifying the fields in exactly the same order they appear in the worksheet. All you need to do is include [ID] and [Actors], and make those the first two columns of your worksheet.

[Roy22]

All you need to do is include [ID] and [Actors], and make those the first two columns of your worksheet.

Yes, that's all I did in the end, and even for tests of just two or three Actors names to add, it didn't do.

[rick.ca]

If you post your test worksheet, I'll try it. You'll have to zip it, or change it's extension to TXT.

[Roy22]

rick.ca,

Thanks for the offer, but I owe you an apology, as somewhere in all this I think I got confused between ID (numeric?) and Title, and may have wasted quite some time in this confusion. 

The other issue which put me off the scent, is that there seems to be some practical limit when running the Excel import.  The original 1300 or so line Excel sheet would lead to a crash with a message from PVD about Continue/Abort etc, but would always end with nothing changed.  A bit of trial & error has lead to 600 records at a time being targeted successfully for update, so I can get it to work in a few goes with the import sheet cut-down into smaller ones.  I'm not sure exactly sure what the limit is, or if it derives from PVD itself or something related to my hardware/software setup, but there does appear to be one beyond which no import will work.

It might be worth your while finding out if there is one, and if so adding it to the manual, to save others a lot of time.

Cheers,

Roy22

[rick.ca]

The original 1300 or so line Excel sheet would lead to a crash with a message from PVD about Continue/Abort etc, but would always end with nothing changed.

Using version 1.0.2.2, I tested imports of 2,000, 1,000 and 100 records. They all completed without error, suggesting it's not the number of records that's causing the problem. I'm reminded the import is rather slow—about one record/second on my system. Is there any chance you were trying to do other things with the program when the error occurred? It may have something to do with the content or the of data being imported (I was importing only a flag). It would be helpful if you could reproduce the error when the program is running with the -debug switch, and then send the exception report. It don't believe the plugin has changed since 0.9.9 (assuming that's what you're using), so if the problem is with the plugin it likely persists.

[Data1001]

Roy -

Just chiming in that I used the Excel import plugin successfully this week, but was having troubles using it with v9.9.21, so I downloaded the latest beta version of PVD, and it worked great. (I can't vouch that the switch from the stable release to the preview release is what made the difference, however, since I did also make some changes to the Excel spreadsheet during that time, as well.)

The Excel document that I used has over 2700 rows.

I did make sure I had the columns organized a certain way, though. First column was ID (which I put the exact same thing I had in my 7th column, LOCATION). The second column was TITLE. Third column, YEAR; fourth column DESCRIPTION; fifth, COMMENTS; sixth, RELEASE DATE; and seventh column, LOCATION (which, in this case, since I was importing records of my videotapes, was the number I assigned to each particular VHS tape -- i.e., 823-01 or ST-A-01). The "type" format of all columns except DATE was formatted as Text. All columns had a header row.

There was a lot of babysitting while I was importing, but despite the time it took, it was a pretty easy method of getting over 2,700 titles into the database, relatively speaking.

[Roy22]

Is there any chance you were trying to do other things with the program when the error occurred?

Only in terms of the PC video processing other stuff, which I expected to slow PVD down, though not crash it.  Or do you mean something else with PVD, as I'm not aware you can get it to do more than one thing at once?

Just chiming in that I used the Excel import plugin successfully this week, but was having troubles using it with v9.9.21, so I downloaded the latest beta version of PVD, and it worked great.

I am using 9.9.21.  By 'having troubles using it with v9.9.21' do you mean something similar to my experiences?  It may be that something has been knowingly or unknowingly fixed in the beta release.

I'll try and get the crash data you requested.

[Data1001]

Just chiming in that I used the Excel import plugin successfully this week, but was having troubles using it with v9.9.21, so I downloaded the latest beta version of PVD, and it worked great.

I am using 9.9.21.  By 'having troubles using it with v9.9.21' do you mean something similar to my experiences?  It may be that something has been knowingly or unknowingly fixed in the beta release.

Honestly, I was having so many issues with other things this week (mainly trying to use both the stable release and the beta at the same time and corrupting my configuration file -- but that's another story), I don't completely recall the issue with importing from Excel, but as my memory serves me, when I tried the import initially, it just hung there and didn't do anything.

I would suggest you install the beta version and see if that makes any difference for you. Then, if it does, you can decide whether you want to start using the beta release exclusively (which is what I'm doing). Just make sure to back up your database first -- you won't be able to open the database in the stable version of PVD once it's been opened in the beta (preview) release of the application. And also when you run the beta, make sure you do what I should have done in the first place, and run it with the "-portable" switch (without the quotes) -- that will keep your configuration files separate between the two versions.

[Roy22]

This may have been covered elsewhere, but is the beta version anywhere near become officially released?  I usually like to let others get the nasty surprises first. 

[rick.ca]

Or do you mean something else with PVD, as I'm not aware you can get it to do more than one thing at once?

With PVD. Incompatible operations are disabled and their commands greyed-out, but other things can be done. I have no reason to believe it would make any difference, I just wondered what else might be going on at the time of the error.

It may be that something has been knowingly or unknowingly fixed in the beta release.

Possible, but unlikely. But consider switching to the beta anyway. It has some minor glitches and things yet to be done, but it's stable. As for safely running both versions, see Portable Mode, and bear in mind not using it will overwrite and destroy the 0.9.9 versions of your database and configuration file if they are saved in the default locations. Make backups of both of them, and copy both of them to the 1.0.2.2 installation folder—where they will be used by the beta version running in portable mode. You may, of course, do the opposite and run 0.9.9 in portable mode.

[Data1001]

This may have been covered elsewhere, but is the beta version anywhere near become officially released?  I usually like to let others get the nasty surprises first. 

I hear ya. 

But if you poll the long-time users of PVD, I'd bet you'd find that a lot of them are using the beta version. From looking at his response to you in this thread, Rick appears to be using it -- and he is one of the most knowledgeable users of the program. The beta has been in release (and regularly updated for quite a while now), and so has probably gotten many kinks worked out. This week I've probably spent 30 hours using it -- so far it hasn't eaten my database nor exploded my computer. 

[rick.ca]

Rick appears to be using it -- and he is one of the most knowledgeable users of the program.

That's right, but I'm the one he wants to have the nasty surprises.

I haven't had any. I think it's stable.