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.
| Author |
Topic |
|
GTaber
Starting Member
3 Posts |
 Posted - 2015-02-03 : 14:41:45
|
| I am trying to export data from SQL Express into a .CSV file, without having to ommit the columns that have Extra CR LF's in them. I can generate the .csv file but it won't import or parse properly into SQL 2012. All the problem columns are free text and quite large so users have hit CR plenty of times within problematic fields.My working BCP Command Looks like this below i am using "," as the normal column delimiter:bcp "SELECT quotename(ID,CHAR(34)), quotename(Name,CHAR(34)), quotename(Date,CHAR(34)), quotename(Smallcomment,CHAR(34)) FROM database.dbo.table ORDER BY Date" queryout C:\filename.csv -c -CACP -t, -S servername\instance -U readonlyuser -P readonlyuserpassThe results are perfect, and even this 'Smallcomment' field is a free text string and users are putting ',' commas, '/' slashes, ':' colons all kinds of junk, but it never has an issue. The next group of about 5 more columns after this one, are much larger in size and all have the same commas, slashes and colons and junk in them, but i see consistent breaks in what appears to be SPACES in Notepad, but I See CR LF symbols using Notepad++Statements/Questions: I am pretty sure there are CR LF's being hand entered in the larger comments columns breaking my logic?How can I ignore those specific CR and LF's in those specific columns which I have identified to keep my Carriage Return and or Line Feed for my Row Delimiter?Below is an example of good results coming out:ID Col "1111","name1, name2 (AAA: 01/01/01 ID: 1111)","Jan O1 1001 10:00AM",,"","","","","","","","","","3","Name Name, Title",""CRLFThe bad results basically show Several CR LF characters within those larger columns and starts putting all the rows in separate lines, thus making it impossible to separate the columns when we import the file.How can I make those trouble columns ignore CR and LF and only utilize the ones after the last column I need to go to the next row?I have screenshots to compare the result differences in Notepad ++ w/All Char View Below:good data http://tinyurl.com/obj8pgfbad data http://tinyurl.com/owzbwje |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-03 : 14:44:42
|
| You can use the REPLACE function in your bcp query to remove the CRLFs, which are CHAR(10) and CHAR(13). REPLACE(REPLACE(column1, CHAR(10), ''), CHAR(13), '')Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
BCP Export to .CSV and Manage CR and LF issues