We help IT Professionals succeed at work.

sql server export to flat file - text qualifier problem

-Dman100-
-Dman100- asked
on
6,818 Views
Last Modified: 2013-11-30
I'm exporting using a query to a flat .txt file.  The problem I'm encountering is when I export the data and then open the .txt file into excel some columns cause line breaks to the next row.  The columns that are breaking to a new row are varchar fields where the user has entered text into the field with double quotes ".

When I export, I'm using row delimiter {CR}{LF} column delimiter Comma and text qualifier Double Quote (")

Is there a way to prevent this from happening when I export and open the flat file into Excel?

Thanks for any help.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
-Dman100-Software Consultant

Author

Commented:
Hi Lowfatspread,

Thanks for replying to my post.

How do I export to a grid?  Will that import into excel okay?

I tried the replace, but I was getting a syntax error in my query.  Here is the query without using replace:

SELECT e.session_date, l.lab_no, i.first_name + ' ' + i.last_name AS Teacher,
tt.name, d.district_name, s.school_name, t.title, a.q1 AS Question1, a.q2 AS Question2,
a.q3 AS Question3, a.q4 AS Question4, a.q5 AS Question5, a.q6 AS Question6, a.q7 AS Question7,
a.q8 AS Question8, a.q9 AS Question9, a.q10 AS Question10
FROM evaluation e
LEFT OUTER JOIN training t ON t.id = e.training
LEFT OUTER JOIN lab l ON l.id = e.lab_no
LEFT OUTER JOIN instructor i ON i.id = e.instructor
LEFT OUTER JOIN trainee tt ON tt.id = e.trainee
LEFT OUTER JOIN district d ON d.id = e.district
LEFT OUTER JOIN school s ON s.id = e.school
LEFT OUTER JOIN answers a ON a.id = e.answers
WHERE session_date >= '20070401' AND session_date < '20070501'

I would need to use the replace on columns a.q7, a.q8, a.q9, and a.q10

Thanks for your help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.