July 5, 2011 at 7:34 pm
Is there a script out there that would add "|" at the end of every row? I have export a table with a million row and since the last column is null I want a "|" at the end (of every row). I've tried SSIS & export.
it is:
Lastname | Firstname | employeeNumber | Age
I want it to be:
Lastname | Firstname | employeeNumber | Age |
July 5, 2011 at 9:22 pm
You can try using BCP and define the -t and -r switch both as |.
C:\Users\leo>bcp scratch.dbo.TestTable out C:\temp\pipeOut.txt -S MyTestServer\SQL2008 -T -t"|" -r"|" -n
I tried the above and it accepted the delimiters being the same where Export complained.
HOWEVER you MUST check that the BCP IN works before/if you want to discard your original source table because I got errors boing the BCP in.
1) An enexpected end of file after 10 rows loaded
2) Only 17 rows loaded, no error
I haven't been able to resolve this.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 6, 2011 at 11:36 am
You could continue to use SSIS but change your source to a query like this:
SELECT *,
NULL AS LastColumn
FROM dbo.TableName
Adding "LastColumn" to your query will add an additional empty column to your result set forcing SSIS to append another pipe to each line in the file.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 11:46 am
yet another way is since you already did the export, using a decent text editor like EditPlus you can use a regular expression to find \n (CrLf) and replace it with |\n
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply