From what you are describing it sounds like you needs to provide the BULK INSERT command a format file so it knows how to parse your data. You can use the bcp command to auto-generate one for you, however I have always had to edit them afterwards before they work properly. (Lots of testing I am afraid).
The links below should be good starting points for you:
Format Files:
http://msdn.microsoft.com/en-us/library/ms190393.aspx
BCP:
http://msdn.microsoft.com/en-us/library/ms162802.aspx
BULK INSERT:
http://msdn.microsoft.com/en-us/library/ms188365.aspx
EXAMPLE:
http://msdn.microsoft.com/en-us/library/ms178129.aspx
I hope this helps you.
EDIT
Based on the extra information that you have added above it looks like you have set all field terminators to pipes, which is not what you want. You will need to edit the format file on a per column basis.
For instance, in the example above your first two columns are delimited by a semi-colon. The third would need to be delimited by ";|". Here is an example of what I mean for the first few columns:
9.0
18
1 SQLCHAR 0 510 ";" 1 web1numdoss
2 SQLCHAR 0 510 ";" 2 web1dem
3 SQLCHAR 0 510 ";|" 3 web1def
...
18 SQLCHAR 0 510 "\r\n" 18 Col018
Making use of Jeff Moden's Tally-Ho! CSV splitter from here:
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just
-- once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final
-- element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
go
We can code the solution as an apply against Jeff's function and a pivot like so:
with data as (
select Code,Location,Quantity,Store from ( values
('L698-W-EA', NULL, 2, 'A')
,('L82009-EA', 'A1K2, A1N2, C4Y3, CBP2', 2, 'A')
,('L80401-A-EA', 'A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20', 17,'A')
,('CWD2132W-BOX-25PK', 'A-AISLE', 1, 'M')
,('GM22660003-EA', 'B1K2', 1, 'M')
)data(Code,Location,Quantity,Store)
)
,shredded as (
select Code,Location,Quantity,Store,t.*
from data
cross apply [dbo].[DelimitedSplit8K](data.Location,',') as t
)
select
pvt.Code,pvt.Quantity,pvt.Store
,cast(isnull(pvt.[1],' ') as varchar(8)) as Loc1
,cast(isnull(pvt.[2],' ') as varchar(8)) as Loc2
,cast(isnull(pvt.[3],' ') as varchar(8)) as Loc3
,cast(isnull(pvt.[4],' ') as varchar(8)) as Loc4
,cast(isnull(pvt.[5],' ') as varchar(8)) as Loc5
,cast(isnull(pvt.[6],' ') as varchar(8)) as Loc6
from shredded
pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5],[6])) pvt;
;
go
yielding this:
Code Quantity Store Loc1 Loc2 Loc3 Loc4 Loc5 Loc6
----------------- ----------- ----- -------- -------- -------- -------- -------- --------
L698-W-EA 2 A
L82009-EA 2 A A1K2 A1N2 C4Y3 CBP2
L80401-A-EA 17 A A1S2 SHIP R2F1 CBP5 BRP BRP1-20
CWD2132W-BOX-25PK 1 M A-AISLE
GM22660003-EA 1 M B1K2
Best Answer
On your BCP command use the -t switch to specify which field delimiter you want to use
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15