Author Topic: [SOLVED] How to overwrite date added when importing from Csv  (Read 14671 times)

0 Members and 1 Guest are viewing this topic.

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
 Hi again,
 I am creating a new database by CSV importing from an existing database.
 The date added that appear in the new entries are not the ones I can find in my CSV file, would you know how to make sure that date added field is taking the value found in the csv file?
 
 Also, I have been using the comma delimiter since the file I use is a csv, but there are commas in descriptions, actors, etc. so the import tool will take these as delimiters. Would you know how to get around that?
 I have changed the settings in XP to use the ";" instead of the comma, but I guess that's a bit far fetched no?
 Any better solution would help.

Thank you all.
« Last Edit: September 22, 2010, 12:46:09 am by rick.ca »

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #1 on: July 11, 2010, 06:52:09 pm »
Use replace=";<->," in your export template (I believe it's in the default CSV template) and then set ";" as the delimiter in the import configuration.

If you still have a problem with the date, I can only guess it might be because the exported data includes the date and time, while the import plugin can only handle dates. If this is the case, you would have to use Excel to change the data to dates only, and then import using the Excel import plugin.


Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #2 on: July 11, 2010, 07:13:11 pm »

 The problem with the dates occurs even when the dates did not have a time.
 It is basically pushed to the next field, so my File Path ends up being the old date added...

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #3 on: July 11, 2010, 07:18:23 pm »

 By the way, to export I am not using the csv export plugin because it does not export the movie ID, so i use the xml export.
 I then convert the xml into a csv file.
 I will try to include the string you gave me into the xml export.

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #4 on: July 11, 2010, 09:54:12 pm »
There is no "CSV export plugin." There's only the Plain text export plugin. Whatever is exported by it is determined by a user-configurable template. All you need to do is add the mid field to the list of fields exported. You may need/want to add other fields—the template provided is only an illustration. I don't know what problems you might be introducing by converting from XML to CSV. If there are no errors in your file, then I would assume the problem you're having with the date field is the result of the import plugin not being configured properly. The fields have to be mapped in exactly the same order as they occur in the CSV file (as determined by the specified delimiter).

If you still have problems, I would recommend importing the CSV file to Excel, saving it as an Excel file, and then using the Excel import plugin. That allows you to check and revise the data before importing, and the Excel import plugin seems less finicky than the CSV.

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #5 on: July 11, 2010, 10:07:39 pm »

 Yes I have since switched to using excel import and it's much better.
 
 I will have to modify the template later, I have also read the instructions you pointed me to, I had selected them before, just so you know that I searched the forum before posting!

 The problem here that I am having is that the images generated do not contain the Movie ID but another number that I cannot find anywhere in the database.
 
 

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #6 on: July 12, 2010, 01:07:09 am »
Quote
The problem here that I am having is that the images generated do not contain the Movie ID but another number that I cannot find anywhere in the database.

Maybe it's mid (the internal database index number), I don't know. If so, you can use the Excel file to create a batch file to rename the images... i.e., rename [Title]_*.jpg [ID].jpg.

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #7 on: July 19, 2010, 02:17:30 pm »
Hi Rick and all,

 I have now successfully created a new database file :) .
 The movies ID's are the same than in the original corrupted database.

 The list of posters that I had generated from the corrupted database contains the name of the movie and the Internal ID.
 This internal movie ID is NOT the same in the new database, even after re-indexing.
 So imgid is unable to add the posters to my new file.
 I need to rename the image files in order to get them into my new database.

 From Buah's post http://www.videodb.info/forum_en/index.php?topic=1734.0 it looks like he is using the ID (and not the internal ID), so I am unsure about what ID I  should use when I rename the image files. Would you know?

 My main question however is HOW do I rename the files, I am completely useless when it comes to batch files and scripting, can you help me with detailed information?
 I am attaching two text files. The first gives you the list of movies with ID's and Internal ID's. The second gives you a list of the image files generated from the folder they are in.
 There are sometimes images that belong to the same film.
 What is the best way of doing this?

