asked on

BULK INSERT csv data file with quotations

Is it possible to import data from a CSV file using BULK INSERT and removing quotation marks around the data?

We have a application that exports to a cvs and I have no control over the formatting. Every data item is encased in quotaion marks and this is making it difficult to import the data using a stored procedure.

I realize that I can use DTS but I already have 12 different sprocs that import data and I don't really want to take take this one and only and "de-centralize" (so to speak) how data is imported into my database.

Thanks

Julianva

BULK INSERT tablename
FROM 'c:\filename.csv'
WITH
(
FIELDTERMINATOR =','
ROWTERMINATOR ='\n'
)

this will import the csv file

open file in excel then find quotation marks and replace with nothing then save an import the file

Keyman

ASKER

Well that certainly works but that is not exactly what I am looking for.

The csv is output automatically and I don't want to do any manual intervention with the file.

I am trying to keep everything as central as possible because I have so many different imports going on. I could have imported information in many different ways for each of the variouos datasources I must gather information from. Each one of the datasources may have had a more optimal way to get the data but I chose to use stored procedures that I call from Job in the sql server.

It is very centralized and calls for no manual intervention. It just makes things easier to troubleshoot when something goes wrong and requires someone less knowlegable than myself to potentially fix a problem when it happens.

That long winded story being said...I have to do the whole thing in a stored procedure.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Keyman

ASKER

Thanks