CODEWITHSUNDEEP
csvssis
Ravi Shankar Kota
Ravi Shankar Kota

Reputation: 705

Remove Leading spaces in exported CSV file

I created a Data Flow Task that generates a CSV file from Database. The CSV file has a zip column.Some rows are having zip like "43218-2166" and few are having zip like "21709". The problem here is for the columns with zip length=5, alignment is right.I want these Zips to be text aligned left. Otherwise Execute Process Task which takes this CSV as input does not work fine. I created a Derived Column between OLEDB Source and Flat File Destination and used several expressions on zip including LTRIM,RTRIM , REPLACE etc. Nothing worked. Please suggest if any workaround to get the left alignment in CSV for Zip?

Below image shows how the Zips are arranged.

Upvotes: 0

Views: 2212

Answers (2)

user2943111
user2943111

Reputation: 461

You could open the file in Excel, select the zip column and then format the entire column as text. But I guess you don't want to do this every time you regenerate your file.

So you could quote the values (that is: enclose each value within quote characters on both sides) that might otherwise wrongly be interpreted as number. Since your file is generated, easiest would be to quote all text fields, at least for the zip column. The other two columns are fine, since Excel can see these aren't numbers.

What exactly is take to be the quote character depends. In Libre Office Calc, when I open a csv file, I get a dialog to specify field separators and quoting character. So here you could choose apostrophe, backtick or whatever you want. Whatever you input there obviously has to match with your file.

Upvotes: 0

Ako
Ako

Reputation: 1221

Your issue is that when you open a file in Excel it show '21709' as a number, so right aligned and '50301-0100' as text, therefore left aligned.

In csv itself you can not define an alignment.

I tested with next csv file.

A,B,X
1,2,X
1   ,2,X
1,   2,X
111-111   ,222-222,X
111-111,   222-222,X
111-111,222-222,X
111-111,222-222,X

To force a left align, you can do something ugly like putting a apostroph before every number.

Upvotes: 3

Related Questions