Imported Address Data Has Wrong States

[sharingsunshine]

I had 10,000 addresses get imported to our new site and everything seemed fine but after a month of use we discovered that the sales  drop off was because many of the states came across as foreign cities names.  For example, California was changed to Cagliari.  Consequently, customers can't complete their order because no US shipper services Italy.  All of our orders  are in the Continental US.

 

Certainly a customer could  change their address but  instead  they see the no carriers error and bail.  We have seen this  in the GA statistics.

 

I need a way to change Cagliari to California and there are about 9 other states that need to be changed too.  When I open up the addresses section in the admin section it defaults  to Alabama if there was a foreign city there by mistake.

 

Is there any module, script or tool of any kind that can make this editing process easier?

 

Thanks,

 

Randal

 

 

[bellini13]

The customer addresses are stored in the database table ps_address.  So first you need to locate the records in question, and determine what the value of the column id_state is.

 

The value is either going to be 0 (zero), which means that there is not a valid state.  Or the value is going to be a positive number representing the state.

 

Using California as an example, you would find its ID in the back office States page.  Then for each address that should be California, you would change the ps_address.id_state value to California's ID. 

[sharingsunshine]

This was a great help.  I am going to include the sql statement I used for each wrong state.

 

I am using PS 1.5.56

 

Before you work on that table make sure you back it up, in case something goes wrong.

 

UPDATE ps_address
SET id_state = REPLACE(id_state, '221', '42')
WHERE id_state = '221'

 

In my case the wrong states were anything above 53 so I sorted the id_state to be descending and went through and found each wrong state code.  I looked at the zip code and found what state it should be.  Like you said it can be found in the backoffice under the states tab.

 

In the case above that is changing the state code of 221 to 42 which is Tennessee.

 

Hope that helps someone.