-1

I am a noob trying to get my head around Libre Office and sql. I have some experience with Python and databases in general however not a strong programmer. I'm trying to automate the creation of tables from .dbf address point files as I have 29 council areas that update property info regularly.

I am using the following sql command 

CREATE TABLE "tbl_AddPts_Burnie"
("GEOCODE_ID,N,10,0" varchar(255),
"EASTING,N,19,8" int,
"NORTHING,N,19,8" int,
...
"LIST_GUID,C,200" varchar(255))
SELECT *
INTO "tbl_AddPts_Burnie"
FROM "Macintosh HD/Users/Axel/Documents/Work/list_address_points_burnie.dbf"

It is creating the table however I get the following error:

Table not found in statement [SELECT *
INTO "tbl_AddPts_Burnie"
FROM "Macintosh HD/Users/Axel/Documents/LarkCreese/list_address_points_burnie.dbf"]

I figure the file location is no good but am not sure how to define the location properly in MacOSX.

Any ideas? Anyone?

Axel

Kepler
  • 21
  • 3
Axel Clark
  • 1
  • LibreOffice SQL cannot see anything outside the database. You'll need to use a programming language like Python to connect to the .dbf file, get out the information you need (potentially by running the SELECT statement), then connect the program to LibreOffice and construct SQL to create and add data to your table. You might also be able to connect a new LibreOffice file directly to the .dbf file; it might be easier to write the Python to move data between LibreOffice files instead of from the .dbf to a LibreOffice. – Lyrl Jan 11 '16 at 15:16

1 Answers1

0

LibreOffice can use dBase files as a data source.

  1. File -> New -> Database
  2. Radio button Connect to an existing database, select dBase from the list. Then press Next.
  3. Browse to or enter the path where the files are located, which in your case is "Macintosh HD/Users/Axel/Documents/Work/". Then press Finish.
  4. Give the new file a name, for example "dBase_files.odb".

Now in Tables view, you should see the "list_address_points_burnie" table.

The file should have been registered as "dBase_files" during setup, which means that you can use this from Writer or Calc.

To automate moving this data into another database, write some Python or LO Basic code to do a query and then an insert. Something similar to: https://stackoverflow.com/a/34729988/5100564.

Community
  • 1
  • 1
Jim K
  • 12,824
  • 2
  • 22
  • 51