Storing Text/Number in dBase (*.dbf)

Discuss the spreadsheet application
Post Reply
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Storing Text/Number in dBase (*.dbf)

Post by Scott M. Sanders »

I wanna bring this up again as this is just not user-friendly whatsoever.

We have to convert Excel spreadsheets to DBFs a lot for use in Pitney Bowes SmartMailer (and no, using Base for basic file conversion would be a total pain), and anyway character fields containing just numbers get converted by Calc to number fields constantly and cause lots of trouble for us.

When I (re)format a cell as character, I want the cell's value to be (re)formatted too. Why else would I be doing that anyway? Same goes for a whole column.

And what must we do anyway? Either edit the DBF headers manually, Ns to Cs, or do the same with SmartMailer. Calc could at least make it a little easier.

Moderation(V.): Split from [Solved] Number Re-formatting
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Number Re-formatting

Post by acknak »

I sympathize with your frustration at using Calc in this way, but (as far as I can guess, having no crystal ball) nothing much is going happen to improve the situation. You either have to use something else, implement some workaround, or fix Calc yourself.

The problems with using Calc (and Writer) for file conversion are well known--they're been reported to the developers many times since the early days of OOo. Fixing the problems would require huge changes; I guess it just isn't a core goal for the software.

If you want to attach some example files and explain exactly what you need to do, maybe we can suggest some good workarounds.
AOO4/LO5 • Linux • Fedora 23
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Number Re-formatting

Post by Villeroy »

We have to convert Excel spreadsheets to DBFs a lot for use in Pitney Bowes SmartMailer (and no, using Base for basic file conversion would be a total pain),
This has nothing to do with the original csv-topic which was about import of text into spreadsheets. You complain about spreadsheet to dBase.

Csv import is really broken since it disregards double-quotes and you need to declare text columns explicitly.

Base can import and convert data between spreadsheets and any writable database by means of simple drag&drop operations.

Without using Base you can convert cell ranges between numbers and text across sheets and store each resulting sheet as DBF table.

Excel can't read your mind neither. It stores text as text and numbers as numbers when exporting to DBF.
And what must we do anyway? Either edit the DBF headers manually, Ns to Cs, or do the same with SmartMailer. Calc could at least make it a little easier.
Make it easier to edit a cell value :?: How?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Re: Number Re-formatting

Post by Scott M. Sanders »

Attached are two files. In the Excel file, I have the first column formatted as text. I then opened it in Calc and saved it as a DBF, and behold, Calc turned that column into numbers instead due to its content.

Anyway this topic is about "number reformatting," not CSV importing or DBF exporting, as Calc seems to not discriminate.

Base is not an option for us simply convert XLS(X) to DBF. Again not user-friendly. I mean using Base may be OK for me 'cause I love learning new things but not for most humans who do not.

My co-workers have been using Excel for years and were pissed that MS dropped DBF support from 2007 but initially relieved that Calc supported it, but now they are just getting frustrated that they tell Calc that this column or cells should be text, and Calc outputs it/them as numbers in DBFs instead. Then you suggest we use Base instead, akin to switching from Excel to Access...?

The whole "user-friendly" thing is that different "users" do different things in different ways...
Attachments
number re-formatting 2009-10-14.zip
converted to DBF with Calc (in zip file) -- first column formatted as numbers
(8.89 KiB) Downloaded 180 times
number re-formatting 2009-10-14.xlsx
original Excel document -- first column formatted as text
(31.2 KiB) Downloaded 208 times
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Number Re-formatting

Post by Villeroy »

It is not Calc which supports DBF well. It is Base.
Your problem is about changing the data.
The original issue in this thread was about reformatting.
There is no need to discuss on file formats unless this difference is clear.
Text formatting does not change any of your values. The first column contains numbers, so there is no issue in Excel nor Calc. It is your understanding of spreadsheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Re: Number Re-formatting

Post by Scott M. Sanders »

Not really...

DBF column headers (first row) define the formatting for that entire column. The individual "spreadsheet cell values" or whatever mean nothing once exported to DBF.

