[Solved] Empty fields in ODB created from spreadsheet cells

[Britta Leuchner]

Hello and best regards from Berlin to the OOO Community.

This is my first topic. I would like to ask you for help on a strange behaviour by the regulary "create database from spreadsheet" procedure between OOo Calc and OOo Base.

(English Version of OOo 3.2.1 on Apple MAC OS X 10.5.8 PPC Dual 2.7 and Java Apple 1.5)

The spreadsheet file was an exported excel file (.xls) created from an outlook addresslist, including names, streets, zipcodes, and many more. The outlook file and the exported xls file came from a Vista PC. It opened in OOo Calc on my Mac without any trouble. In the spreadsheet everything looks good so far and there are properly 2 rows of the same kind of address data, for example street1, zipcode1, city1 ... and then the same with street2, zipcode2, and so on ... one the private address and one for the business address.

To use it for a serial envelope printing job, I have created a new database from the Calc spreadsheet without any trouble, but I found out that there were many zipcodes missing in the print. I took a look at the spreadsheet, but the zipcodes were there. Then I took a look back on the database and found out, that the database doesn't contain all the zipcodes of the row "zipcode1". The row is there, but most of the fields are empty.

So I tried to find the error and have started to check all the cell formatting. But it was exactly the same than the formatting of the other zipcode cells in the other row (zipcode2), but they show up correctly. Then I copied one cell from the zipcode2 row in the zipcode1 row in the spreadsheet and have created and have re-created and re-registered the database, to proof if there is any "ghost" formatting going on, which is different in the cells of zipcode2. And guess what? The copied value appears in the zipcode1 row of the database. Then I was going back to check if I have missed anything what differs between the two cells in the rows but I didn't find anything!?

The database is too big for me to retype all zipcodes. There MUST be a way to find the mysterious difference between the cell values of the 2 different zipcode rows. Does anyone had any similar experience with some cell values, which won't import into the database created from a spreadsheet? Does anyone know a solution? Even resetting and validating the cell formats and types in the spreadsheet didn't make anything. Please help me! The list should be printed today.

I was looking around, but I didn't find a similar discussion. Please ... Thanks for all ideas!

Best regards,
Britta

[Villeroy]

You must not mix numbers and text. ZIP codes should be of either type. I'd prefer text. Databases can not handle different types of data in one column.
=IF(ISNUMBER(A2;TEXT(A2);A2) converts A2 to text.
=VALUE(A2) converts to number (if possible).
Then import the calculated field.

Array formula {=MAX(LEN(A$2:A$9999))} checks the maximum length of an assumed text field, likewise with MIN
COUNT(A$2:A$9999) returns the count of numbers, if it equals ROWS(A$2:A$9999) then you have a column full of numbers
COUNTA(A$2:A$9999) does the same with any value and when COUNTA does not equal COUNT you have a mixed column. When it equals ROWS(A$2:A$9999) you have a column without empty cells.
COUNTIF(A$2:A$9999;A2)-1 returns zero if the value in A2 (text or number) is unique within A2:A9999. Therefore you have a unique column when the sum of this calculated field is zero.
ISNUMBER(MATCH(A2;X$2:X$9999;0)) tests if the value in A occurs in X, testing the referencial integrity for 2 tables.

[Britta Leuchner]

Just a quick addition, what may help somebody to guide me to the right point:

It seems that it has to do with a unexplainable Apostroph -> ' on the beginning of all the zipcodes which correctly appear in the created database from the sheet. All the zipcodes which won't be imported doesn't have these Apostroph in the spreadsheet. The Apostroph isn't visible in the sheet, it is only visible in the formula type-line on top of the sheet. Does anyone know what this 'Apostroph' about?

EDIT: @ Villeroy. Thanks for your fast reply! I typed the second post while you answered. Thanks for trying to help me. But if I understood correctly what you sad, it is nearly the same what I've tried at first? Because I have converted all cells to the same format (text) at first. It was one of my first ideas, but I did it via menu point -> cell format. But it has changed nothing on the behaviour ... Ok, maybe I missunderstand something about cell format from the menu and your point on it. I will try your way to look if it differs, maybe I will understand better afterwards.

[Villeroy]

The apostroph is very well explainable. Like a leading = marks an entry as a formula, the apostrophe marks an entry as text if it would be interpreted as number otherwise. It is not part of the value. With the apostrophe a zip code like 12345 will be treated a sequence of digits rather than number.

[Britta Leuchner]

Thanks Villeroy! Yes indeed, that's what I thought at first, but then I wondered why some have it and some not, from the same export file. Maybe there was an error in the outlook file? ... My stomach told me already to put the apostrophe in front of all the zipcodes , but then, at second, I thought there's maybe another reason why some have it and some not. Also about the formatting of cells via menu: there was a missunderstanding on my side depending on the english version I use over here in Germany. I thought it would convert the values but it was for view purposes of the cells only, I think. Because activating the "text only" option doesn't change anything on how the cells handled the zipcodes. Well ... you are very right, finally. I would like to thank you for your help and patience, Villeroy. OOo is an impressing software suite by the way. And this is a great place here.

I will find a way to trigger the apostrophe for all fields (makro?) by one click (for the future).

1000 Thanks again and Best regards and best wishes from Berlin, Germany
Britta

[r4zoli]

May be the Convert Text To Number (and date) extension can help you, to remove apostrophes.

[Villeroy]

You can read your Outlook addressbook directly into Base:
File>New>Database...
[X] Connect to existing database
Type: Outlook Addressbook (or similar)

[Britta Leuchner]

May be the Convert Text To Number (and date) extension can help you, to remove apostrophes.

You can read your Outlook addressbook directly into Base:
File>New>Database...
[X] Connect to existing database
Type: Outlook Addressbook (or similar)

ups ... sorry didn't recognize that there where some new posts. I would like to thank you thousand times for all of your help, Villeroy and r4zoli ... thanks a lot.

greetings from Berlin!
Britta