January 12, 2011 at 9:12 pm
Hi ,
I recently came across with bulk insert , i need your help to resolve them. i have the following CSV file content and move them into SQL Server 2008 R2 table.
My CSV content
DN,test,test1,test2
"CN=p0725622,OU=ABE,OU=Users,OU=CIS,DC=sd,DC=sp,DC=edu,DC=sg",,,,
i have tried the basic bulk insert but it is not inserting the column properly. the first column "CN=p0725622,OU=ABE,OU=Users,OU=CIS,DC=sd,DC=sp,DC=edu,DC=sg" spited into other column.
it supposed to give me the other column as null.
Note : My tables column also accept null.
i have used the following code to push it
FROM 'c:\test.csv'
WITH
(
FIELDTERMINATOR = ',',
firstrow =2,
ROWTERMINATOR = ''
)
i don't want to use the format file also import CSV also i don't want to , because i need to achieve them on the SQL Script.
I have also tried the OPENROWSET which also failed to achieve my goal.
Please give me your suggestion to achieve this.
Regards,
Subbu
Click here to Get Speedy answer or solution
January 12, 2011 at 9:24 pm
Can you attach a sample file with data
January 12, 2011 at 9:36 pm
Hi,
Please find the sample file with the data , file has only one row.
due to the constraint on csv upload on this site , i have save my csv into txt file and attached the same.
Regards,
Subbu
Click here to Get Speedy answer or solution
January 12, 2011 at 10:18 pm
--try this first create the function
create FUNCTION [dbo].[fn_SmallSplitVarChar] (@sText VARCHAR(8000),
@sDelim VARCHAR(20))
RETURNS @ret TABLE(
Value varchar(800)
)
AS
BEGIN
DECLARE @i INT
SELECT @i = 1
DECLARE @j-2 INT
WHILE @i <= LEN(@sText) + 1
BEGIN
SELECT @j-2 = CASE
WHEN CHARINDEX(@sDelim, @sText, @i) <= 0 THEN
DATALENGTH(@sText) + 1
ELSE
CHARINDEX(@sDelim, @sText, @i)
END
INSERT INTO @ret (Value)
VALUES (LTRIM(RTRIM(SUBSTRING(@sText, @i, @j-2 - @i))))
SELECT @i = @j-2 + DATALENGTH(@sDelim)
END
RETURN
END
--then
DECLARE @LotsOfText VARCHAR(MAX)
SELECT @LotsOfText = BulkColumn
FROM OPENROWSET(BULK N'C:\temp\1.csv' , SINGLE_BLOB) AS x
select * from dbo.fn_SmallSplitVarChar(@LotsOfText,Char(13) + Char(10)) rows
January 12, 2011 at 10:19 pm
--and this will give you every row as a table
DECLARE @LotsOfText VARCHAR(MAX)
SELECT @LotsOfText = BulkColumn
FROM OPENROWSET(BULK N'C:\temp\1.csv' , SINGLE_BLOB) AS x
DECLARE @row VARCHAR(50) -- database name
DECLARE db_cursor CURSOR FOR
select * from dbo.fn_SmallSplitVarChar(@LotsOfText,Char(13) + Char(10))
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @row
WHILE @@FETCH_STATUS = 0
BEGIN
select * from dbo.fn_SmallSplitVarChar(@row,',')
FETCH NEXT FROM db_cursor INTO @row
END
CLOSE db_cursor
DEALLOCATE db_cursor
January 13, 2011 at 1:55 am
Please give feedback
January 13, 2011 at 12:09 pm
hi thank you for your code , still this code does not give me the exact output as i said earlier , i have 3 column have null value still this code is not help me to extract the csv properly
please help me if you have any other solution.
Regards,
Subbu
Click here to Get Speedy answer or solution
January 14, 2011 at 1:11 am
Hi
I managed to fix this issue and i use OPENROWSET to import CSV files into SQL Server 2008 R2
Regards,
Subbu
Click here to Get Speedy answer or solution
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply