Sponsored Content
Top Forums Programming MYSQL merge csv data with exisiting table Post 302997257 by barrydocks on Wednesday 10th of May 2017 10:05:23 AM
Old 05-10-2017
MYSQL merge csv data with exisiting table

I have a MYSQL table with demographic data. The addresses contained in the table were entered manually before the advent of online postcode lookup software and, hence, there are a lot of errors when using automated online mailing checking software. I plan to export the data as csv file for a 3rd party to do a bulk correction and then reimport the data. The problem is that the database will continue to be used and the demographic table also includes other data (such as dates and times, etc) than might change in the meantime, for example each row will have the following fields:
Code:
ID, Surname, firstname, dob, street, address2, town, county, postcode, tel, email, dateseen, nextdate

Obviously the address fields for some records will change in the csv file and the date fields may change in the original database table. So how do I go about importing the corrected data fields while leaving the unchanged fields that same? Would it be sensible to export only the address fields?
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help Inserting data in mysql table

Cant understand the error #!/bin/bash temp="" A="" D=$(date +"%Y-%m-%d") H=$(date +"%R") temp=$(wget -q -O - website | grep -o "Temperature:]**" | grep \-E -o "+") mysql -D "weather_wise" -e "INSERT INTO weather (Date, Hour, Degrees) VALUES ($D,$H, $temp)"; my data types for... (11 Replies)
Discussion started by: vadharah
11 Replies

2. Shell Programming and Scripting

select data from oracle table and save the output as csv file

Hi I need to execute a select statement in a solaris environment with oracle database. The select statement returns number of rows of data. I need the data to be inserted into a CSV file with proper format. For that we normally use "You have to select all your columns as one big string,... (2 Replies)
Discussion started by: rdhanek
2 Replies

3. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

4. Shell Programming and Scripting

how to print out data from mysql table in perl script

I'm having trouble with this code. if i do .\read.pl -u user it prints out 2010-12-20 12:00:00 host1 <cmd>a 2010-12-20 12:00:01 host1 <cmd> <execute> 2010-12-20 12:00:02 host1 <cmd>b 2010-12-20 12:00:03 host1 <cmd>c however, if i enter .\read.pl -h host1 it should... (3 Replies)
Discussion started by: kpddong
3 Replies

5. UNIX and Linux Applications

mysql table disappear

I have set a mysql file to excute everyday morning to generate a html file displayng 2 tables from the database. Sometime they cannot be shown, and it shows the tables are not existed. I have not drop any table, and those 2 tables are not used by any other excution. Anybody know what is happening?... (0 Replies)
Discussion started by: c203040
0 Replies

6. Shell Programming and Scripting

Merge Two Tables with duplicates in first table

Hi.. File 1: 1 aa rep 1 dd rep 1 kk rep 2 bb sad 2 ss sad 3 ee dam File 2 1 apple fruit 2 mango tree 3 lilly flower output: 1 aaple fruit aa,dd,kk rep (7 Replies)
Discussion started by: empyrean
7 Replies

7. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

8. Linux

Parsing - export html table data as .csv file?

Hi all, Is there any out there have a brilliant idea on how to export html table data as .csv or write to txt file with separated comma and also get the filename of link from every table and put one line per rows each table. Please see the attached html and PNG of what it looks like. ... (7 Replies)
Discussion started by: lxdorney
7 Replies

9. UNIX for Beginners Questions & Answers

Merge cells in all rows of a HTML table dynamically.

Hello All, I have visited many pages in Unix.com and could find out one solution for merging the HTML cells in the 1st row. (Unable to post the complete URL as I should not as per website rules). But, however I try, I couldn't achieve this merging to happen for all other rows of HTML... (17 Replies)
Discussion started by: Mounika
17 Replies

10. Shell Programming and Scripting

Send Data to MySQL Table Columns

I have two scripts, each script reads an individual data file and copies specific lines of data and sends to MySQL table. Only difference is, each script sends data to a separate column on the same DB. I want to use one script to populate DB table and have data look horizontal, with no overlapping.... (3 Replies)
Discussion started by: SysAdminRialto
3 Replies
Geo::Postcode::Location(3pm)				User Contributed Perl Documentation			      Geo::Postcode::Location(3pm)

