Ecommerce software home
Shopping Cart Software Forum for Ecommerce Templates
 
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?


Find us on Facebook Follow us on Twitter View our YouTube channel
Search our site

 All Forums
 Technical
 ECT Hosted Store
 Uploading Data
 New Topic  Reply to Topic
 Printer Friendly
Author  Topic   

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/19/2021 :  00:26:39  
In the hosted version of ECT how is uploading data to the database carried out please?

Many thanks
John

dbdave
ECT Moderator

USA
9023 Posts

Posted - 01/19/2021 :  04:45:23  
Hi, this should get you going - https://www.ecommercetemplates.com/help/ecommplus/help.asp#csv

David

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/19/2021 :  06:17:15  
Thanks David,
I will have a go when I have the fields aligned!

Regards

John

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  06:42:58  
Hi David
I found CSV upload under product admin.
I chose the csv data file and selected Add to database.
Very straight forward.

I then received the error message below.
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM products WHERE pID='abcwxyz'' at line 1open_basedir: YES
Show errors and Stop on erres were selected

ERROR:
Column list is: "pid,pname,psection,pprice,pwholesaleprice,plistprice,pshipping,pshipping2,pweight,pdisplay,psell,pbackorder,pgiftwrap,pexemptions,pinstock,ptax,pdropship,pstaticpage,pstockbyopts,precommend,porder,psku,pmanufacturer,psearchparams,ptitle,pmetadesc,pstaticurl,pdescription,pminquant,pdateadded,plongdescription,"

Rows successfully added 0

This page took: 0.0039 seconds
Update Successful !
Please click here for the admin home page.

Any ideas what to do next please?
Thanks
John

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  06:47:55  
Ah sorry the lines were too long so I repeat the message
I found csv upload under product admin.
I chose the csv data file and selected Add to database.
Very straight forward.

I then received the error message below.
You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near
'FROM products WHERE pID='abcwxyz'' at line 1open_basedir: YES
Show errors and Stop on erres were selected

ERROR:
Column list is: "pid,pname,psection,pprice,pwholesaleprice,plistprice,pshipping,
pshipping2,pweight,pdisplay,psell,pbackorder,pgiftwrap,
pexemptions,pinstock,ptax,pdropship,
pstaticpage,pstockbyopts,precommend,porder,psku,
pmanufacturer,psearchparams,
ptitle,pmetadesc,pstaticurl,pdescription,
pminquant,pdateadded,plongdescription,"

Rows successfully added 0

This page took: 0.0039 seconds
Update Successful !
Please click here for the admin home page.

insight
ECT Moderator

USA
4283 Posts

Posted - 01/21/2021 :  10:19:45  
Is that a comma you have at the end of the first line, after pLongDescription? That shouldn't be there, the fields are comma-separated but at the end of line there's nothing to separate.

Incidentally, you don't need to use all the columns if you don't need to. pID is the only column that must be there, others you can skip and the cart will set a sensible default value for them.

Peter


Professional ecommerce web hosting services
Shared hosting Windows & Linux | Dedicated servers | Domains | SSL
Ecommerce Templates specialists since 2003
https://servelink.com

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  12:33:58  
There is a comma at the last field, I cannot see how it gets there nothing in the row titles.
I removed that last column but still the same error.
I then removed all columns except col1 and received this error.

You have an error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near
'FROM products WHERE pID='abcwxyz'' at line 1open_basedir:
YES ERROR:
Column list is: "pid,"
Rows successfully added 0
This page took: 0.0007 seconds
Update Successful !
pID='abcwxyz'' This is not correct
Regards
John

insight
ECT Moderator

USA
4283 Posts

Posted - 01/21/2021 :  12:51:22  
You'll see if you download inventory.csv and then re-upload it without any changes to update the database, it will go fine. The format you receive in inventory.csv is what you want to stick with, excluding any obscure columns you are not using which you can get rid of. At it's simplest, one header line and one product line like this will work:
"pID","pName"
"test product id","test product name"


Why not try trimming your file down to the header line and a single product line, have the pID and a few sample columns present and then you can easily paste it here if there's any issues. I believe Excel can sometimes mess with data when you export it, but as a Mac user I'm not really qualified to comment much on that.

Peter


Professional ecommerce web hosting services
Shared hosting Windows & Linux | Dedicated servers | Domains | SSL
Ecommerce Templates specialists since 2003
https://servelink.com

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  13:17:13  
I reduced the table to 2 columns and 1 Row

Adding row: Duplicate entry '' for key 'PRIMARY'Line 3,
10623
Rows successfully added 2
Error rows 1
This page took: 0.0021 seconds
That is 0.0007 seconds per row

