Hi – I thought I would do a post regarding parsing the Solidworks Item Number. In my travels I’ve often run into this issue and it you use this code you can create a parent component relationship with the qty per from a Raw Export.
Note that your input field would have the following fields. The table NewBOM was created on SQL Server by importing the Excel Sheet using Import/Export.
QTY PART DESCRIPTION NOTE ZONE ITEM
Here it is:
SELECT p.[ITEM] AS PNum, LTRIM(p.Part) AS PARPRT_02, c.CNum, LTRIM(c.COMPRT_02) as COMPRT_02, c.[QTY]
FROM
(SELECT LEFT([ITEM], LEN([ITEM]) – PATINDEX(‘%.%’, REVERSE([ITEM]))) AS PNum, [ITEM] AS CNum, Part AS COMPRT_02, [QTY]
FROM Newbom) AS c
INNER JOIN NewBOM AS p
ON p.[ITEM] = c.PNum
WHERE p.Part <> c.COMPRT_02
UNION
SELECT pp.[ITEM], pp.PARPRT, cc.[ITEM], cc.COMPRT, cc.[QTY]
FROM
(SELECT C1.[ITEM], C1.COMPRT, C1.[QTY]
FROM
(SELECT COMPRT, [ITEM], c.[QTY]
FROM
(SELECT [ITEM], CASE WHEN len([ITEM]) = 1 AND [ITEM] <> 0 THEN Part END AS COMPRT, [QTY]
FROM NewBOM) AS C
WHERE c.COMPRT IS NOT NULL) AS C1) AS cc CROSS JOIN
(SELECT P1.[ITEM], P1.PARPRT
FROM
(SELECT p.PARPRT, p.[ITEM]
FROM
(SELECT [ITEM], CASE WHEN len([ITEM]) = 1 AND [ITEM] = 0 THEN Part END AS PARPRT
FROM NewBOM) AS p
WHERE p.PARPRT IS NOT NULL) AS P1) AS PP