Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004

    Unanswered: export to flat file - text qualifier problem

    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?

    I tried using 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,, 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 =
    LEFT OUTER JOIN lab l ON = e.lab_no
    LEFT OUTER JOIN instructor i ON = e.instructor
    LEFT OUTER JOIN trainee tt ON = e.trainee
    LEFT OUTER JOIN district d ON = e.district
    LEFT OUTER JOIN school s ON =
    LEFT OUTER JOIN answers a ON = 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 delimiter...pipes (|) and that didn't work? Maybe I was attempting it incorrectly?

    Thanks in advance for any help.

  2. #2
    Join Date
    May 2007
    somewhere in dbforums
    I got Your problem
    suppose your column is

    a7 = AA"DS
    a8 = SD"AD
    a9 = WR"TY
    a10 = DGHR

    now you have given text qualifier as double quote -> "

    so while preparing data for export your fields will be treated as

    a7 = "AA"DS"
    a8 = "SD"AD"
    a9 = "WR"TY"
    a10 = "DGHR"

    since they are text

    did you get my point now.... AA will be treated as one piece of text instead of AA"DS......since there is a start " and an end " which defines a text i.e AA

    try using a different symbol for text qualifier, a symbol which does not exist in a7,a8,a9,a10 and that should do your job.....

    i know I have not explained it that clearly but i hope you are getting what i am trying to convey to you.......

  3. #3
    Join Date
    Jan 2004
    Hi Nick,

    thanks for replying to my post. yes, I think that is exactly the problem and I understand what you are saying. How do I use a different text qualifier though? The only ones available are a comma, double quote or none?

    Thanks for your help.

  4. #4
    Join Date
    May 2007
    somewhere in dbforums
    the thing is why do you need a text qualifier.......if eventually you are going to import the data into another table or the same table you don't need to specify the text qualifier...... SQL server will directly import the data without a hitch......has worked for me....try it out if that is the case.....

    Also if you import directly without the text qualifier to excel it should work fine......try it out and let us know what happens

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts