Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL 2014 Text Qualifier for Double quotes

Author  Topic 

DisasterFaster
Starting Member

2 Posts

Posted - 2014-12-18 : 16:51:22
Hello, I am brand new to SQL 2014 and have a problem I am unable to resolve. I am attempting to use the Import Wizard to import data from a .txt flat file. I have columns that looks like this on the flat file:

81181,8924750,4,1011,"00083212","D",1954-08-17 00:00:00,,"Perpetual","","MO","","","","BROWN, "Skipper" CHARLEY"

As you can see this is comma delimited but on some columns it also has a double quote text qualifier. My problem is on the last column where we are comma delimited with a double quote text qualifier and then within the first and last double quote qualifier we have a comma as well as extra double quotes.

I want the imported data set to look like this:

BROWN, "Skipper" CHARLEY

Problem is, the import algorithm has no way to understand which double quotes are truly qualifiers and which ones I want to stay haha.

Any suggestions on how to achieve this would be greatly appreciated!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-12-18 : 17:08:47
Change your input data either by escaping the double quotes and inserting double quotes where appropriate, for example, "BROWN", """Skipper"" CHARLEY" as the input data, or by using a different delimiter such as the pipe character.
Go to Top of Page

DisasterFaster
Starting Member

2 Posts

Posted - 2014-12-18 : 17:50:15
Thank you for helping out James! I was already in progress with your suggestion to change out those items. It's a bit of a hassle since this .txt file is over 2 million rows long lol! What gets me is why the DB admin allowed the data to be inputted like that in the first place argh. It creates a real headache to rebuild the thing oh well...
Go to Top of Page
   

- Advertisement -