Operation Failed !
Please click here for the admin home page or to go back and try again.
Regards
John

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  13:20:40  
Could I do this with FTP?

dbdave
ECT Moderator

USA
9023 Posts

Posted - 01/21/2021 :  13:23:00  
quote:
pID='abcwxyz''


Should not be an apostrophe there.

Editing/creating csv files with excel can be troublesome.

David

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  13:33:24  
Hi David
This is what is in the table I tried last.
pID pName
alp3033 Alpha Coral, loose fitting, casual short sleeved dress 6437

No pID='abcwxyz'' in there.
There is the comma in the text...
Regards
John

insight
ECT Moderator

USA
4283 Posts

Posted - 01/21/2021 :  13:41:06  
Where's the comma to separate the two items in each row? Also keep in mind that you can either ADD or UPDATE, but you can't do both at once. So if a pID already exists in the store you need to UPDATE it (you can't ADD it as it's already present).


Peter


Professional ecommerce web hosting services
Shared hosting Windows & Linux | Dedicated servers | Domains | SSL
Ecommerce Templates specialists since 2003
https://servelink.com

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/21/2021 :  14:00:39  
Thanks Peter.
Quote:
Where's the comma to separate the two items in each row?
I don't understand what is being said here. There are no commas in this extract
pID pName
alp3033 Alpha Coral, loose fitting, casual short sleeved dress 6437

I tried to update the table and received this error
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near 'FROM products WHERE pID='abcwxyz'' at line 1open_basedir:
YES ERROR:






The latest error message:

Column list is: "pid,pname,psection,pprice,pwholesaleprice,plistprice,pshipping,pshipping2,pweight,pdisplay,psell,pbackorder,
pgiftwrap,pexemptions,pinstock,ptax,pdropship,pstaticpage,pstockbyopts,precommend,porder,psku,
pmanufacturer,psearchparams,ptitle,pmetadesc,pstaticurl,pdescription,pminquant,pdateadded,plongdescription,"
Rows successfully updated 0
This page took: 0.0041 seconds
Update Successful !

btw Peter would you break your sentences as I cannot see the longer ones
and have to copy the text into note pad before I can.

Thanks for all the help
John

insight
ECT Moderator

USA
4283 Posts

Posted - 01/21/2021 :  14:24:33  
quote:
There are no commas in this extract

No, but there should be, that's what makes a comma separated values file. Like this:

"pID", "pName"
"alp3033", "Alpha Coral loose fitting casual short sleeved dress 6437"

Peter


Professional ecommerce web hosting services
Shared hosting Windows & Linux | Dedicated servers | Domains | SSL
Ecommerce Templates specialists since 2003
https://servelink.com

Edited by - insight on 01/21/2021 17:28:25

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/22/2021 :  01:44:55  
Hello Peter,
I have read your comments several times but I still do not understand what needs to be done.
Can you please inform me what I need to do to get this solved.
------------------
Looking at saving a csv file in excel there are several versions
CSV UTF-8(Comma delimited)(*.csv)
CSV (Comma delimited)(*.csv)
CSV (MS-DOS) (*.csv)
I have been using CSV UTF-8(Comma delimited)(*.csv) is this correct?

Many thanks

John

Edited by - JH10 on 01/22/2021 02:11:14

Vince
Administrator

39851 Posts

Posted - 01/22/2021 :  05:02:39  
Hi John
CSV (Comma delimited)(*.csv)
That will do fine.

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

JH10
Advanced Member

United Kingdom
272 Posts

Posted - 01/22/2021 :  10:16:50  
Hello
I have been through the table looking for any defects in the code and taken out some blank rows.

I updated the database and seem to have inserted the Pid numbers but nothing else.

I have shortened the data list as below.

Updating row: 500, 1500, 5000, 5124
Rows successfully updated 0
Rows unchanged or where pID not found 1572
This page took: 1.4331 seconds
Rows where pID not found 1572
alp3033
alp3033-0001
alp3042
alp3042-0001
alp3042-0003
alp3046
alp3046-0004
alp3051
alp3051-0002
alp3051-0005
ash3088
ash3088-0002
ash3091
stu3224-0004
stu3224-0005
stu3225
stu3225-0001
stu3225-0002
stu3225-0003
stu3225-0004
stu3225-0005
stu3226
stu3226-0001
stu3227
stu3227-0001
stu3227-0002
stu3227-0003
stu3227-0004
stu3228
stu3228-0001
stu3228-0002
stu3228-0003
stu3228-0004
Update Successful !
Please click here for the admin home page.
No idea what to do next so at a stand still.

John



   Topic   
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Shopping Cart Software Forum for Ecommerce Templates © 2002-2020 ecommercetemplates.com
This page was generated in 0.03 seconds. Snitz Forums 2000