Missing data converted to zero in OpenOffice

Discuss the database features

Missing data converted to zero in OpenOffice

Postby Hedditch245 » Wed Mar 13, 2019 6:57 am

I am using Quantum GIS (QGIS) to map demographic & economic data, for example, from the US Census (factfinder2.census.gov). I store and manipulate the datasets in Excel; Census data downloads as csv files. QGIS uses dbf files to store data, which I open with OpenOffice. While I can open dbf files in Excel, QGIS doesn't like what Excel does to the file vivavideomaker. The easiest way I have found to do this is to copy/paste the data from Excel into the dbf file open in OpenOffice.

The problem is that any missing data, blank cells in Excel, must stay "missing," but when I copy/paste the data from Excel into the OpenOffice dbf file, then close and reopen the dbf file, OpenOffice has converted all of my blank cells to zeros. I can't find a way to keep missing data as blank cells (or some other designation of missingness, like NA in R).

For example, if I have QGIS mapping a change in poverty status for the counties in a state, say, between -10% to +10%, I need to be able to distinguish a zero, no change, from 'missing data.' Right now all of my 'missing data' is showing up as 'no change' so I can't tell which is which, even by looking at the original dbf file since the missing data shows up as zeros.
Last edited by Hedditch245 on Thu Mar 14, 2019 10:00 am, edited 1 time in total.
NeoOffice 2.2.3 with MacOS 10.4
Hedditch245
 
Posts: 1
Joined: Wed Mar 13, 2019 6:52 am

Re: Missing data converted to zero in Openoffice

Postby Villeroy » Wed Mar 13, 2019 1:34 pm

Don't edit dBase with spreadsheet programs.
Put the file(s) in a dedicated directory. dBase is a database in a directory.
menu:File>New>Database...
[X] Connect to existing database
Type: dBase
Specify the directory and encoding.
[X] Register the database
Save the database document.
----------------------------------
Now all your dBase files in the dBase directory appear as database tables.
You can copy one table icon, select the other table icon and paste.
A wizard pops up where you choose to append data from the source table to the target table.
The dialog's next step allows you to map the source and target columns to each other. If the tables have the same structure, just hit the [Create] button.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26714
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Missing data converted to zero in Openoffice

Postby jrkrideau » Wed Mar 13, 2019 1:59 pm

@ Hedditch245
Why are you using Excel?

Is there some reason that you cannot load the .csv file into a database and proceed as Villeroy suggests?

The factfinder2.census.gov site seems to suggest that it downloads .csv files in a db useable format.

Is there a lot of editing that must be done before it is usable in a db?
If so, what needs to be done?

@Villeroy
My impression is that the OP is preprocessing the raw data in Excel before creating the data base.

Why xls -> ods -> db is not obvious but it may be due to some limitations on NeoOffice?
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3684
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Missing data converted to zero in Openoffice

Postby Villeroy » Wed Mar 13, 2019 2:47 pm

How to copy a cell range into a database:

1) Copy cell range into clipboard.
2a) Select the table icon in the Base window
2b) Alternatively, hit F4 in Calc, browse to your database, select the [Tables] icon in the left pane of the data source window.
3) Paste. Make sure that the right table name is given, "append data" is selected, "includes column labels" if the copied range included one row of labels and check that you paste the source columns to the right target columns.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26714
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests