I have a database of user data in an excel file that I wish to import into backdropcms.
I exported "users" backdropcms table and 2 rows of contents (fresh install). Using Excel, I added additional columns/fields desired, the dataset was then mapped to the fields. then saved as .csv. Then, used a .csv to .sql conversion tool.(only a small test set of 25 records.)
Then, attempted to import the sql file into the "users" table via phpmyadmin.
Fail. Recurring error was "incorrect number of columns".
I suspect this solution is two fold, though unsure.
a. manually add the additional fields/columns to the table likely through backdropcms interface or hopefully through phpmyadmin
b. once fields exist, run sql import again.
I am hoping there is an easier solution. The database of "users" is about 50,000, so importation is important.
As a "best practices" consideration, I'm wondering if a better approach is to create another table "user_details", which then opens in a block below the "user" pages. In this way, automatic updates/modifications to the base table set do not blow up the site structure. Thoughts and best practice tips greatly appreciated. It would seem that a sitewide search for "users" table would then enable me to determine which pages require a link to the additional table "user_profile". In this way, the upgrades or changes to the base system structure would not impair or damage the site, with the exception of the additional code insertion linking the "user_profile" table.
Help appreciated. I'm quite skilled with backend server functionality yet new to both backdrop and customization of these sites.
Thanks in advance.
Comments
My first thought would be to try to set up the CSV as an import using Feeds. I believe it can handle user imports (but testing would be needed of course).
Edit to add: I've seen a few comments related to user imports so decided I'd start porting the "User Import" module. if that seems like it might be useful, feel free to jump over there and help with testing and fixes:
https://github.com/backdrop-contrib/user_import
Yes, I recommend Feeds as well. And you'll need to create the fields on the user account from the UI first so you can map your CSV columns to fields.
You should avoid manually creating tables since Backdrop (like Drupal) has a particular approach to its data structure.
I further read about adding the module PROFILE to the USER.
Then, with PROFILE one can create multiple "profile blocks" of info, to wit my thinking; Contact (firstname, last name, phone1, phone2, phone3, emaila, emailb). So, using the "add field" in profile area of backdrop, I added "First Name" and "Last Name" as fields. Reviewing the database via phpmyadmin, the result is two additional tables for each field;
field_data_field_first_name
field_data_field_last_name
field_revision_field_first_name
field_revision_field_last_name
and each of these tables (1 table per profile field) has multiple fields. Apparently, each of these fields is an entity, with entity ID, etc.
Bottom-line, the concept of a "profile table" and "profile fields" to extend user details/specificity is good, but terrible in practice. Each additional field added in this manner adds 2 tables and 12-16 database fields instead of one. It may be effective "hack" for a single field add, but 50 fields is a disaster...
Again, it would make much more sense to;
- establish a "user_profile" table, linked via user table primary key
- extend user detail specificity/fields
Under the backdrop "default", it virtually will be impossible to import client data as the fields are spread over so many tables each with their own set of keys etc.
Certainly someone has considered or encountered this before...
You can probably find old discussions on drupal.org about table structure for fields. This is what Backdrop inherited and I don't think it'll change given the target userbase. If you have heavy needs for something like a CRM you might want to consider using CiviCRM, which integrates with Backdrop.
By the way, you don't necessarily need an extra module to add fields to user accounts. Profile provides extra features but isn't necessary.
For historical purposes these links might help:
While I understand your reply, band-aids on top of band-aids is only as good as the first band-aid.
CiviCRM has sooooooo much goo, difficult to manage that as well.
Looking for a clean KISS (keep it simple stupid) platform. Maybe it doesn't exist.
I don't think you can look at as just a Band-Aid solution. There were design considerations early on that ended up being quite different from what you would like/need.
If an out of the box CMS or CRM has too much "too" then maybe you would like a library instead. I've not used any of these but might be more down your alley: Laravel, Django, etc. These provide some structure but let you build things more like you'd like (or so I assume from the outside).