Is this straight TSSQL or are you doing CF? Can you do a replace() on the double-quotes in each string/substring? I just finished a similar utility, but I specified a tab-delimited text file as the import, implemented a customization of the BufferedReader java class I found on macromedia.com; I replace bad characters, trim and test for empty strings, then build a dynamic built sql string that inserts only the fields that aren't "empty" and executed the query using PreserveSingleQuotes(). I know what you're going through: that's why I specified tab-delimited; comma-delimited files are notoriously different accg to any program/programmer who builds them -- sometimes with double-quotes around every text value, sometimes single-quotes, sometimes only around values that contain interior commas.
----- Original Message ----- From: "Scott Weikert" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Friday, March 14, 2003 3:46 PM Subject: BULK INSERT and fields surrounded by double quotes > Trying to get my head around an issue with a project. > > I've got a page where a client can upload a comma-delimited file, which I > then scan over for total columns, build up a text file with the format > information for the BULK INSERT call, and then do the bulk insert. All this > works great. > > But often, the files uploaded by the client have fields surrounded by > double quotes, i.e. > > "Joe","Blow","[EMAIL PROTECTED]" > > As it sits, the bulk insert puts in the full fields - including the quotes. > > I dynamically create a view of the target table, using LTRIM and RTRIM on > each field (to get rid of leading/trailing spaces), and I'd like to be able > to do the same as far as ditching any double quotes, should they be > present. But I'm having trouble getting my head around how to do this. > > Is there a way to get BULK INSERT to ditch the double quotes in the first > place? And if not, how to work my view to get rid of said quotes? > > Thanks much in advance- > --Scott > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
