Hi All,
I am doing BCP out to text file notepad, looks like some rows are getting divided into two or multiple rows.
Is there anything I need to add in the code to format it.
I have comma some special characters inside the field. Ex. This test is for, dba team @, got success #.
exec master..xp_cmdshell 'bcp "select * from [db].dbo.[tbl_test123]" queryout "\\x.x.x.x\dba\BCP_Out\tbl_test123.txt" -S"server name" -U"user" -P"password" -n -c -t^| -T -o"\\x.x.x.x\dba\tbl_test123_log.txt"'
January 4, 2023 at 5:22 pm
Does any of the data itself contain a control code for a carriage return or line feed?
It's also a really bad thing to include logins and passwords in clear text. Learn how to use Active Directory for your service logins and trusted connections. This is a part of the reason why people say xp_CmdShell is a "security risk". It's not... the way people improperly use it is.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2023 at 9:09 am
reason why you have your records split like that is because the data on those columns contain a CR or a LF or both - in order to be able to read them (in a program that understands CSV files) you will need to double quote those fields as well as escape any double quote on its content (with another double quote)
so your select would become
select field1, field2, quotename(replace(field3, '"','""'), '"') as field3 from tbl
in notepad it will still show in multiple lines - but if you open it with excel the content should be on its own cell (with multiple lines within the cell)
or if you open it with SSIS (after defining the file with correct delimiters and with quoted fields) the viewer will also show the correct info.
and now the question .. what is this file going to be used for? who/what is going to read them and process it?
January 5, 2023 at 12:44 pm
Thanks for the CR LF note, I am trying to bcp out from mssql and load to mysql.
January 5, 2023 at 1:53 pm
Thanks for the CR LF note, I am trying to bcp out from mssql and load to mysql.
Does that mean you're all set now?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2023 at 2:16 pm
No Guru, I am doing test, I tried to use quotename(field3,CHAR(34)) still in the output i am seeing split. I think quotename is not working.
To add: the split comes with ""
ex:
"Test A
TestB
TestC"
This should be one one line as per SQL table - Test A Test B Test C. This is just example.
January 5, 2023 at 3:44 pm
Are you familiar with quote qualified delimited files?
Text editors generally don't care about quote qualified which is why you're seeing the line breaks in Notepad++(if you turn on the View -> Show Symbols -> Show end of line you'll see exactly which line breaks are in your data) Then you'll have to determine whether you need to strip them out at query time of whether whatever system you're passing the data to can handle it.
January 5, 2023 at 4:45 pm
Use a different Row Delimiter
Far away is close at hand in the images of elsewhere.
Anon.
January 5, 2023 at 4:56 pm
Thanks for the CR LF note, I am trying to bcp out from mssql and load to mysql.
if that is the case then doing what I stated is what you need to do - you may or not need to change the row delimiter - first try out doing the enclosing of fields in double quotes and them use the mysql load data command (I assume you are using this one to load the data) using fields terminated by "|" (the delimiter you used above), enclosed by '"' escaped by '"'
another option is to use powershell/c# to use the mysqlbulkloader (where you retrieve data from sql on a recordset and send directly to mysql) (see https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html - anything you can do in C# you can also do in powershell)
January 5, 2023 at 10:06 pm
The other option is to convert those CR and LF to text values that you can then convert after importing. If you use a tab delimited - or pipe delimited file and convert CR, LF, TABS - then the files should easily be imported to a staging table where you can then run a replace to convert them back to original characters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2023 at 4:45 am
HI,
fields terminated by "|" (the delimiter you used above), enclosed by '"' escaped by '"'
I have used the same test. But wherever there is a split it load as zero and ID column numbers are not correct - PFA.
LOAD DATA INFILE 'G:/Data_txt_files/db/L/tbl.txt' INTO TABLE tbl FIELDS TERMINATED BY '|' enclosed by '"' escaped by '"' (col1,col2,col3,col4,col5,col6);
January 6, 2023 at 4:49 am
The other option is to convert those CR and LF to text values that you can then convert after importing. If you use a tab delimited - or pipe delimited file and convert CR, LF, TABS - then the files should easily be imported to a staging table where you can then run a replace to convert them back to original characters.
Thank you, how can I do this on notepad ++.
HI,
fields terminated by "|" (the delimiter you used above), enclosed by '"' escaped by '"'
I have used the same test. But wherever there is a split it load as zero and ID column numbers are not correct - PFA.
LOAD DATA INFILE 'G:/Data_txt_files/db/L/tbl.txt' INTO TABLE tbl FIELDS TERMINATED BY '|' enclosed by '"' escaped by '"' (col1,col2,col3,col4,col5,col6);
apologies - I assumed you knew you had to pick a different line separator on the load data.
add LINES TERMINATED BY '\r\n' to the above command.
and to be sure sure sure on your source table issue the following sql
select top 10 *
from mytable
where field like '%' + char(13) + char(10) + '%'
this will tell you if any of the contents contains CRLF (instead of just LF as on your example)
if you have any instances on your data then you can use another load data construct which allows you to set the contents of a column to an expression - in this case you would replace the CRLF on the extract sql with a string (^^^ for example) and then on the load you would set the column to a replace('^^^', '\r\n').
see manual at https://dev.mysql.com/doc/refman/8.0/en/load-data.html, input processing section
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply