PDA

View Full Version : Modifying my Postcode table

Gary Williams
02-13-2008, 08:08 PM
Hi All,

I have a large postcode file that needs modfying. It came as a csv file (36MB) which I imported into MySQL, giving a total of 1,762,919 records. I worked out the changes (8 in total) I need to make and using a 5 record sample, checked the asp script I wrote produced the correct changes.

My problem is this. To process this database table on my local PC using IIS takes many hours and times out before completion. I tried using SQLyog but the code that works in asp does not work in the query window of SQLyog.

How do I process this table to get my final result?

The code that works is as follows. Each 'execute' line deals with one of the 8 changes change. The rem'ed out html is just to show the results for an initial loop of 5 records.

Regards

Gary

===============================

<html>
<body>
<%
Dim ocn,ors,strConnection,strSQL

set ocn = Server.CreateObject("ADODB.Connection")
set ors = CreateObject("ADODB.Recordset")

strConnection = "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=root;"
strConnection = strConnection & "pwd=;database=postcodes;Option=3;"
ocn.open strConnection

strSQL = "SELECT * FROM currentpostcodes ORDER BY recordid ASC;"

ors.open strSQL, ocn
%>

<table border="1" width="100%">
<tr>

<%
'for each x in ors.Fields
'response.write("<th>" & x.name & "</th>")
'next
%>

</tr>

<%
a = 1
ors.movefirst
' Do until a = 5
Do until ors.eof
%>

<tr>
<% 'for each x in ors.Fields%>
<td><% 'Response.Write(x.value)%></td>
<% ' next %>
</tr>

<%
var1 = ors("postcode1")
var2 = ors("postcode2")
var3 = var1+var2
var4 = left(ors("ratingdata"),1)
var5 = mid(ors("ratingdata"),2,1)
var6 = right(ors("ratingdata"),1)
bac = (asc(""&var4&"")-64)

ocn.Execute ("UPDATE current SET CompanyNumber = '1' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET AreaLoad = 'N' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET FloodArea = 'N' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET Postcode = '" & var3 & "' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET ContentsAreaCode = '" & var4 & "' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET SubsidenceRate = '" & var5 & "' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET FloodRate = '" & var6 & "' WHERE recordid = " & a & "")
ocn.Execute ("UPDATE current SET BuildingsAreaCode = '" & bac & "' WHERE recordid = " & a & "")

a=a+1
ors.movenext
loop

ors.close
ocn.close
%>

</table>
</body>
</html>

=========================
shyam
02-13-2008, 08:36 PM
why waste time looping around in asp?

assuming that the table current is simply a copy of the currentpostcodes table with the extra columns u can run this query once

update `current` set
`CompanyNumber` = '1',
`AreaLoad` = 'N',
`FloodArea` = 'N',
`Postcode` = postcode1 + postcode2,
`ContentsAreaCode` = substr(ratingdata, 0, 1),
`SubsidenceRate` = substr(ratingdata, 1, 1),
`FloodRate` = substr(ratingdata, -1),
`BuildingAreaCode` = ascii(ratingdata)-60;
Gary Williams
02-13-2008, 08:46 PM
Hi Shyam,

I forgot to mention that for each record, these parameters change in value so must be re-computed each time from the original postcode fields values or the ratingdata field value

Postcode = postcode1 + postcode2,
ContentsAreaCode = substr(ratingdata, 0, 1),
SubsidenceRate` = substr(ratingdata, 1, 1),
FloodRate = substr(ratingdata, -1),
BuildingAreaCode` = ascii(ratingdata)-60;

Would your solution deal with that?

Regards

Gary
shyam
02-13-2008, 08:59 PM
I forgot to mention that for each record, these parameters change in value so must be re-computed each time from the original postcode fields values or the ratingdata field value

Would your solution deal with that?


as long as the following assumption holds good
assuming that the table current is simply a copy of the currentpostcodes table with the extra columns u can run this query once


the update query will run for every row and the values for that row will be used in the computations for the set fields
Gary Williams
02-15-2008, 11:44 AM
Hi Shyam,

That worked!

I had to make a few tweeks to the code, ie, use concat and modify the substr parameters, which gave the following:

update `current` set
`CompanyNumber` = '1',
`AreaLoad` = 'N',
`FloodArea` = 'N',
`Postcode` = concat(postcode1,postcode2),
`ContentsAreaCode` = substr(ratingdata, 1, 1),
`SubsidenceRate` = substr(ratingdata, 2, 1),
`FloodRate` = substr(ratingdata, -1, 1),
`BuildingsAreaCode` = ascii(substr(ratingdata, 1, 1))-64;

but it processed all the necessary changes in only a few minutes.

Many thanks for your help,

Regards

Gary