Import / Export columns

Permalink Browser Info Environment
When exporting, the column order is
State, Zip, Country

When importing, the column order is
State, Country, Zip

So an export followed by an import results in country and zip data being cross-filled.

Type: Pre-Sale
Status: Resolved
JohntheFish
View Replies:
JohntheFish replied on at Permalink Reply
JohntheFish
PS.
The example csv has the column order
State, Country, Zip
JohntheFish replied on at Permalink Reply
JohntheFish
Thinking more about this, the cleanest fix for any existing data would be to change the import sequence and example to match the export sequence.
edbeeny replied on at Permalink Reply
edbeeny
Thanks John,

I will also amend this :)
JohntheFish replied on at Permalink Reply
JohntheFish
Just realised the new version is available and have been doing some checks.

At first, I created a store, no groups, exported and imported, CSV import gave a column mis-match error (expected 12 columns). Looking at the csv, there are only 11 columns.

Guessing this could be because there are no groups, I created some groups. Now when I edit my test store and save, I get a database error.
An exception occurred while executing 'INSERT INTO bthwstorelocation VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [null, "John Shop", "", "San Francisco", "CA", "United States", "94133", "37.8083823", "-122.41041970000003", "", "", "", "", "My test shop", "0", "0", ["1","2","3","4","5"]]: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'Array' for column 'gID' at row 1

Thinking maybe this was a table update glitch, I did a complete uninstall, dropped the tables, then reinstalled and repeated. Exactly the same problems.
edbeeny replied on at Permalink Reply
edbeeny
OK that's odd, I will have a look into that error.

Ed
edbeeny replied on at Permalink Reply
edbeeny
John,

Pushed an update that fixes that issue.

Ed
JohntheFish replied on at Permalink Reply
JohntheFish
Same error. Double checked I had installed the update.
JohntheFish replied on at Permalink Reply
JohntheFish
Looking through the trace, it stems from:

22 Concrete\Core\Database\Connection\Connection Execute
/­packages/­hw_store_location/­controllers/­single_page/­dashboard/­hw_store_locations/­addstore.php299
edbeeny replied on at Permalink Reply
edbeeny
Can you PM me your csv file?
JohntheFish replied on at Permalink Reply
JohntheFish
That's not in a csv.

The db error happens when trying to add a store with groups through the dashboard page in order to create some content so I could export a CSV with groups.
edbeeny replied on at Permalink Reply
edbeeny
Yep sorry, me not reading the support ticket correctly.
edbeeny replied on at Permalink Reply
edbeeny
John,

Can you amend Line 296 in hw_store_location/­controllers/­single_page/­dashboard/­hw_store_locations/­addstore.php

from
$_POST['hwGroups']


to
NULL

Ed
JohntheFish replied on at Permalink Reply
JohntheFish
Adds OK.
Similar error on edit from
/­home/­john/­Documents/­web/­customers/­wayne_dejager/­ling_ling/­cms2/­packages/­hw_store_location/­controllers/­single_page/­dashboard/­hw_store_locations/­addstore.php195

Hacked 195 to
'gID' => null, //$_POST['hwGroups'],


seems to resolve that
edbeeny replied on at Permalink Reply
edbeeny
OK thanks John,

will push an update to fix.

Ed
JohntheFish replied on at Permalink Reply
JohntheFish
Export and then import leaves the country and zip fields crossed over, so back to the original bug at the top of this thread.
JohntheFish replied on at Permalink Reply
JohntheFish
With the export/import crossed over, I then got a faulty geocode that appears to be stuck.

Once a store is geocoded, a change of address (ie correcting the country and zip) does not invalidate the geocode.
edbeeny replied on at Permalink Reply
edbeeny
Yes the geo code is activated when doing an address search.

I will look into this and think of another way to update, may be a geo refresh button.

Ed
edbeeny replied on at Permalink Reply
edbeeny
Version 1.3.5 pushed to the market place.
This should fix the cross over issue and other issues you have discovered,

