Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to remove " from txt-file csv import SQL

Posted on 2012-03-15
2
233 Views
Last Modified: 2012-03-26
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.
0
Comment
Question by:team2005
  • 2
2 Comments
 
LVL 2

Author Comment

by:team2005
ID: 37728798
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
 
LVL 2

Accepted Solution

by:
team2005 earned 0 total points
ID: 37766181
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question