SQLTeam.com | Weblogs | Forums

Exporting Tab Delimited Files Using Import/Export Wizard


I am using the Import/Export Wizard to export a SQL table to a tab delimited file. However, there are extra spaces in the fields to the right that I need to remove either before or during the export. I have used the rtrim(ltrim()) function to remove the spaces in the SQL table fields. I have also tried using nvarchar as the field type for the fields. How can I remove the spaces from the fields so that the tab delimited file does not contain the spaces ?



Is it just leading/trailing spaces you need to remove, or spaces within the data of a column?

I cannot see why spaces would upset import into Excel - but it is possible that the import settings in Excel are (currently) set to split on both TAB and SPACE. Unfortunately (AFAIK) the settings used by Excel are always the "current" ones, so even if it works for a user now then if they change their column split delimiter settings, e.g. to import a different file, then those new settings will be used "next time".

If you want to remove spaces from WITHIN a column you could do

REPLACE(MyColumn, ' ', '~')

where "~" is whatever character you want to replace space with.

Beware of any column that you are included in the export that has embedded Line Breaks as that WILL muck up the import into Excel. You can fix those with

REPLACE(REPLACE(MyColumn, CHAR(13), ' '), CHAR(10), ' ')

(or replace with something other than SPACE if it turns out that spaces ARE causing you problems)