export to flat file - text qualifier problem

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

export to flat file - text qualifier problem

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2007-06-07 : 13:04:49

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.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 13:09:35

try doing a REPLACE(REPLACE(YourColumn,CHAR(10),''),CHAR(13),'') to see if makes any difference

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-07 : 13:10:47

Can you use another text qualifier, or another delimiter, i.e. pipes (|)?

Mark

-Dman100-
Posting Yak Master

210 Posts

Posted - 2007-06-07 : 14:13:15

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

I tried using another delimite...pipes (|) and that didn't work? Maybe I was attempting it incorrectly?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources