[Solved] Importing addresses

Discuss the spreadsheet application

[Solved] Importing addresses

Postby omeganos » Tue Mar 12, 2013 11:22 am

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
Last edited by MrProgrammer on Sat Jul 04, 2020 3:48 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open office 3.4.0
Windows 7 Ultimate
omeganos
 
Posts: 4
Joined: Tue Mar 12, 2013 10:58 am

Re: importing addresses

Postby Villeroy » Tue Mar 12, 2013 2:01 pm

You want a line like this one ...
A897, Helmsdale, Highland KW8, UK
... turned into what?
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28854
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing addresses

Postby omeganos » Mon Mar 18, 2013 3:44 pm

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
Open office 3.4.0
Windows 7 Ultimate
omeganos
 
Posts: 4
Joined: Tue Mar 12, 2013 10:58 am

Re: Importing addresses

Postby Villeroy » Mon Mar 18, 2013 4:02 pm

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.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28854
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing addresses

Postby MrProgrammer » Wed Mar 20, 2013 3:13 am

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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3893
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests