no way to import customers in 3.7?

i want to import customers csv list in nop 3.7 but no way?

plz help?

There is nothing built-in.  In can be done manually with SQL, but you'll need to deal with passwords.

where to add excel list to import customers list or emails?

Please clarify "where to add excel list...".

New York wrote:

the sql cmd link given by you do not talk about the source of customer list to import. i want to import customers from excel list. how can i do that?

There is no built-in Customer import in nopCommerce.  If you use SQL, you need to first get your Excel data into the database. SSMS has a "SQL Import and Export Wizard" that can read Excel data into a table.

pepper wrote:
Hello Pepper.

I've just seen your private message regarding my previous answer about importing customer data directly into the database. I mention in the post on the second link above that "This assumes you've got a table called MyOldCustomersTable that contains both the email and plain text password but would need adapting to wherever the data was actually coming from".

As New York says if you don't have the information in the database already it's pretty easy to import it into a database table temporarily so you can import it into nop. Assuming you've got access to SQL Server Management Studio that can connect to your nop database just google for "ssms import excel" and you'll find plenty of info about how to import your spreadsheet using the import wizard. If you're on shared hosting you might not have direct database access from SQL Server Management Studio but it's likely your host would provide some sort of facility for importing data from flat files to your database so just find out what that is and go from there.

pete

confirming if i understood steps

A. Create table (MyOldCustomersTable) in nop database or different blank database?
B. create excel customer list with any text password
C. import excel customer list in (MyOldCustomersTable)
D. run this query as it is below on 3.6 nop db?
    this query will import customer list from (MyOldCustomersTable) to customer table in nop and make all password hashed and all customer need to reset pass?

WITH 
  BinarySaltyUsers AS (
    SELECT NewId() AS CustomerGuid
        ,MyOldCustomersTable.Email
        ,CAST(RTRIM(MyOldCustomersTable.[Password]) AS varchar(201)) AS [Password]
        ,CONVERT(varbinary(max), CAST((ABS(CHECKSUM(NewId())) % 90000)+ 9999 AS varchar(100))) AS Salt
      FROM MyOldCustomersTable
        ),
  Base64SaltyUsers AS (
  SELECT CustomerGuid
      ,Email
      ,[Password]
      ,cast('' as xml).value('xs:base64Binary(sql:column("Salt"))', 'varchar(max)') AS PasswordSalt
    FROM BinarySaltyUsers
     ),
  PasswordUsers AS (
  SELECT CustomerGuid
      ,Email
      ,PasswordSalt
      ,[Password]
      ,UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', [Password]+PasswordSalt),1,0)) AS HashedPassword
    FROM Base64SaltyUsers
     )

  MERGE AshNop.dbo.Customer AS mTarget
  USING (
     SELECT CustomerGuid
       ,Email
       ,PasswordSalt
       ,[Password]
       ,HashedPassword
     FROM PasswordUsers) AS mSource
  ON (mSource.Email = mTarget.Email
    )
  WHEN NOT MATCHED BY TARGET
    THEN INSERT 
         (CustomerGuid
         ,Username
         ,Email
         ,[Password]
         ,PasswordFormatId
         ,PasswordSalt
         ,AdminComment
         ,IsTaxExempt
         ,AffiliateId
         ,VendorId
         ,HasShoppingCartItems
         ,Active
         ,Deleted
         ,IsSystemAccount
         --,SystemName
         --,LastIpAddress
         ,CreatedOnUtc
         --,LastLoginDateUtc
         ,LastActivityDateUtc
         --,BillingAddress_Id
         --,ShippingAddress_Id
         )
    VALUES (CustomerGuid
         ,Email --Username
         ,Email
         ,HashedPassword
         ,1 --PasswordFormatId
         ,PasswordSalt --PasswordSalt
         --,AdminComment
         ,0 --<IsTaxExempt, bit,>
         ,0 --<AffiliateId, int,>
         ,0 --<VendorId, int,>
         ,0 --<HasShoppingCartItems, bit,>
         ,1 --Active
         ,0 --<Deleted, bit,>
         ,0 --<IsSystemAccount, bit,>
         --,<SystemName, nvarchar(max),>
         --,<LastIpAddress, nvarchar(max),>
         ,GETUTCDATE() --<CreatedOnUtc, datetime,>
         --,<LastLoginDateUtc, datetime,>
         ,GETUTCDATE() --<LastActivityDateUtc, datetime,>
         --,<BillingAddress_Id, int,>
         --,<ShippingAddress_Id, int,>
        )
  OUTPUT $action, inserted.*;

pepper wrote:

Yeah pretty much. The MyOldCustomersTable would need 2 columns (Email, Password). The whole point of this query though is to migrate data from system where the customers already have passwords set and you want to keep them the same when you import them to nop. It salts and hashes the plain text passwords using the same algorithm that nop uses before inserting them into the Customer table in the nop database.  If you don't need to retain passwords from an existing system  then you can just set all the salt and password columns to NULL for the new customer records. The customers would then have to go through the password reset process to set a password and access their accounts.

A. Create table (MyOldCustomersTable) in nop database or different blank database?
B. this sql query that i will run on nop db will work without changes?
i have to start query with WITH?