Change Field Type In Base From An Embedded Spreadsheet

[jparton]

I’ve got a fairly large list (over 10,000 rows) in a calc file that I’ve embedded over into base in an attempt to make it a little easier for the average user out here to find and edit records for our cemetery. One of the fields, and thus columns, I have is going to be for a picture of the headstone.

I can’t change the field type in base, I’m assuming because it’s embedding that information from the calc file. I also don’t think there’s a way to tell calc that what I’m going to be putting in those cells is an image so it needs to format appropriately.

Is there a way I can get this to work without going through all the hassle of rebuilding everything?

Thanks very much for the help.

[RobertG]

If you are using Calc as datasource the datasource is only readable. You couldn’t change the type in Base.

If it is shown as a link to the image it could be you see the image in a form with an image control.

Which type is shown for the field?

[jparton]

That’s what I was afraid of.

Base tells me it came through as a Text [VARCHAR].

If I can’t change it in Base, can I change it in Calc to work with that?

[Villeroy]

This is because spreadsheet data tend to be inconsistent. A spreadsheet is not a database, not even close. It is a cheap and error prone plastic toy.
=COUNT(A$2:A$10000)
=COUNTA(A$2:A$10000)
should give you a hint if your data in A2:A10000 are numbers, text or a mixture. In most cases it is a mixture of types.
=MAX(LEN(A$2:A$10000) entered as an array formula [Ctrl+Shift+Enter] tells you the maximum lenght of text.
MIN(A$2:A$10000) and MAX(A$2:A$10000) tell you about the range of values in a numeric column.

[jparton]

Ok. But how do you really feel?

I already know what you’re telling me, I need to know if there’s a way to make what I’m trying to do work without having to start over. If I have to start over, fine, I will. But I’d rather not if I can avoid it.

[Villeroy]

@jparton

I’ve got a fairly large list (over 10,000 rows) in a calc file that I’ve embedded over into base in an attempt to make it a little easier for the average user out here to find and edit records for our cemetery.

Continue working with the spreadsheet until you got your data tidy. No relational database accepts inconsistent data which is the number one feature of a database.

[Villeroy]

@jparton

One of the fields, and thus columns, I have is going to be for a picture of the headstone.

Store them in your spreadsheet as hyperlinks to picture files.
Having some IMG_0999.jpeg in a dedicated picture directory, you can store the file name IMG_0999.jpeg in a cell A2 and calculate the full path like this: =HYPERLINK(“file:///C:/path/to/pictures/”&A2)
Or just store the number 999 in A2 and do this: =HYPERLINK(“file:///C:/path/to/pictures/IMG_”&TEXT(A2;“0000”)&".jpeg") which concatenates the path with the IMG_ prefix and the .jpeg suffix with the 4-digit format of 999.
Later on, this will work with any kind of database.

[jparton]

Minus all your editorialization, this may end up actually being helpful. We’ll see.

[Villeroy]

editorialize
Definitions
from The American Heritage® Dictionary of the English Language, 5th Edition.
intransitive verb To express an opinion in or as if in an editorial.
intransitive verb To present an opinion in the guise of an objective report.

This is not a matter of opinion. As a matter of fact, a database does not allow inconsitent data. Contrary to the topic “Change Field Type In Base From An Embedded Spreadsheet”, your spreadsheet is not embedded in the database document. The database document is connected to the spreadsheet. Your database document weighs only a few kilobytes because it does not contain any data at all. Base treats the list in the connected spreadsheet as if it were a database table. Any sheet columns with mixed data types are treated as literal text with no numeric value. Such a connection to a spreadsheet is not a true database. It is a quick and easy solution for mail merge and label printing avoiding a true database setup.

[jparton]

You’ve spent more time &(&^ on spreadsheets than you have actually being helpful. You don’t know what I’m doing, you don’t know why I’m doing it, you don’t know the method I’m using for it. You don’t need to know any of that, but you’ve assumed a lot here.

And yes, it is an embedded document. It says so right there when you open the @#$#@ thing.

Also, your solution didn’t work. Did it exactly as you said and nothing happened. If you don’t know how to do it, that’s fine. It’s ok. I’ll keep looking around for someone that does know what I can do about this… or they’ll tell me right out if I just need to start over from the beginning. That’s not a big deal.

[Villeroy]

So what exactly do you read from that status bar? “HSQLDB Embedded”, right? Created by copying sheet data into a database document created newly from scratch, right? How can we know that? Everything looked as if you connected a sheet to your database like hundreds of users did before.
OK, you have a HSQL database embedded in the Base document with one(?) table having text columns where you expect numbers and dates and Base does not allow editing. Unfortunately, that’s a perfectly normal result after going the copy&paste route.

1. Continue working with your spreadsheet for now.
2. Open the database and call Tools>SQL…

DELETE FROM "Table";
SHUTDOWN COMPACT

Replace “Table” with the double-quoted actual name of your read-only table and push [Execute].
Upload the now empty database structure to this forum.

[joshua4]

@jparton Let’s clean up the language with a quick edit. Fight clever, not loud.

[jparton]

Just remove the @%#$# thing. If I could find a button to delete the whole thread I would. The question I asked hasn’t been answers, there’s no solution given and the @&^& trying to help isn’t doing anything but confusing the issue even more.

[Ratslinger]

@jparton

there’s no solution given

Don’t think there is an easy solution. Do not see copy/paste method viable when it comes to images. Also the database you are going to use is not mentioned (unless I missed that). HSQLDB embedded is out of the question for what you have stated. It will crash & burn with not many images let alone 10,000+.

Possible method to transfer is for a macro to be written but not clear if the images are already in the Calc file or not.

[keme1]

The main issue with what you are trying to do is that images in a spreadsheet do not constitute cell content. They are “anchored” to the cell, but not directly available to further processing as data. When you register your spreadsheet as a database, this propagates to the database platform. Only cell content is available as data elements. Attached objects are not.

There should be a way to access images by way of macro, but that is beyond my competence. To get images correctly into your database with oher data, you can unzip your ods file and by some external means tie data in content.xml to the images in the images folder.

I do not have a specific procedure for you, but if you are not helped by the info above, I may look into it later (unless someone else knows a better way).