Why are columns not aligned when createing txt file.

Question

I am sending resluts from query to a flat file, then using flat file for the body of email I send.  I am having hard time aligning my 4 columns columns.  I have tried various combination of delimited and fixed width columns.  I have tried .txt and .htm extensions on flat file.  All feilds are cast as nvarchars in SQL query, including some dates.

However my flat file looks is how the body looks, so I am convinced if I can get the colunms aligned in the flat file, the columns will be aligned in the body of my email.

how ever I configure my flat file connection I can't get the columns to alighn. 

Will the approach I am taking even work, as far as aligning columns?  Everything works great except columns are not aligned.

Any help appreciated

Please help,

Thanks,

Mike

Answer 1

Hi Mike,

To make your columns get aligned you must have target flat file as Fixed width Format instead of any Column delimiter.

When you use column delimiter then it will align as the column data length it is receiving from the source.

Check this link

http://sqlserverandbi.blogspot.com/2012/10/create-fixed-width-text-file-using-flat.html

Regards

Naveen

Answer 2

Choose your delimiter as {CR}{LF}

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

how ever I configure my flat file connection I can't get the columns to alighn.

Hello Mike,

Just to confirm: In the flat file the column are aligning in rows, but not in the e-mail body? Are you using a true type font in the email or is it plain text?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

I tried using fixed with, but then it messed up my row delimiter.  It would come across like one long string and wrap acccording to how big the window is.  I tried different things but could get the cariage return after each row

Since my dataset is small (30 rows, 4 columns), I was thinking about saving the result set to a variable and then linking the body of my email to the variable.

Answer 5

The email body looks just like the txt file.  They are not aligned in the text file

Answer 6

If my columns are fixed width, what should my row delimiter be?

Answer 7

The email body looks just like the txt file.  They are not aligned in the text file

Hi Mike,

Do you mean the columns are not aligned in the flat file destination? First, we need to make sure the data are well formatted in the flat file. Based on my test, if we set the Flat File Format to either “Fixed width with row delimiters” or “Ragged right”, the text in the CSV file is well formatted. When using the “Fixed width with row delimiters” format, we don’t need to change the default value of header row delimiter ({CR}{LF}) and don’t need to map a input column to the destination column “Row delimiter column”.

However, even though the text in the flat file is well formatted, the text format displayed in the email body may be different. It is related to the Client or Web application you use to view the email. Based on my test, the following screenshot illustrates how the same well formatted text are rendered in Outlook, Gmail (Web), and Live (Web):

Regards,
Mike Yin

If you have any feedback on our support, please click here

---

Mike Yin
TechNet Community Support