Solved

How to remove " from txt-file csv import SQL

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now