I am so glad I am moving away from using a corrupted database file, but I need these posters so bad, I had invested too much time in finding them!


[attachment deleted by admin]

buah

  • Guest
Re: How to overwrite date added when importing from Csv
« Reply #8 on: July 19, 2010, 03:36:02 pm »
I can't remember if it's possible to assign more than one poster to a title this way.

I did use (non-internal) IDs for importing, but I was lucky, because they were exported from CATVids with those IDs.

I think you could try to use technic to sort movies by title, then to export them to excel, then to add a column with paths to images, then te reipmort such xls file to a new database.

It's necessary for you to configure PVD to save images outside database, and to define folders in PVD with images.

I would first try to assign a couple of images in PVD, then to export it to xls, to check out what is the syntax and proper path for images.

Sorry, but it's very hard for me to be mor clear in english about this.

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #9 on: July 19, 2010, 05:33:15 pm »

 I did not realize I could actually try to use "image path" to import the posters...
 Now my question is how can I export the image path from pvd (I need to export it from the original corrupted database) ?

 Thanks!

buah

  • Guest
Re: How to overwrite date added when importing from Csv
« Reply #10 on: July 19, 2010, 06:43:12 pm »

 I did not realize I could actually try to use "image path" to import the posters...


When you import such xls file, and you afterward define for PVD to save images in database, it should work.

Quote
Now my question is how can I export the image path from pvd (I need to export it from the original corrupted database)?

If you can do that it could be perfect. But if you can't, you could copy paste a list of posters in a column I described in a post above, and than paste path in every cell of a column (C:/.../Shared documents/PVD/Images/, for instance).

Concatenating two columns (the first is folder path, and the second is image name) is also considerable.

Sorry for my English.

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #11 on: July 19, 2010, 07:06:05 pm »
 Sounds good thanks Buah.
 This is not going to be simple as some of my movies do not have a poster file, and also the alphabetic order is not perfect...
 
 Also, what is the name of the "image path" in the import plugin? I can't find it in the list available.

buah

  • Guest
Re: How to overwrite date added when importing from Csv
« Reply #12 on: July 19, 2010, 08:34:55 pm »
Also, what is the name of the "image path" in the import plugin? I can't find it in the list available.

Honestly, I can't remember, and you have to discover it. That's why I told you to assign some posters with saving 'em outside of PVD option set. Then export only list of entries with posters, edit it in Excel, and you should find out the name of the column.

This is not going to be simple as some of my movies do not have a poster file, and also the alphabetic order is not perfect...
 
I would import then in two steps.

1. Export only entries with posters from the corrupted database - import to a new database (assigning path in xls, previously)
2. Export entries without posters from the corrupted database - import to a new database (without "path" column in xls)
« Last Edit: July 19, 2010, 08:38:44 pm by buah »

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #13 on: July 19, 2010, 09:06:15 pm »

 Yes the two steps are a good suggestion thanks.
 
 
Quote
Then export only list of entries with posters, edit it in Excel, and you should find out the name of the column

 I have tried that but no export seem to give me any image path, unless I have to build it with the export manual that Nostra has put together?
 Still, I cannot find any import field (in the configuration menu for the import plugins) that could even resemble an image path...
 
 

Offline rick.ca

  • Global Moderator
  • *****
  • Posts: 3241
  • "I'm willing to shoot you!"
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #14 on: July 19, 2010, 09:35:31 pm »
The list of posters that I had generated from the corrupted database contains the name of the movie and the Internal ID...

Forget about the "Internal ID"—it has nothing to do with this process and won't help you.

Quote
I have tried that but no export seem to give me any image path...

Neither will this. Poster paths cannot be exported or imported.

The only tool available for importing images is IMGbyID. It can only work if the image is named by ID, and the new database has the same ID assigned to the target movie. This is not the case illustrated by your two text files—the ID's do not match.

Hopefully, you still have a copy of the corrupt database—with the images saved in the database. You can then start over and...

