Author Topic: 3928 won't import .txt to MySQL  (Read 4698 times)

0 Members and 1 Guest are viewing this topic.

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
3928 won't import .txt to MySQL
« on: 2013-06-27 12:51:53 »
Create empty MySQL database. Export from .tpbd8 with external thumbnails.

Import file to MySQL gives the error message "<5114> D:\Pictures\ThumbsTest.txt: Format error in file."

This happens for multiple databases, including one with only 6 thumbnails from one folder in.

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
3928 won't import .txt to MySQL
« Reply #1 on: 2013-06-27 16:45:13 »
Could it be that the exported file had any multi-line annotations in it? I have a vague recollection that those would cause multiple lines to be exported also, breaking the one-record-per-line format.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
3928 won't import .txt to MySQL
« Reply #2 on: 2013-06-27 17:06:17 »
Have just checked and all annotations are null.

I don't know what the format should be, but there is something that strikes me as odd.

All of the fields have 3 sets of quotes, with the exception of metric1, metric2, md5 and thumbnail which only have 1.

This is the beginning of the file, up to the end of the first thumbnail (I have truncated the metrics and thumbnail fields so they fit on the page):-

idVol="""1"""   
vtype="""3"""   
serialno="""2900577186"""   
maxcomplen="""255"""   
vchar="""65470719"""   
filesystem="""NTFS"""   
label="""Data"""   
netname="""\\AMD-3GHZ\d$"""   
alias=""""""   
idPath="""1"""   
idVol="""1"""   
pathtype="""1"""   
name=""""""   
idPath="""2"""   
idVol="""1"""   
pathtype="""1"""   
name="""Users\Public\Pictures\Deb Train Meal"""   
idThumb="""1"""   
idPath="""1"""   
idFiletype="""0"""   
file_time_iso="""2007-11-07T08:00:40.000"""   
thumbnail_time_iso="""2013-06-27T19:40:58.956"""   
taken_time_iso=""""""   
filelength="""5686"""   
checksum="""4263651906"""   
width="""96"""   
height="""48"""   
horiz_res="""2834"""   
vert_res="""2834"""   
colortype="""2"""   
colordepth="""8"""   
gamma="""0"""   
thumbnail_width="""96"""   
thumbnail_height="""48"""   
thumbnail_type="""518"""   
thumbnail_size="""311"""   
idUser="""1"""   
idHost="""1"""   
name="""DSCF4924.JPG"""   
rating="""0"""   
xmp=""""""   
metric1="00000000000000000000000000000"   
metric2="00000000000000000000000000000"   
md5="06fba95313f26e300917c6cea4480890"   
thumbnail="ffd8ffe000104a46494600010100"   
annotation=""""""

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
3928 won't import .txt to MySQL
« Reply #3 on: 2013-06-28 04:38:01 »
Further investigation has revealed that the export format has changed for TP9 to that in my previous post.

TP8 used the same format as TP7, a basic csv layout with column headers in the first line and then one line per thumbnail.

I have run some tests and TP9 can't import a TP8 .txt file either. TP8 won't import the TP9 .txt, but as the format seems to have changed I am not surprised.

mschnell

  • Full Member
  • ***
  • Posts: 98
    • View Profile
3928 won't import .txt to MySQL
« Reply #4 on: 2013-06-29 01:10:47 »
To me it seems that eporting / importing the database content does not need any specifics of the TP program itself.

Can you try to use native import / exports scripts by mySQL ?

-Michael

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
3928 won't import .txt to MySQL
« Reply #5 on: 2013-06-29 04:07:52 »
In this instance I do need TP to do the conversion.

I want to convert a .tpdb8 with external thumbnail files to MySQL, and I need TP to generate the thumbnail fields. I have no idea how to do this with SQL scripts.

mschnell

  • Full Member
  • ***
  • Posts: 98
    • View Profile
3928 won't import .txt to MySQL
« Reply #6 on: 2013-07-07 04:09:12 »
I don't understand.

The Thumbnail fields are in the old database to be converted, So I think they just stay - as the conversion is an in-place process, just generating filling the new fields with appropriate information (and perhaps purging legacy information).

If you - fro whatever reason - need to re-thumbnail such a huge count of images, it is no wonder that it takes a really long time.

-Michael

mschnell

  • Full Member
  • ***
  • Posts: 98
    • View Profile
3928 won't import .txt to MySQL
« Reply #7 on: 2013-07-07 04:34:32 »
If you create a database without thumbnail pictures (and somehow done in a way that the TP software sees that it is not meant for external Thu7mbnail pictures) it is very likely that loading this database in TP and rescan all directories will create new thumbnail pictures within the database and leave all additional information untouched.

-Michael

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
3928 won't import .txt to MySQL
« Reply #8 on: 2013-07-07 05:06:19 »
I have a database with external thumbnails, which need to be processed into the new MySQL database. This is why I can't just use an SQL script to do the conversion.

However it doesn't matter what format (external or internal thumbnails) the database is in, the import process is broken (as Laura has confirmed), and doesn't even get started.

I realise that the process is going to be slow (it took 7 hours to create the export file), but that isn't the problem.