Base is ridiculously complicated compared to Calc and Excel. Let's just throw that out there for honesty's sake.

Calc and Excel (seem to) let you define column formatting in addition to individual cell formatting.

If you define a column of numbers in Excel 2003 and earlier as text (character) and save to DBF, that column's formatting remains as text.
Not so with Calc. That therein lies our frustration.

DBF, XLS, databases, spreadsheets -- they are just different data files, manipulated with different programs. It's how those files are formatted, how those programs work, how people use those files and programs, and for what those files and programs are used that ultimately mean anything to anyone.

Just make it easy and work please. ;)
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Number Re-formatting

Post by Villeroy »

No formatting changes the type of value in Excel. Number format "@" has a completely different meaning.

Open your file in any spreadsheet application and put the following formula in some unused cell:
=COUNT($A$2:$A$285)

This will give the literal formula string "=COUNT($A$2:$A$285)" since you formatted everything as text.
Reset the number format of the formula cell [Ctrl+Shift+Space in Calc]
This will not modify the cell's text value since no formatting (no border, no colour, no number format) will ever modify your data.
Do some dummy editing (hit F2 and space in Calc).
Now the string will be evaluated as formula. Number format "@" supresses evaluation of new input without changing any existing value.
The return value is 284 since COUNT counts numbers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Re: [Solved] Number Re-formatting

Post by Scott M. Sanders »

Villeroy wrote:No formatting changes the type of value in Excel. Number format "@" has a completely different meaning...
OK, I don't wanna start a spreadsheets vs. databases war on my company's time clock, but I think herein lies my point...

Nearly all of our client's mailing lists come to us as Excel spreadsheets -- not databases -- which up to 2007 we would just open in Excel and save as DBFs to process in SmartMailer.

Enter Calc. Questions?

I for one can totally agree that we can and should be using database software like Base heretofore, but I doubt anyone else I work with will.
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Storing Text/Number in dBase (*.dbf)

Post by Villeroy »

If you need an application constantly guessing what you mean rather than what you do, then you should definitively stay with Excel.
I split this topic from the unrelated csv/number format topic.
 Edit: Excel 2000 behaves like any other dbf-capable application. Text is saved as text and number as number, regardless of formatting. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Re: Storing Text/Number in dBase (*.dbf)

Post by Scott M. Sanders »

Anyway another issue with Calc inadvertently doing some guessing of its own is that it's adding a default .00 to number fields on export to DBF. (That's an extra three bytes per field.)

So now my DBFs have columns of character data that just happen to be represented by only numbers of various widths that now all have .00s on them. Great.
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
mriosv
Volunteer
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Storing Text/Number in dBase (*.dbf)

Post by mriosv »

As I can see, calc use the first data cell to get the data type to save in DBF format. (if not defined in the column title).
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Re: Storing Text/Number in dBase (*.dbf)

Post by Scott M. Sanders »

No, because if Calc did, I would have say ZIP Codes as characters in my DBFs, not as numbers, because "Zip" is the header field of that column.

That is pretty much how I would expect a spreadsheet to behave though.
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Storing Text/Number in dBase (*.dbf)

Post by Villeroy »

Give up. You can not use this software. Write your own one.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Scott M. Sanders
Posts: 35
Joined: Mon Dec 22, 2008 6:41 pm
Location: Ashland, Ohio, USA

Re: Storing Text/Number in dBase (*.dbf)

Post by Scott M. Sanders »

Um, Microsoft already did; they just removed DBF support. :P
LibO 3.5, OOo 3.4 beta 1, White Label 3.3; XP Pro SP3, Vista Home Premium SP2, 7 Pro SP1 64-bit; Ubuntu 11.10, Fedora 11 to 16, CentOS 6.2
mriosv
Volunteer
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Storing Text/Number in dBase (*.dbf)

Post by mriosv »

Are you tried?.
Your first zip code not is a number. (45385-3198).
I refer to first data row (second row) not title row.
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
Post Reply