Apache OpenOffice Community Forum

Sorry if this is a simple question but having searched for hours I haven't found a solution that I understand. I am very new to open office calc

I have the following addresses in a csv:

3 Uplawmoor Road, Glasgow, East Renfrewshire G78 3BJ, UK
A897, Helmsdale, Highland KW8, UK
A83, Tarbert, Argyll and Bute PA29, UK
A83, Tarbert, Argyll and Bute PA29, UK
2 Killiechonate, Spean Bridge, Highland PH34 4EY, UK

and I would like to import using the the end column as a baseline as town,county,postcode, are always ordered from right to left allowing me to combine any other columns as the address.

Thanks for looking

omeganos

You want a line like this one ...
A897, Helmsdale, Highland KW8, UK
... turned into what?

I want to just extract city,state,country

number,street,city,state,country
house name,number,street,city,state,country
house name,street,city,state,country

but because some addresses contain more commas than others last columns are misaligned. Is there any way from import from csv so that the last fields are aligned rather than the first.
country,state,city,street,number
country,state,city,street,number,house name
country,state,city,street,House name

reversing the column order on import.

many thanks for looking

Put the file into a separate directory.
File>New>Database...
[X] Connect to existing database
Type: Text
Specify the directory where you stored the file, the delimiters etc.
[X] Register the database
Save the database.

Your text file appears in the tables section as if it were a database table. The database document does not store any data. It reflects the content of similar files stored in the specified directory.
Now build a query in design view.
Choose one table and double-click the fields you need.
Save the query under some name.
Store the database, close the database, forget the database.

In Calc you hit F4, pick the query icon from the left side and drag it into a sheet.
In Writer you can do the same or drag column headers from the right side into your document which creates mail merge fields.

Hi, and welcome to the forum.

omeganos wrote: Is there any way from import from csv so that the last fields are aligned rather than the first.

No, but you can manipulate the data to keep only the last three fields.

• Open the CSV file with Writer.
• Edit > Find & Replace, More options, select Regular expressions
• Search for ^.*,([^,]+,[^,]+,[^,]+)$, Replace with $1, Replace All.
• Deselect Regular expressions, Close.

This converts your sample data to:

• Glasgow,East Renfrewshire G78 3BJ,UK
  Helmsdale,Highland KW8,UK
  Tarbert,Argyll and Bute PA29,UK
  Tarbert,Argyll and Bute PA29,UK
  Spean Bridge,Highland PH34 4EY,UK

And you get city,state,country from all of these:

• number,street,city,state,country
  house name,number,street,city,state,country
  house name,street,city,state,country

Then:

• Edit > Select All
• Edit > Copy
• File > New > Spreadsheet
• Edit > Paste Special > Unformatted Text > Separated by > Comma

The search expression begins with ^ and ends with $. The portion of it in parentheses represents the last three fields. The replace expression begins with $ and ends with 1. You can read about regular expressions in Help > Index > regular expressions.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.