?
Solved

How to remove " from txt-file csv import SQL

Posted on 2012-03-15
2
Medium Priority
?
239 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
[X]
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
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

765 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