Bulk Insert Help

[ababwah]

I am trying to do a Bulk insert of a CSV file into SQL. I tested on dummy database using a created CSV file and got it working.

But the actual CSV file that has arrived has " around the name
eg "Hollywood". The Database has a fixed field length of 9 therefore the convert fails as the above name is 11 characters.

Is there a way of deleting the " before it tries to enter the field in the database. My Current Query looks something like this.

Bulk Insert <TableName>
FROM 'C:\Filepath.CSV'
WITH
(Datafiletype = 'Char'
,FIELDTERMINATOR = ',')

Any Help would be Coooool Thanks

 

[yksvaan]

If you would use dts text import conenction, there is a text qualifier definition. But I think it doesn't exist in bulk/bcp.

So, as I said to workaround this you would have to use dts text connection (import). Of course, there are other ways.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[ababwah]

Hi Mate

Thanks for the reply
But I need to do this from Bulk Insert and not DTS.

 

[Catadmin]

Can't you pull the CSV file into an Excel spreadsheet or Word Doc and do a mass Replace on the " to nothing, then save it again as a CSV file again and import the new file?

I know it's extra work, but I believe Bulk Insert doesn't have the capability of altering data in any way. Hence the need SQL Server had for DTS.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!