How to remove " from txt-file csv import SQL

Hi!

First i create a table in MS SQL:

create table beliggenhet
(Beliggenhetsnummer int UNIQUE, 
 NavnKort varchar(2),
 nabn varchar(30));
 Postsluttmarkert varchar(1));

Open in new window


That code is working fine...


Textfile lock like this:

1,"This is a test","Is this working","AQ"
2,"Working fine","Or not","AX"

The text file must lock like:
1,This is a test,Is this working,AQ
2,Working fine,Or not,AX



But the code :

BULK
 
INSERT beliggenhet
FROM 'C:\testimport\beligg.txt'
WITH
(
firstrow = 1,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
 
)
 
go

Open in new window


give me alot of errors.
This distributions is " in the file ...

How do i remove " from file ?

Onother problem is that, next time i read the table. It maby content
a field that alredy exist in the table beliggenhet. So it must just update the
table, not Insert..

Hope sombody can help me solve this issue.
LVL 2
team2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

team2005Author Commented:
Hi!

Have found this Stored Procedure that do the trick.

ALTER PROCEDURE "dbo"."ps_StudentList_Import"
@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint
AS


DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  
  SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
  
  SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
 END


EXEC (@SQL)


INSERT StudentList (StFName,StLName,StEmail,OrderID)
SELECT  CASE WHEN @FileType = 1 THEN SUBSTRING(StFName,2,DATALENGTH(StFName)-1)
             ELSE StFName
        END,
        SUBSTRING(StLName,1,DATALENGTH(StLName)-0),
        CASE WHEN @FileType = 1 THEN SUBSTRING(StEmail,1,DATALENGTH(StEmail)-1)
             ELSE StEmail
        END,
        @OrderID
FROM tmpStList


TRUNCATE TABLE TmpStList

Open in new window



Using this datafile:(textfile)

"Kelly 2","Reynold","kelly@reynold.com"
"John 2","Smith","bill@smith.com"
"Sara 2","Parker","sara@parker.com"

After execting the Stored Procedure, the result in table -> StudentList are:

Kelly 2,Reynold,kelly@reynold.com
John 2,Smith,bill@smith.com
Sara 2,Parker,sara@parker.com


But if i trye to insert a integer field in the code (added a field test1 in DB)
The tryed code dosent run ?

ALTER PROCEDURE "dbo"."ps_StudentList_Import"
@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint
AS


DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  
  SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
  
  SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
 END


EXEC (@SQL)


INSERT StudentList (test1,StFName,StLName,StEmail,OrderID)
SELECT  
        @test1
        CASE WHEN @FileType = 1 THEN SUBSTRING(StFName,2,DATALENGTH(StFName)-1)
             ELSE StFName
        END,
        SUBSTRING(StLName,1,DATALENGTH(StLName)-0),
        CASE WHEN @FileType = 1 THEN SUBSTRING(StEmail,1,DATALENGTH(StEmail)-1)
             ELSE StEmail
        END,
        @OrderID
FROM tmpStList


TRUNCATE TABLE TmpStList

Open in new window


What is wrong with the code ?
0
team2005Author Commented:
Hi!

Found out of this myself.

ALTER PROCEDURE "dbo"."KJE_Bransjer_importorter"
@PathFileName varchar(100),
@FileType int
AS


DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  SET @SQL = "BULK INSERT dbo.KJE_BRANSJER_TMP FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
  SET @SQL = "BULK INSERT dbo.KJE_BRANSJER_TMP FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
 END


EXEC (@SQL)


UPDATE dbo.KJE_BRANSJER_TMP 
SET Navnkort =SUBSTRING(Navnkort,2,LEN(Navnkort)-2),
    Navn =SUBSTRING(Navn,2,LEN(Navn)-2),
	Status =SUBSTRING(Status,2,LEN(Status)-2)

	

UPDATE dbo.KJE_BRANSJER
SET dbo.KJE_BRANSJER.Nummer = dbo.KJE_BRANSJER_TMP.Nummer,
dbo.KJE_BRANSJER.Navnkort = dbo.KJE_BRANSJER_TMP.Navnkort,
dbo.KJE_BRANSJER.Navn = dbo.KJE_BRANSJER_TMP.Navn,
dbo.KJE_BRANSJER.Status = dbo.KJE_BRANSJER_TMP.Status


FROM dbo.KJE_BRANSJER_TMP
INNER JOIN dbo.KJE_BRANSJER
   ON dbo.KJE_BRANSJER_TMP.Nummer = dbo.KJE_BRANSJER.Nummer


INSERT INTO dbo.KJE_BRANSJER (Nummer,Navnkort, 
Navn,Status)

SELECT dbo.KJE_BRANSJER_TMP.Nummer,dbo.KJE_BRANSJER_TMP.Navnkort, 
dbo.KJE_BRANSJER_TMP.Navn,dbo.KJE_BRANSJER_TMP.Status

FROM dbo.KJE_BRANSJER_TMP
LEFT OUTER JOIN dbo.KJE_BRANSJER
   ON dbo.KJE_BRANSJER_TMP.Nummer = dbo.KJE_BRANSJER.Nummer
WHERE dbo.KJE_BRANSJER.Nummer IS NULL



TRUNCATE TABLE dbo.KJE_BRANSJER_TMP

Open in new window


This code wil olso update :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.