Ed
JohntheFish replied on at Permalink Reply
JohntheFish
Export/Import now OK.
After importing and adding to existing , I edited a loactaion and changed the zip.
An exception occurred while executing 'UPDATE bthwstorelocation SET name = ?, address = ?, city = ?, state = ?, country = ?, zip = ?, latitude = ?, longitude = ?, phone = ?, email = ?, url = ?, urltext = ?, extra = ?, storeimage = ?, markericon = ?, gID = ? WHERE sID = ?' with params ["John Store", "", "San Francisco", "CA", "United States", "94134", "", "", "", "", "", "", "test store", "0", "0", null, "3"]: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'latitude' at row 1

trace to:
/­packages/­hw_store_location/­controllers/­single_page/­dashboard/­hw_store_locations/­addstore.php195
JohntheFish replied on at Permalink Reply
JohntheFish
Nothing to do with the zip. Edit and save with no changes gives the same error.
edbeeny replied on at Permalink Reply
edbeeny
Looks like your database didn't like trying to update the table with a blank latitude data.
edbeeny replied on at Permalink Reply
edbeeny
John,

Can you try this hack.

From line 173 add

if(empty($_POST['hwLatitude'])) {
            $latitude = '0';
                }
                else {
            $latitude = $_POST['hwLatitude'];
        }
        if(empty($_POST['hwLongitude'])) {
            $longitude = '0';
        }
        else {
            $longitude = $_POST['hwLongitude'];
        }


and replace lines 197 and 198 with

'latitude' => $latitude,
                'longitude' => $longitude,


Thanks Ed
JohntheFish replied on at Permalink Reply
JohntheFish
Had to hack it around a bit. The empty test was back to front [edit - in the email, I see you have corrected that above]. '0' as a string failed. 0 as a number left lat/lng showing as 0/0.

Seems to accept null, which surprises me as my database settings usually object to null for numeric types. So I am not confident the below will be a good solution. Perhaps numeric 0 is safer.

if(empty($_POST['hwLatitude'])) {
            $latitude = null;
                }
                else {
            $latitude = $_POST['hwLatitude'];
        }
        if(empty($_POST['hwLongitude'])) {
            $longitude = null;
        }
        else {
            $longitude = $_POST['hwLongitude'];
        }

The above could be simpler:
if(!empty($_POST['hwLatitude'])) {
            $latitude = $_POST['hwLatitude'];
        }
        if(!empty($_POST['hwLongitude'])) {
            $longitude = $_POST['hwLongitude'];
        }
edbeeny replied on at Permalink Reply
edbeeny
I noticed and changed the code, you must have copied before I did!

So if string failed I'm guessing floatval('0') may fix the issue?



if(empty($_POST['hwLatitude'])) {
            $latitude = floatval('0');
                }
                else {
            $latitude = $_POST['hwLatitude'];
        }
        if(empty($_POST['hwLongitude'])) {
            $longitude = floatval('0');
        }
        else {
            $longitude = $_POST['hwLongitude'];
        }
JohntheFish replied on at Permalink Reply
JohntheFish
A simple $latitude=0 etc worked for me, but floatval is certainly more correct and ensures type compatibility.

However, with a lat/lng 0.0/0.0 value now in the database, will it be geocoded correctly? Ow will it get stuck off west africa? I haven't tested that far ahead.
edbeeny replied on at Permalink Reply
edbeeny
As it stands you will be stuck just off the coast of West Africa!.
I'm having a think of the best way to resolve this, I may alter the job to pick up blank and 0 and re geocode.
edbeeny replied on at Permalink Reply
edbeeny
Just pushed another update that changes the job to also re-geocode where the longitude and latitude are 0.

Not the best solution but works for now.

I will add a better indication on the store add/edit screen, probably with a map so you can visually see where it has geocoded to. Also have the option to refresh the location.

Ed
JohntheFish replied on at Permalink Reply
JohntheFish
v1.3.6 fixes these issues. OK to mark this resolved.

concrete5 Environment Information

8.1.0

Browser User-Agent String

Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36

Hide Post Content

This will replace the post content with the message: "Content has been removed by an Administrator"

Hide Content

Request Refund

You have not specified a license for this support ticket. You must have a valid license assigned to a support ticket to request a refund.