1. Delete any existing external images.
2. Reindex movies.
3. Change the option to save images outside the database.
4. Export the data and re-import to create a new database (with the same ID's!)
5. Rename the images from [Title]_[ID].jpg to [ID].jpg.
6. Use IMGbyID to import the images.

Alternatively, use the new database and external images you have now, but rename the images using the current (i.e., correct ID)...

1. Reindex movies—to ensure they all have a unique ID.
2. Export a list of [ID] and [Title], and import this into Excel.
3. Import poster list.txt (assuming this is an accurate list of the externally saved images) into Excel; based on the "_" character, extract [Title] to a new column.
4. In the second sheet, use the vlookup function to lookup the title in the first sheet and get the ID.
5. In a new column, create a batch command to rename the image file (i.e., "rename [original name] [ID].jpg").
6. Copy the batch command column to a text file, rename that file with a .BAT extension and run it in the image directory.
7. Use IMGbyID to import the images.


Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #15 on: July 19, 2010, 09:54:20 pm »

 Hi Rick, thanks for your patience.
 Yes I knew the ID's did not match, but this is because the image files are saved with the INTERNAL ID and not the ID's.
 So the image files are like: terminator_Internal ID.jpg
 And ImgID actually works with these for my corrupted database.
 Unfortunately, and I have checked it, the new database does not assign the same Internal ID's to the movies. And I have no way to import the Internal ID's into the new database.

 Anyway I see what you want me to do, and I have done everything right up to the point where I have asked you how to rename my files after you suggested it.
 The ID's are exactly the same from my new and my old database.
 
 So the file I had posted for you to look at are showing the right posters files generated from the old database.
 
 Basically my question can be summarized as this:

 How can I change the name of thousands of files like these:
 movie titles_Internal ID.jpg
 to
 ID.jpg
 when possessing a list of ID's linked to the movies titles?

 The two lists I had posted are the list I would like to use to link up all.

 As you can see, it's not a matter of removing the "movie titles_" part of the image files, as the number displayed is not the ID.

 Thanks again



 

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #16 on: August 04, 2010, 09:14:25 am »
 Just wanted to report that I have now successfully transferred all 5800 movie entries from my previous corrupted database file to a new clean one.
 
 I imported all posters (VERY important step so that I don't loose hours/days of image hunting) using ImgID

 Many many thanks (for their patience and expertise) to all who helped me in this process: Buah, Nostra and Rick

 I am a happy man. ;D ;D ;D ;D ;D ;D

buah

  • Guest
Re: How to overwrite date added when importing from Csv
« Reply #17 on: August 04, 2010, 10:56:04 am »
Great news deazo! Reading your post, I can recall how happy I was when I finished importing my ~5500 movie entries several months ago, so I know how you feel this very day.

P.S. Curiously, today I have 5815 movies :D

Offline deazo

  • Older Power User
  • *****
  • Posts: 283
    • View Profile
Re: How to overwrite date added when importing from Csv
« Reply #18 on: August 04, 2010, 11:12:33 am »

 Yes a bit freaky actually, I am at 5809...
 How do you select them? I am trying to stick with what I like but I haven't seen all obviously, so I use a lot of reviews, critics, magazines, etc... The aim is to have a high quality collection. I think I am far from it, considering I still have "dear john" in it...
 

buah

  • Guest
Re: How to overwrite date added when importing from Csv
« Reply #19 on: August 04, 2010, 11:53:14 am »
How do you select them?

1. Well, mostly relying on IMDb's rating and reviews there. I developed a sense of how to distinguish an IMDb rating for, let's say The Dark Knight's 8,9 (btw, one of the most overrated movies that I've ever seen, IMHO) from Wild Strawberries' rating that is 8,3.  Worlds of film as an entertainment and as an art, don't interfere in my coordinate system, and I enjoy them both.

2. Upon the recommendations of people whose movie taste I believe in, and they believe in mine: "Hey, I saw a movie you'll like it a lot but, you know, it's not my pace (Revolutionary Road, for instance)", or "I saw this movie, I freaked out, and you will too, for sure (The Night of the Hunter)"

P.S. The TV series "Dear John" was one of my favorites, btw :P

 

anything