BULK INSERT question

[cmhunt]

Hi

I have a standard CSV file which has double quote enclosed fields. How do I specify in BULK INSERT that fields are quote enclosed. Currently

BULK INSERT #TestTable FROM 'F:\*****\Production\Test Files\file.csv'
WITH (
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)

works fine but leaves the quotes in.

Any help would be gratefully appreciated.

Thanks

Chris

 

[cLFlaVA]

I could not find a way to do this, and had to revert to making the CSV file without quotation marks as text identifiers...

*cLFlaVA
----------------------------
Breaking the habit...

 

[cmhunt]

Can't believe it's the easiest way to do it but this drops the first and last character off every field in the table:

DECLARE @MaxFields AS int,@FieldCounter AS int, @TableID AS int, @SQL AS varchar(4000), @FieldName AS varchar(4000)
SET @FieldCounter=1
SET @MaxFields=(SELECT MAX(colid) FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='TestTable')) + 1
SET @TableID=(SELECT id FROM sysobjects WHERE name='TestTable')
WHILE @FieldCounter < @MaxFields
	BEGIN
		SET @FieldName=(SELECT Name FROM syscolumns WHERE id=@TableID AND colid = @FieldCounter)
		SET @SQL = 'UPDATE TestTable SET ' + @FieldName + ' = SUBSTRING(' + @FieldName + ',2,LEN(' + @FieldName + ')-2)'
		EXECUTE(@sql)
		SET @FieldCounter = @FieldCounter + 1
	END

Anyone know if I can do this with a temp table??

Thanks

Chris