Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
We want to export SQL server table data into csv file to import into Redshift but for some of the columns export into csv is failing due to muli-lines, specail characters, comma in line and it's not creating proper csv file.Following is one of the column in my table, how i can add everything in single cell in cvs for such columns?


"**Challenges**
For medical device manufacturers, unplanned equipment downtime negatively impacts patient outcomes. That's why medical device manufacturers are adopting a remote service approach.

**Solution**
A Medical device manufacturer used thugs to easily connect to medical devices in the field for remote monitoring of status and performance. The *thugs Edge Microserver* provides secure, bi-directional connection to send data that is displayed on the web application created using *thugs Mashup Builder*. Different machine types are modeled using purpose built features of *thugs Composer* that anticipate this exact use case. thugs seamlessly integrates with other enterprise systems to create a comprehensive, enterprise-wide solution and provides flexibility to meet the necessary security and auditing requirements.

**Results**
Service employees detect, diagnose, and resolve problems faster, minimizing or even preventing equipment downtime. They can also more effectively collaborate with field service technicians to provide contextual guidance, improving first-time fix rates.

Data collected from connected devices is easily integrated into other existing business processes—such as parts planning and allocation or compliance reporting—which further enhances outcomes and reduces costs. Many manufacturers are also leveraging data to develop and deliver differentiated, value-add services, like automatic consumables replenishment and usage-based billing.


![inds](//cht-abcd.s3.amazonaws.com/dsc/led/land%20Ps/inds.png)"


What I have tried:

sqlcmd -S servername -E -Q "set nocount on; set ansi_warnings off; SELECT id,'""' + cast ([description] as nvarchar(max)) + '""' As description FROM [dbname].[schemanem].[tablename]" /o tags.tmp /s "," -W
Posted
Updated 28-Jun-20 21:13pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I think if you wish to use bcp to do this you create a 'format file' .. this may be of help Working with the bcp Command-line Utility - Simple Talk[^]

Else, as per the title of your question, there's XML, which is basically

bcp.exe "Select ... FOR XML AUTO" queryout [filename] -x 
with other options, maybe -S for the server, -T for trusted connection etc, as per Working with the bcp Command-line Utility - Simple Talk[^]
   
Comments
Akash Sarpate 29-Jun-20 3:50am
   
How to format multi-line data with special characters in single cell in cvs using BCP ?
Akash Sarpate 30-Jun-20 1:48am
   
Any comment ?
Garth J Lancaster 30-Jun-20 2:14am
   
ok.. there's plainly data that doesnt look good/work as CSV - so I'd be looking at
1) going directly from your SQL server to RedShift ie a Custom tool, bypassing the CSV issue altogether (or)
2) Cleaning up the data so it works as CSV (removing tabs, cr/lf etc) before it's stored in the SQL server table
3) 'maybe' storing the data as a blob in the SQL Server table, but that would still require (1) to upload it into Redshift

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100