NAME
Geo::Postcode::Location - helper class for Geo::Postcode that handles grid reference lookups SYNOPSIS
$Geo::Postcode::Location::datafile = '/usr/local/lib/postcodes.db'; my ($x, $y) = Geo::Postcode->coordinates('EC1R 8BB'); DESCRIPTION
Geo::Postcode::Location holds the gridref-lookup functions of Geo::Postcode. It is separated here to minimise the footprint of the main module and to facilitate subclassing. It doesn't really have a useful direct interface, since it requires an object of Geo::Postcode (or a subclass) and is most easily reached through that object, but it does have a couple of configuration variables and there is method documentation here for anyone interested in subclassing it or changing the data source. GRIDREF DATA
There are at least three ways to supply your own gridref data. o replace the data file If you can get your data into a SQLite file, all you have to do is set the either "Geo::Postcode::Location::datafile" or $ENV{POSTCODE_DATA} to the full path to your data file: $Geo::Postcode::Location::datafile = '/home/site/data/postcodes.db'; # or PerlSetEnv POSTCODE_DATA /home/site/data/postcodes.db I've included (in ./useful) an idiot script that I use to turn .csv data into a SQLite file suitable for use with this module. o replace the database handle The query that we use to retrieve location information is very simple, and should work with any DBI database handle. If your application already makes available a suitable database handle, or you would like to create one externally and make sure it is reused, it should just work: $Geo::Postcode::Location::dbh = $my_dbh; $Geo::Postcode::Location::tablename = 'postcodedata'; my ($x, $y) = Geo::Postcode->coordinates('EC1Y 8PQ'); If running under mod_perl, you probably don't want to share the handle like that. You can achieve the same thing with instance methods and avoid side-effects, but you have to make the calls at the right time: my $postcode = Geo::Postcode->new('EC1Y 8PQ'); $postcode->location->dbh( $my_dbh ); $postcode->location->tablename( 'postcodedata' ); my ($x, $y) = $postcode->coordinates; o override the lookup mechanism in subclass The data-retrieval process is divided up to make this as simple as possible: see the method descriptions below for details. You should be able to replace the data source by overriding "dbh" or redo the whole lookup by replacing "retrieve". $Geo::Postcode->location_class('My::Location'); package My::Location; use base qw(Geo::Postcode::Location); sub dbh { ... } METHODS
new () Constructs and returns a location object. Must be supplied with a postcode object of the class dictated by "postcode_class". postcode_class () Returns the full name of the postcode class we should be expecting. postcode () Returns the postcode object used to construct this object. retrieve () Retrieves location information for this postcode. This method is called during construction, retrieves all the necessary information in one go, so all the rest have to do is look up internal values. disconnect_after_use () If this returns a true value, then dbh->disconnect will be called after location information is retrieved. dbh () Accepts, returns - and creates, if necessary - the DBI handle that will be used to retrieve location information. This is only separate to make it easy to override. datafile ( path_to_file ) Accepts and returns the location of the SQLite file we expect to provide location data. If no file path is supplied, or found by checking $Geo::Postcode::Location::datafile and $ENV{POSTCODE_DATA}, then we will scan the path to locate the default data file that is installed with this module. tablename () Sets and gets the name of the database table that should be expected to hold postcode data. cols () Returns a list of the columns we should pull from the database row into the location object's internal hash (and also provide as instance methods). This isn't used in the SQL query (which just SELECTs *), so we don't mind if columns are missing. AUTOLOAD () Turns the columns defined by "cols" into lookup methods. You can't set values this way: the whole module is strictly read-only. gridref () Returns a proper concatenated grid reference for this postcode, in classic Ordnance Survey AA123456 form rather than the all-digits version we use internally. See http://www.ordnancesurvey.co.uk/oswebsite/freefun/nationalgrid/nghelp2.html or the more sober http://vancouver-webpages.com/peter/osgbfaq.txt for more about grid references. Unlike other grid methods here, this one will also strip redundant trailing zeros from the eastings and northings for the sake of readability. distance_from () We prefer to use grid references to calculate distances, since they're laid out nicely on a flat plane and don't require us to remember our A-levels. This method just returns a single distance value. You can specify the units of distance by setting $Geo::Postcode::Location::units or passing in a second parameter. Either way it must be one of 'miles', 'km' or 'm'. The default is 'km'. bearing_to () Returns the angle from grid north, in degrees clockwise, of the line from this postcode to the postcode object supplied. friendly_bearing_to () Returns a readable approximation of the bearing from here to there, in a form like 'NW' or 'SSE'. AUTHOR
William Ross, wross@cpan.org COPYRIGHT
Copyright 2004 William Ross, spanner ltd. This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.10.1 2011-02-05 Geo::Postcode::Location(3pm)
All times are GMT -4. The time now is 08:23 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy