Manual Import of Customer Data

[naissurbara]

Apologies in advance if this has been covered elsewhere already, but I was unable to find the answer to my question.

I have had a Zen Cart site operating for a while now and just this weekend decided to upload my customer information from my non-Zen Cart customers. I am reasonably comfortable with databases and SQL, so I pre-formatted all the data (in Excel) for the customers whose info I wanted to upload. I did so for both the customers and address_book tables. I ensured the referential integrity between the two tables is intact (essentially, I uploaded 100 customers, with IDs 100 through 199, each with a single address, also with IDs from 100 to 199, mapped 1-to-1). I populated all of the data in a similar manner to that which was automatically provided by customers creating their accounts through the front end previously. (I did not import any order information, nor to I intend to)

The challenge is, I can see the customers in the Admin site and can send mass e-mails, etc. but when I go to Customers->Customers and choose a customer to try and view or edit their info, in many cases the panel on the right loads with blank info. The URL clearly shows there is a customer ID associated with the customer I am trying to access (e.g. http://www.mysite.com/admin/customer...page=1&cID=151) but the data does not appear to be there.

Notes:
1) I did upload a dummy password for all customers
2) For convenience, my max customer ID before the upload process was around 50, but when I uploaded addresses and customers today I had each of them start at ID 100 and went to 199

Things I tried:
1) Optimize / re-index database
2) Manually populate the customers_info table with dummy timestamp information

Questions:
1) Is there any other table (or tables) that I need to populate?
2) How come some of the customers I uploaded do in fact display address data, etc. while others do not (I did upload the data and can see it in the database in the address_book table)
3) Do the IDs need to be consecutive (i.e. is point #2 in the Notes section above a problem?)

Thanks in advance for all your help.

[RodG]

Questions:
1) Is there any other table (or tables) that I need to populate?

Customer data is spread across three tables, namely. address_book, customers & customers_info

2) How come some of the customers I uploaded do in fact display address data, etc. while others do not (I did upload the data and can see it in the database in the address_book table)

I feel that this is the important question and knowing the answer will solve your problem.

3) Do the IDs need to be consecutive (i.e. is point #2 in the Notes section above a problem?)

I'd have to say 'probably not', but if it were important I'd suspect you'd be having trouble inserting new data rather than retrieving existing data.

My suggestion would be to concentrate on the answer to question#2, and the 'obvious' thing to look for is for anything 'odd' about the data. For example, are there any unescaped quotes (or perhaps commas) in the data?

My view is that if the problem only shows with *some* customers then the import itself is probably 100% valid and all the relevant tables have been populated, which only leaves the data itself as being the cause of the problem.
If your questions #1 & #3 were at all relevant I'd be expecting none of the imports would be valid rather than just some.

Cheers
Rod

[naissurbara]

Thank you so much for the detailed reply. I am glad to know that there are 3 key tables, all of which I have already tackled in my bulk import. I am also happy to know that numbers don't need to be consecutive (that would have been quite inconvenient). I did originally suspect that there may be some internal counter variable that tracks the number of customers and didn't get updated because of my back-end upload. Seems that is not the case.

This brings us to the single remaining item, then, which is Question 2. I do not see most of the records that were bulk-inserted. I checked that the data is populated in a consistent manner and still cannot pinpoint what may be causing the issue.

The data I populated consists of first/last names, e-mail address (user ID), password, address info (including zone ID where applicable and country ID for all records, for countries with no zone ID I populated a zero). I also populated "default" values for all columns in these tables to match those that were in the existing customers (e.g. "TEXT" for email preference, "1" for newsletter flag, etc.)

I also checked to see if by any chance certain countries are causing the problem but that is not the issue. Also all addresses appear to be normal in the sense that there are no quotes or odd characters.

More observations:
- If I choose the "send email" option from admin, the site recognizes all of the newly-added customers.
- If I go to customers->customers, the site also displays all customers. The problem is only when I try to click on a particular customer from here, as the panel on the right appears empty (see screen shot). This happens for most but not all customers. I tried to find anything common in the data for the customers which do work properly (or don't), but couldn't find anything yet. The URL, in all cases displays a cID value, though if I do click on "Edit" and move to the next page, cID gets lost and is now blank in the address bar in the web browser.

I am really baffled here.

[naissurbara]

Attachment.

[naissurbara]

I think I have just found the issue. Rod is right - it is a data issue. I did not populate the Company field for all customers (i.e. NULL) and it seems I need to have at least an empty string there. I am currently unable to access my DB (in the officenow, DB only available from home) but that is what I suspect is the case. I will update this thread upon verifying this.

[naissurbara]

Sadly, I was wrong and am now back to square one. Any thoughts?

[DrByte]

You've not maintained relational integrity between address records and countries and zones tables. And you've probably also not set the customers' default address-book value either.

[naissurbara]

Thank you DrByte.

I am quite certain I have actually maintained the relational integrity with the address records and countries/zones. Most of my addresses are in the Middle East and therefore zone is 0. For the Australia, Canada, and USA addresses I have gotten the proper zone from the zones table. Country ID has been populated for every record. Is there any other relational integrity issue with regards to countries/zones that I should check? As for a default address for customers - I did map that in the table. In fact, to make things easier, my newly-inserted customers each have 1 address. So, customers with IDs 100-199 all have corresponding 1-to-1 addresses in the address book table, with IDs 100-199. I have entered the mapping between the two for each record. It was simple since each customer ID has a corresponding and address ID with the same value.

Thanks.

[naissurbara]

Would it help if I get an extract of a row that works in my DB and another that does not? (and if so, from which tables?) I can provide customers, address book, and customer info. Anything else?

[DrByte]

It may reveal to you some information about where the problem is.