Odd error message while try to import users

2 years 4 months ago #327154 by Ake2017
I tried to import some new users

All rows failed to import and the messages issued were...

Cannot import row n because: DB function reports no errors

What could be wrong?

Please Log in to join the conversation.

2 years 4 months ago #327161 by krileon
Replied by krileon on topic Odd error message while try to import users
My guess is your import file was not formatted correctly. Export a user and use that as a basis for your import structure. It will include all the columns you're allowed to import with examples of their formatting. For CSV the following fules should be used in your CSV editor.

1. Character set: UTF-8
2. Separated by Comma
3. Format quoted fields as text
4. " as string delimiter


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

2 years 4 months ago - 2 years 4 months ago #327171 by Ake2017
Replied by Ake2017 on topic Odd error message while try to import users
Well, you are correct.
I missed the opening and closing citation marks (") for the Gids.
Therefore the enclosed square brackets and commas were exposed and consequently upset the import.

No real harm done though. It turns out that the persons were indeed imported anyhow (up until the Gids column)
So...
1. The Error message "Cannot import Row" due to "DB function reports no errors" was not helpful at all. Can it be improved?
2. This problem "natively" only applies the Gids and Params column.
Could those columns be checked before import and a more sensible alert be provided.

Some other Gotchas (as of my current understanding):
3- I use Notepad to save the .csv files to upload. It is vital that "WrapAround" is turned OFF. (or the import will soon get out of sync and potentially destroy the DB)
4- I use Excel to format my upload - BUT any dates MUST be converted to text before creating the .csv-file (Or the will be in the form x,y, uppsetting the sync)
(But I understand that the registration date will be overwritten by the import date)
5- The limitation to UTF-8 means that no Scandinavian "Umlaut"-characters can be imported (a lot of names must thus be corrected after import)
6- Fields that are "Required" on the registration form are also Required for the import .csv (and required to be non-empty, and non-umlaut)
7- (I am a little uncertain about this, but put it here anyhow- to be checked later.)
I think that if you are updating a user who's Name has been corrected to include Scandinavion Umlaut characters (Those cannot be uploaded, but they can manually be entered into the field after upload), THEN the upload will fail with "Please enter a name" for that user ID.
I have tried to find what the defaults are, and what will happen if I import values to the following fields (or not).
Can you hint where to look?

sendEmail (0=False/No, 1=True/Yes)
activation ("") (?)
requireReset (0=False, 1=True)
approved (0=False, 1=True)
confirmed (0=False, 1=True)
cbactivation ("") (?)

Please Log in to join the conversation.

2 years 4 months ago #327175 by krileon
Replied by krileon on topic Odd error message while try to import users

1. The Error message "Cannot import Row" due to "DB function reports no errors" was not helpful at all. Can it be improved?

Improved how? What do you want it to say that would be of more help?

2. This problem "natively" only applies the Gids and Params column.
Could those columns be checked before import and a more sensible alert be provided.

It'll apply to any column that doesn't follow the requirements above. They are already being checked that's why it errors.

3- I use Notepad to save the .csv files to upload. It is vital that "WrapAround" is turned OFF. (or the import will soon get out of sync and potentially destroy the DB)

I can't help you with regards to specific software. There's a 100 different ways you could build a CSV. We chose to be ideally compatible with LibreOffice, Microsoft Excel, and Google Sheets.

4- I use Excel to format my upload - BUT any dates MUST be converted to text before creating the .csv-file (Or the will be in the form x,y, uppsetting the sync)
(But I understand that the registration date will be overwritten by the import date)

Not sure what you mean by this. Dates need to be in SQL format. It will not convert formats for you.

5- The limitation to UTF-8 means that no Scandinavian "Umlaut"-characters can be imported (a lot of names must thus be corrected after import)

Data just needs to be in the same encoding as your database. By default Joomla and CB are UTF-8. You can technically set the encoding to whatever you want I suppose, but you may lose data if it's not compatible with your database encoding.

6- Fields that are "Required" on the registration form are also Required for the import .csv (and required to be non-empty, and non-umlaut)

The import doesn't check required validation. The only required inputs are core Joomla ones. So Name, Username, and Email. Password is also typically required but if excluded CB will generate a password.

7- (I am a little uncertain about this, but put it here anyhow- to be checked later.)
I think that if you are updating a user who's Name has been corrected to include Scandinavion Umlaut characters (Those cannot be uploaded, but they can manually be entered into the field after upload), THEN the upload will fail with "Please enter a name" for that user ID.

This sounds like your database encoding is not set correctly. Joomla by default is UTF-8. There shouldn't be any issues with Scandinavian characters. My guess is your encoding might not be set correctly. It should be utf8mb4_unicode_ci for every table and column in your Joomla database.

activation ("") (?)
cbactivation ("") (?)

Those are for email confirmation and can just be excluded from your import.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

2 years 4 months ago #327177 by Ake2017
Replied by Ake2017 on topic Odd error message while try to import users
Thank you Kyle.

1. IMHO it should not say "Cannot import Row" when in fact it already did (up to the GIDS column).
I can easily think of a lot more informative error messages than "DB function reports no errors".

3,4. I need no help on that - they were just a tips to others. (And you are not compatible with Scandinavian Excel)

5 and 7. As I told you, when I try to import a name with Scandinavian Umlaut characters I am alerted to "Please input name"
However, after the name has been imported, I can change to Scandinavian umlaut characters.
I have not changed any encoding deliberately.

6. OK. So only Name, Username, and Email are really required. Good. Maybe I got it wrong when I understood that also other fields that I myself has declared are required may create problems at upload. I will have to check that.

As for the fields I listed. I want to learn how they work and what the effects are if they are imported with various settings. That must surely be described somewhere. I have not been able to find that explanation, so please point me to a suitable place where it is described.

Please Log in to join the conversation.

2 years 4 months ago #327187 by krileon
Replied by krileon on topic Odd error message while try to import users

1. IMHO it should not say "Cannot import Row" when in fact it already did (up to the GIDS column).
I can easily think of a lot more informative error messages than "DB function reports no errors".

My guess is the Joomla user saved, but the usergroup store behavior failed and that's what triggered the error. There isn't really a way around this order than just deleting the user when that happens. We already run pre-checks before store, but have added a bug ticket to look at this more and see if we can try and catch some common errors sooner.

forge.joomlapolis.com/issues/8782

5 and 7. As I told you, when I try to import a name with Scandinavian Umlaut characters I am alerted to "Please input name"
However, after the name has been imported, I can change to Scandinavian umlaut characters.
I have not changed any encoding deliberately.

Strange, my guess is the CSV file encoding is wrong then. You can set the CSV encoding to technically whatever you want. The UTF-8 recommendation is just so it's more likely to be compatible with Joomla database. Have you tried a different encoding in your CSV file?

As for the fields I listed. I want to learn how they work and what the effects are if they are imported with various settings. That must surely be described somewhere. I have not been able to find that explanation, so please point me to a suitable place where it is described.

sendEmail = should the user receive moderator emails or not
requireReset = should the user be required to reset their password on next login
approved = is the users registration approved or not
confirmed= is the users email address confirmed or not

As mentioned earlier the 2 activation ones are the activation code (Joomlas and CBs) and they can just be ignored.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.422 seconds

Facebook Twitter LinkedIn