Solved

How to remove " from txt-file csv import SQL

Posted on 2012-03-15
2
235 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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