|
By shulemj - Thu 30 Jun 2016
|
Can someone tell me what I'm doing wrong?
Log:
| Data File Update | Info | ======= Data Update Started on 6/29/2016 11:19:15 PM... ======== | | Data File Update | Info | Import Option: ADD IMPORTED DATA TO EXISTING DATA AND IGNORE DUPLICATES | | Data File Update | Info | Processing file... | | Data File Update | Info | Processing data sheet... | | Data File Update | Error | line skipped V_CodeNumber 2 - Error Reading category/product data! Skipping... - Record #1 | | Data File Update | Error | line skipped V_CodeNumber 2 - Error Reading category/product data! Skipping... - Record #2 | | Data File Update | Error | line skipped V_CodeNumber 2 - Error Reading category/product data! Skipping... - Record #3 | | Data File Update | Info | Processed 0 line records | | Data File Update | Error | 3 lines skipped | | Data File Update | Info | ======= Summary ======= | | Data File Update | Info | 0 new categories | | Data File Update | Info | 0 new products | | Data File Update | Info | 0 new versions | | Data File Update | Info | 0 new attributes | | Data File Update | Info | 0 existing categories updated | | Data File Update | Info | 0 existing products updated | | Data File Update | Info | 0 existing versions updated | | Data File Update | Info | 0 attribute values added/updated | | Data File Update | Info | ======= Data Update finished on 6/29/2016 11:19:15 PM. Total Elapsed Time: 0 mins. ====== |
|
|
By Tiggywiggler - Thu 30 Jun 2016
|
Can you please post a copy of the data you are trying to import please? It looks like it is only three lines so it should be easy enough.
If you are trying to import product data, are you sure that the categories that they are attached to actually exist?
|
|
By shulemj - Thu 30 Jun 2016
|
|
Here's the file. All categories exist.
|
|
By Tiggywiggler - Thu 30 Jun 2016
|
I don't have access to the data tool source code, however, I get the same error and I have seen that if I delete the values in column V_CodeNumber I get a different fault:
line skipped V_CodeNumber 0 - Error Reading V_CodeNumber! Skipping... - Record #1
If I type rubbish data into that column such as 1, 2, 3. Then I get the same fault you are getting again.
I am sure that the fault is with this column, but without being able to read the code I am not sure what is causing it.
Can you tell me how you exported your data in the first place to get this Excel document please?
|
|
By shulemj - Thu 30 Jun 2016
|
I manually created the excel file to test the functionally before I create a full feed.
The reason you get the error V_CodeNumber 0 - Error Reading V_CodeNumber! is because it's missing the required SKU.
BTW, I'm running DataTool v2.904, which seems to be the most recent.
|
|
By Tiggywiggler - Thu 30 Jun 2016
|
I understand missing the required SKU, what I don't understand is that no matter what SKU you put in there it always throws an error. I'm not sure if you have to put an existing SKU in there (wouldn't make sense for importing new products).
Sorry mate, I am not any help to you.
|
|
By shulemj - Thu 30 Jun 2016
|
|
Wait a minute, isn't this a feature to upload new products? Maybe this is the issue. I uploaded products that are not yet in the DB.
|
|
By Tiggywiggler - Thu 30 Jun 2016
|
Okay, I think I have cracked it mate.
You know in the spreadsheet is starts with Cat 5 on the far left and then decreases down through Cat 4, 3, 2 to Cat 1? Well Cat 5 is the top of the tree, not Cat 1 as you would think.
So this means you need to take everything you have under Cat 5 and put it under Cat 1.
Try that and let me know how you get on.
I have added a copy of your file updated (I changed the SKU numbers and removed the category which was 'v' and blanked it too, just because I was messing around and that's where I got to).
|
|
By Tiggywiggler - Thu 30 Jun 2016
|
Okay, I think I have cracked it mate.
You know in the spreadsheet is starts with Cat 5 on the far left and then decreases down through Cat 4, 3, 2 to Cat 1? Well Cat 5 is the top of the tree, not Cat 1 as you would think.
So this means you need to take everything you have under Cat 1 and put it under Cat 5.
Try that and let me know how you get on.
I have added a copy of your file updated (I changed the SKU numbers and removed the category which was 'v' and blanked it too, just because I was messing around and that's where I got to).
|
|
By shulemj - Thu 30 Jun 2016
|
You're definitely on to something, as moving CAT1 to CAT5 (and CAT2 to CAT4) allowed the upload with no error.
Two fails though:
1. I didn't know that bey leaving the Cat5_Desc1 field blank, it actually deleted whatever description that category had. I wish there's some sort of documentation for the excel format.
2. The products were only added under the Cat5_Name1, but not under Cat4_Name1. It was ignored, with no error in the log.
|
|
By Tiggywiggler - Thu 30 Jun 2016
|
I tried to pick apart the example spreadsheets to try and understand how it would all work. It takes a bit of wrapping your head around but I am sure with a bit of work it would be possible. Hopefully you manage to find success.
I think that it is now a matter of trial and error, but if you find something specific that you want help with after this, please let me know.
|
|
By shulemj - Thu 30 Jun 2016
|
|
Thanks for your help
|
|
By shulemj - Thu 30 Jun 2016
|
|
Thanks for your help
|
|
By Paul - Thu 30 Jun 2016
|
Funnily enough, I have been working on fixing the Products for Data tool export from the db admin section today because it puts the categories in the wrong order.
Have it working, the new sql update should be on the repository tomorrow when I get confirmation it's working right for a client. I'll try to remember to update this thread then with details.
Meanwhile, here is the new sproc I have, which is *probably* the final one.
****** Object: StoredProcedure [dbo].[_spKartrisDB_ExportProductRelatedData] Script Date: 30/06/2016 16:22:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paul
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[_spKartrisDB_ExportProductRelatedData]
(
@LanguageID as int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Put products from View into temp table
SELECT * INTO #TempProducts FROM dbo.vKartrisTypeProducts WHERE LANG_ID = @LanguageID;
-- apply index
CREATE CLUSTERED INDEX IDX_P_ID ON #TempProducts(P_ID);
CREATE INDEX IDX_LANG_ID ON #TempProducts(LANG_ID);
-- Put versions from View into temp table
SELECT * INTO #TempVersions FROM dbo.vKartrisTypeVersions WHERE LANG_ID = @LanguageID;
-- apply indexes
CREATE CLUSTERED INDEX IDX_V_ID ON #TempVersions(V_ID);
CREATE INDEX IDX_V_ProductID ON #TempVersions(V_ProductID);
CREATE INDEX IDX_LANG_ID ON #TempVersions(LANG_ID);
-- Put categories from View into temp table
SELECT * INTO #TempCategories FROM dbo.vKartrisTypeCategories WHERE LANG_ID = @LanguageID;
-- apply indexes
CREATE CLUSTERED INDEX IDX_CAT_ID ON #TempCategories(CAT_ID);
CREATE INDEX IDX_LANG_ID ON #TempCategories(LANG_ID);
-- Put category hierarchy from View into temp table
SELECT * INTO #TempCategoryHierarchy FROM dbo.vKartrisCategoryHierarchy WHERE LANG_ID = @LanguageID;
-- apply indexes
CREATE CLUSTERED INDEX IDX_CH_CAT_ID ON #TempCategoryHierarchy(CAT_ID);
CREATE INDEX IDX_CH_ChildID ON #TempCategoryHierarchy(CH_ChildID);
CREATE INDEX IDX_LANG_ID ON #TempCategoryHierarchy(LANG_ID);
-- Put attributes from View into temp table
SELECT * INTO #TempAttributes FROM dbo.vKartrisTypeAttributes WHERE LANG_ID = @LanguageID;
-- apply indexes
CREATE CLUSTERED INDEX IDX_ATTRIB_ID ON #TempAttributes(ATTRIB_ID);
CREATE INDEX IDX_LANG_ID ON #TempAttributes(LANG_ID);
-- Put attributes from View into temp table
SELECT * INTO #TempAttributeValues FROM dbo.vKartrisTypeAttributeValues WHERE LANG_ID = @LanguageID;
-- apply indexes
CREATE CLUSTERED INDEX IDX_ATTRIBV_ID ON #TempAttributeValues(ATTRIBV_ID);
CREATE INDEX IDX_ATTRIB_ID ON #TempAttributeValues(ATTRIBV_AttributeID);
CREATE INDEX IDX_LANG_ID ON #TempAttributeValues(LANG_ID);
SELECT * INTO #TempProductData
FROM
((SELECT 'CAT1_NAME' AS Cat5_Name1, 'DO NOT MODIFY OR DELETE THIS LINE. THIS SHOULD HELP ADDRESS THE ISSUES WITH THE OLEDB DRIVER LIMITATION. BY ADDING THIS LINE WE ARE LETTING THE DRIVER KNOW THE CORRECT DATA TYPE OF EACH FIELD. THIS IS BETTER THAN MODIFYING THE REGISTRY TO SET A HIGHER ROWSCAN VALUE.' AS Cat5_Desc1, 'CAT1_IMAGE' AS Cat5_Image, 'CAT2_NAME' AS Cat4_Name1, '################################################################################################################################################################################################################################################################' AS Cat4_Desc1, 'CAT2_IMAGE' AS Cat4_Image, 'CAT3_NAME' AS Cat3_Name1, '################################################################################################################################################################################################################################################################' AS Cat3_Desc1, 'CAT3_IMAGE' AS Cat3_Image, 'CAT4_NAME' AS Cat2_Name1, '################################################################################################################################################################################################################################################################' AS Cat2_Desc1, 'CAT4_IMAGE' AS Cat2_Image, 'CAT5_NAME' AS Cat1_Name1, '################################################################################################################################################################################################################################################################' AS Cat1_Desc1, 'CAT5_IMAGE' AS Cat1_Image, 'P_NAME1' AS P_Name1, '################################################################################################################################################################################################################################################################' AS P_Desc1, 'P_IMAGEFIELD' AS P_Image, 'P_STRAPLINE1' AS P_StrapLine1, 'V_NAME1' AS V_Name1, '################################################################################################################################################################################################################################################################' AS V_Desc1, 'V_IMAGEFIELD' AS V_Image, 'V_CODENUMBER' AS V_CodeNumber, 'V_TYPE' AS V_Type, '0' AS V_Price, '0' AS V_Quantity, '0' AS V_Weight, '0' AS V_RRP, '0' AS T_Taxrate, '0' AS T_Taxrate2, 'V_TAXEXTRA' As V_TaxExtra, 'SUPPLIER' AS Supplier, 'ATTRIBUTES' AS Attributes, 'OPTIONS' AS Options)
UNION
(
SELECT COALESCE(#TempCategories.CAT_Name, '') AS Cat5_Name1, COALESCE(#TempCategories.CAT_Desc,'') AS Cat5_Desc1, '' AS Cat5_Image,
COALESCE(#TempCategoryHierarchy.CAT_Name, '') AS Cat4_Name1, COALESCE(#TempCategoryHierarchy.CAT_Desc, '') AS Cat4_Desc1, '' AS Cat4_Image,
COALESCE(vKartrisCategoryHierarchy_1.CAT_Name, '') AS Cat3_Name1, COALESCE(vKartrisCategoryHierarchy_1.CAT_Desc, '') AS Cat3_Desc1, '' AS Cat3_Image,
COALESCE(vKartrisCategoryHierarchy_2.CAT_Name, '') AS Cat2_Name1, COALESCE(vKartrisCategoryHierarchy_2.CAT_Desc, '') AS Cat2_Desc1, '' AS Cat2_Image,
COALESCE(vKartrisCategoryHierarchy_3.CAT_Name, '') AS Cat1_Name1, COALESCE(vKartrisCategoryHierarchy_3.CAT_Desc, '') AS Cat1_Desc1, '' AS Cat1_Image,
#TempProducts.P_Name AS P_Name1, #TempProducts.P_Desc AS P_Desc1, '' AS P_Image,
#TempProducts.P_StrapLine AS P_Strapline1, #TempVersions.V_Name AS V_Name1, #TempVersions.V_Desc AS V_Desc1,
'' AS V_Image, #TempVersions.V_CodeNumber, #TempVersions.V_Type, #TempVersions.V_Price, #TempVersions.V_Quantity,
#TempVersions.V_Weight, #TempVersions.V_RRP, dbo.tblKartrisTaxRates.T_Taxrate, COALESCE(#TempVersions.V_Tax2, 0), #TempVersions.V_TaxExtra, dbo.tblKartrisSuppliers.SUP_Name AS Supplier,
COALESCE(STUFF((SELECT ' || ' + CONVERT(NVARCHAR(max), #TempAttributes.ATTRIB_Name),
' {{' + CONVERT(NVARCHAR(max), #TempAttributeValues.ATTRIBV_Value) + '}}'
FROM #TempAttributeValues INNER JOIN
#TempAttributes ON #TempAttributeValues.ATTRIBV_AttributeID = #TempAttributes.ATTRIB_ID AND
#TempAttributeValues.LANG_ID = #TempAttributes.LANG_ID
WHERE (#TempAttributes.ATTRIB_Live = 1) AND (#TempAttributes.ATTRIB_ShowFrontend = 1) AND (#TempAttributeValues.ATTRIBV_ProductID = #TempProducts.P_ID)
ORDER BY #TempAttributes.ATTRIB_OrderByValue
FOR xml path('')
)
, 1
, 4
, ''), '') AS Attributes, '' AS Options
FROM dbo.tblKartrisTaxRates RIGHT OUTER JOIN
#TempVersions INNER JOIN
#TempProducts ON #TempVersions.V_ProductID = #TempProducts.P_ID AND
#TempVersions.LANG_ID = #TempProducts.LANG_ID ON dbo.tblKartrisTaxRates.T_ID = #TempVersions.V_Tax LEFT OUTER JOIN
dbo.tblKartrisSuppliers ON #TempProducts.P_SupplierID = dbo.tblKartrisSuppliers.SUP_ID LEFT OUTER JOIN
dbo.#TempCategories INNER JOIN
dbo.tblKartrisProductCategoryLink ON #TempCategories.CAT_ID = dbo.tblKartrisProductCategoryLink.PCAT_CategoryID LEFT OUTER JOIN
#TempCategoryHierarchy LEFT OUTER JOIN
#TempCategoryHierarchy AS vKartrisCategoryHierarchy_3 RIGHT OUTER JOIN
#TempCategoryHierarchy AS vKartrisCategoryHierarchy_2 ON vKartrisCategoryHierarchy_3.LANG_ID = vKartrisCategoryHierarchy_2.LANG_ID AND
vKartrisCategoryHierarchy_3.CH_ChildID = vKartrisCategoryHierarchy_2.CAT_ID RIGHT OUTER JOIN
#TempCategoryHierarchy AS vKartrisCategoryHierarchy_1 ON vKartrisCategoryHierarchy_2.LANG_ID = vKartrisCategoryHierarchy_1.LANG_ID AND
vKartrisCategoryHierarchy_2.CH_ChildID = vKartrisCategoryHierarchy_1.CAT_ID ON
#TempCategoryHierarchy.LANG_ID = vKartrisCategoryHierarchy_1.LANG_ID AND
#TempCategoryHierarchy.CAT_ID = vKartrisCategoryHierarchy_1.CH_ChildID ON
#TempCategories.LANG_ID = #TempCategoryHierarchy.LANG_ID AND
#TempCategories.CAT_ID = #TempCategoryHierarchy.CH_ChildID ON
#TempProducts.P_ID = dbo.tblKartrisProductCategoryLink.PCAT_ProductID
WHERE (#TempProducts.P_Type <> 'o')
)) ProductData
ORDER BY Cat5_Name1, P_Name1
--SELECT * FROM #TempProductData
SELECT * FROM (
(SELECT Cat5_Name1, Cat5_Desc1, '' As Cat5_Image, '' As Cat4_Name1, '' As Cat4_Desc1, '' As Cat4_Image, '' As Cat3_Name1, '' As Cat3_Desc1, '' As Cat3_Image, '' As Cat2_Name1, '' As Cat2_Desc1, '' As Cat2_Image, '' As Cat1_Name1, '' As Cat1_Desc1, '' As Cat1_Image, P_Name1, P_Desc1, P_Image, P_StrapLine1, V_Name1, V_Desc1, V_Image, V_CodeNumber, V_Type, V_Price, V_Quantity, V_Weight, V_RRP, T_Taxrate, T_Taxrate2, V_TaxExtra, Supplier, Attributes, '' As Options FROM #TempProductData WHERE Coalesce(Cat5_Name1, '')<>'' AND Coalesce(Cat4_Name1, '')='' AND Coalesce(Cat3_Name1, '')='' AND Coalesce(Cat2_Name1, '')='' AND Coalesce(Cat1_Name1, '')='')
UNION
(SELECT Cat4_Name1, Cat4_Desc1, '', Cat5_Name1, Cat5_Desc1, '', '', '', '', '', '', '', '', '', '', P_Name1, P_Desc1, P_Image, P_StrapLine1, V_Name1, V_Desc1, V_Image, V_CodeNumber, V_Type, V_Price, V_Quantity, V_Weight, V_RRP, T_Taxrate, T_Taxrate2, V_TaxExtra, Supplier, Attributes, '' As Options FROM #TempProductData WHERE Coalesce(Cat5_Name1, '')<>'' AND Coalesce(Cat4_Name1, '')<>'' AND Coalesce(Cat3_Name1, '')='' AND Coalesce(Cat2_Name1, '')='' AND Coalesce(Cat1_Name1, '')='')
UNION
(SELECT Cat3_Name1, Cat3_Desc1, '', Cat4_Name1, Cat4_Desc1, '', Cat5_Name1, Cat5_Desc1, '', '', '', '', '', '', '', P_Name1, P_Desc1, P_Image, P_StrapLine1, V_Name1, V_Desc1, V_Image, V_CodeNumber, V_Type, V_Price, V_Quantity, V_Weight, V_RRP, T_Taxrate, T_Taxrate2, V_TaxExtra, Supplier, Attributes, '' As Options FROM #TempProductData WHERE Coalesce(Cat5_Name1, '')<>'' AND Coalesce(Cat4_Name1, '')<>'' AND Coalesce(Cat3_Name1, '')<>'' AND Coalesce(Cat2_Name1, '')='' AND Coalesce(Cat1_Name1, '')='')
UNION
(SELECT Cat2_Name1, Cat2_Desc1, '', Cat3_Name1, Cat3_Desc1, '', Cat4_Name1, Cat4_Desc1, '', Cat5_Name1, Cat5_Desc1, '', '', '', '', P_Name1, P_Desc1, P_Image, P_StrapLine1, V_Name1, V_Desc1, V_Image, V_CodeNumber, V_Type, V_Price, V_Quantity, V_Weight, V_RRP, T_Taxrate, T_Taxrate2, V_TaxExtra, Supplier, Attributes, '' As Options FROM #TempProductData WHERE Coalesce(Cat5_Name1, '')<>'' AND Coalesce(Cat4_Name1, '')<>'' AND Coalesce(Cat3_Name1, '')<>'' AND Coalesce(Cat2_Name1, '')<>'' AND Coalesce(Cat1_Name1, '')='')
UNION
(SELECT Cat1_Name1, Cat1_Desc1, Cat1_Image, Cat2_Name1, Cat2_Desc1, Cat2_Image, Cat3_Name1, Cat3_Desc1, Cat3_Image, Cat4_Name1, Cat4_Desc1, Cat4_Image, Cat5_Name1, Cat5_Desc1, Cat5_Image, P_Name1, P_Desc1, P_Image, P_StrapLine1, V_Name1, V_Desc1, V_Image, V_CodeNumber, V_Type, V_Price, V_Quantity, V_Weight, V_RRP, T_Taxrate, T_Taxrate2, V_TaxExtra, Supplier, Attributes, '' As Options FROM #TempProductData WHERE Coalesce(Cat5_Name1, '')<>'' AND Coalesce(Cat4_Name1, '')<>'' AND Coalesce(Cat3_Name1, '')<>'' AND Coalesce(Cat2_Name1, '')<>'' AND Coalesce(Cat1_Name1, '')<>'')
) As OutputProducts
END
GO
Few things about this... we used temporary tables because many of the views weren't indexed, and so putting data from the views into temptables let us index them and it ran a lot faster. There may be a better way, so open to suggestions.
The select statements near the end build up the final results and swap the category data around as required.
|
|
By shulemj - Fri 1 Jul 2016
|
Thanks Paul for pitching in, but I am not sure what you're trying to resolve or how it works.
I guess I'll wait until you come back tomorrow with more information.
|
|
By Tiggywiggler - Fri 1 Jul 2016
|
Thank you for this Paul,
I think the issue is that the user is trying to import new records that they have manually entered into the Excel spreadsheet, not export data from the database into the Excel sheet, which is what I think you are doing here.
I may be wrong, please correct me if so.
|
|
By Paul - Fri 1 Jul 2016
|
|
Could you pm me with a link to your export file so I can test with that?
|
|
By Paul - Fri 1 Jul 2016
|
1. The data tool will update products as much as it can from the values in the spreadsheet. If you think about it, it kind of makes sense that if you have a cat name, but blank description, the description should be blanked for that category. Consider that other users might have categories with descriptions they want to blank; if the data tool didn't blank them, these users would be asking how can they do that.
2. I've tested locally, exporting a spreadsheet from my database, then making changes such as changing the price of some items, or deleting an item from the Kartris back end, then importing the spreadsheet. It creates products fine for me under Cat4 as well as Cat5. Check you don't have duplicated SKUs, or product names in multiple places; the structure possible with DT imports is less flexibile than Kartris itself supports (for example, you can only have 5 levels of categories, product and category names must be unique, etc.).
|
|
By Tiggywiggler - Fri 1 Jul 2016
|
Paul,
The post one page 1 that has been marked as an answer has a file attached to it which is what we are using. The OP posted the file they were trying to use earlier in the thread.
|
|
By shulemj - Fri 1 Jul 2016
|
|
Here's the test file.
|
|
By shulemj - Fri 1 Jul 2016
|
1. I get the challenge, but a solution would be to enter a single space (" ") if blank description is desired over existing description. Remember, the category is either an existing one or a new one, and how many people would want to delete a nicely written description they have online, and leave it blank? Also, it is very impractical to include the category description on each product that you want to upload.
2. I'm sure that the system works file, but it seems that I'm missing something. Can you take a look on the very simple sample that I uploaded?
|
|
By shulemj - Fri 1 Jul 2016
|
|
Regarding the blank description issue, I just noticed that it was discussed here.
|
|