Problem Importing CSV

3 years 1 month ago #323233 by Ake2017
Problem Importing CSV was created by Ake2017
I was populating my members from Excel as CSV

I have a feeling that it is essential that the csv file includes/starts with the "id" (even if it is empty for all new users).
(My database got corrupted, and I think this could have been the reason.)
If so, the checking for "id" before import would be fine,

I imported names with scandinavian umlaut characters as well as é (accented-e). They were replaced with "?" in their fields.
Although the fields themselves do not object to those characters if edited in-place.
If the import rules are really stricter than the field rules, is there a workaround (or can those rules be made less strict.
Except for "username" I do not see a good reason to prevent any printable characters from importing to name fields).
Or is there a good reason for that?

I also found that importing immediately sends an invite to the email address imported.
I would have liked to be able to suppress that before importing (so I can send the invite when all the bits and pieces are in place).

By the way- when importing I had a need to import "invalid" e-mail addresses although during registration they are required (and thus, if they are not valid, they abort the import for that line). As I want the member in place, even if the e-mail is unknown at import time, is there a workaround?

On one occasion I imported just "id" and a "cb_variable" and got 15 errors stating "Please enter a name".
First- I imported 30 id's, but it gave errors for 15, and just skipped the remaing 15 (is there a maximum amonut of errors before "Import Aborted"?).
Second- How can a name field having anything to do with this import.
Third- The errors of the type "Please enter..." would be much more informative if they informed about what is actually wrong, and where (e.g. "The value <xxx> is not recognized as a proper value for the field <yyy> (plus a link to the filter spec ;-))

Kind regards, Ake2017

Please Log in to join the conversation.

3 years 1 month ago #323247 by krileon
Replied by krileon on topic Problem Importing CSV

I imported names with scandinavian umlaut characters as well as é (accented-e). They were replaced with "?" in their fields.
Although the fields themselves do not object to those characters if edited in-place.
If the import rules are really stricter than the field rules, is there a workaround (or can those rules be made less strict.
Except for "username" I do not see a good reason to prevent any printable characters from importing to name fields).
Or is there a good reason for that?

How strings store is entirely dependent on the collation set within your database. Typically the collation should be utf8mb4_unicode_ci and you CSV should be using UTF8 charset.

I also found that importing immediately sends an invite to the email address imported.

That's normal. It tries to go through normal backend user create process for imports.

I would have liked to be able to suppress that before importing (so I can send the invite when all the bits and pieces are in place).

Turn off emails in CBs configuration before importing.

By the way- when importing I had a need to import "invalid" e-mail addresses although during registration they are required (and thus, if they are not valid, they abort the import for that line). As I want the member in place, even if the e-mail is unknown at import time, is there a workaround?

You need to import the same information Joomla requires to create a functioning user. This means username, name, and email address. CB will try and fill username/name if missing. CB will also generate password if one isn't supplied in the 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.

3 years 1 month ago #323267 by Ake2017
Replied by Ake2017 on topic Problem Importing CSV
Thanks a lot.

Two comments/questions.

1. Do you mean that the "collation set" affects import, but not field editing? I don't know "collation set", is this something I can adapt to facilitate importing of names with umlaut characters, if so how?

2. I redirected some member's emails to my own account (I want them imported, even if their proper email adress is not yet known).

But the "invitation letters" I thus received gave the username (as imported), but an empty password.

What could be the reason for that? (It obviously means that my new members are invited, but yet cannot log in... Although I suspect that the "Forgotten PW" will still work)

Please Log in to join the conversation.

3 years 1 month ago #323280 by krileon
Replied by krileon on topic Problem Importing CSV

1. Do you mean that the "collation set" affects import, but not field editing? I don't know "collation set", is this something I can adapt to facilitate importing of names with umlaut characters, if so how?

Database collation affects how it'll be stored in the database. How it stores in the database determines what characters it can store. If the collation isn't correct then some characters are going to get turned into ? on storage. This has nothing to do with CB, but with the database. Joomla, CB, and its database tables all use UTF8. You have to use UTF8 charset in your CSV file as instructed above.

What could be the reason for that? (It obviously means that my new members are invited, but yet cannot log in... Although I suspect that the "Forgotten PW" will still work)

Don't import a password. Exclude it from the CSV entirely and CB will generate one. It should be included with the email, but you may need to temporarily turn on generated passwords in CB > Configuration > Registration for it to be included in the email as we made inclusion of password in email more strict.


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.

3 years 1 month ago - 3 years 1 month ago #323296 by Ake2017
Replied by Ake2017 on topic Problem Importing CSV
Thanks again.

I did not try to import the password. And yet an empty password was sent to the members.
So, it seems that cb did not generate any passwords after all.
I think that I will just let the members use the "forgotten pw" to get on track.

Here is my final Import Strategy for importing from a Swedish Excel Listing (Swedish excel uses decimal commas):

1. If you save the csv file via NotePad (good for cleanup), make sure that "Word Wrap" is TURNED OFF (...or the import will severely get out of sync)
2. Commas cannot be used in any column UNLESS surrounded by citation marks, (","), such as in the "gid" column. Other commas MUST be replaced (i.e. by semicolons). (...or the import will severely get out of sync)
3. Excel dates MUST first be converted to text (…or they will be exported as "DateNo","TimeFraction", the extra comma getting the import severely out of sync.)
4. A "name" column must be included (...or that row will not be imported at all. Raises alert like "Please give your name")
5. A "username" column must be included (...or that row will not be imported at all. Raises alert like "Please give a username")
6. An "email" column must be included (or that row will not be imported at all. Raises alert like "A proper email adress must be given")
7. "name", "username" and (of course) "email" columns may not contain any Scandinavian/German umlaut characters, nor accented characters (like é). (...or you will get an error like "Please input your name")
8. "username" may also not contain any spaces or ellipses (…or you will be alerted to "Input a username")

My remaining work now is to re-insert the ÅÄÖåäö and é characters in the firstname and lastname fields. (They will then be accepted by the database.)

I suggest that importing alerts of the form "Please input your name" should be replaced by a much more explicit message that informs the user about what the problem really is.

SUGGESTION:
An alert in the form of "Import for the row <N> has not been executed because the value <xyz> cannot be imported to the field <name>", will provide a much more efficient feedback for the user.

NEW question: Am I correct in that every import HAS to include the columns id, name, username and email?

I tried to update some of the user-id:s already imported with a file containing only id,cb_variable. However my variable setting was rejected due to "Give your name"-type of reasons. (And if I include those columns I get "name already exists" type of alerts). Why is that - with the required fields already populated with valid data, shouldn't I be allowed to set any other field once I know the id of the member I want to update?

Please Log in to join the conversation.

3 years 1 month ago #323353 by Ake2017
Replied by Ake2017 on topic Problem Importing CSV
For me, cb did not generate a password. cb sent an empty password in the invitation e-mail.
What could be wrong then?

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.300 seconds

Facebook Twitter LinkedIn