Create CSV but add a comma after last column

[UHNSTrust]

I need to use DTS to create a CSV but the row delimiter needs to be a comma and then {CR}{LF}.

How do I do this? Whatever I have tried has not worked. Can somebody help please?

Thanks in advance

Jonathan

 

[jby1]

A crude solution to your problem, which I am sure someone will better, is that you could have an extra column in your table, at the end, which has an empty string in it for each row.

 

[dbomrrsm]

in the connection properties window of the text file (destination) when you have specified where you want the file creating by clicking the ... button specify a file name with a .csv extension then click on the properties button just underneath the ... button and that allows you to specify comma separation and row delimiter.

[blue]DBomrrsm[/blue]

 

[UHNSTrust]

Thanks for the replies but unfortunately neither helped.

jby1 - If I use an extra column it puts quotes around the last one so I would get ,""{CR}{LF} when I only want ,{CR}{LF}

dbomrrsm - The option of specifying your own row delimeter is not allowed. It just gives you a list to choose from.

Any one else have any ideas?

Thanks again for the replies

Jonathan

 

[hmckillop]

An equally crude solution,
You could temporarily update your current last column with a comma
i.e.

UPDATE MyTable Set LastColumn = LastColumn & ','

then once you run the export

UPDATE MyTable SET LastCOlumn = LEFT(LastColumn,Len(LastCOlumn)-1)

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dbomrrsm]

can you show us some example data i.e what is in the DB and what the output as a CSV needs to look like _ I am sure we can help just need some clarification.

[blue]DBomrrsm[/blue]

 

[jby1]

What happens if you make the last column an integer type, and have a null in it?

Although I think hmckillop's crude solution is slightly less crude than mine, and has the definite advantage of not requiring a change to the data structure.

Of course, that solution would only work if the last column happens to be a varchar or char[] type, and you will probably still have the inverted commas you talked about.

Hmmm .....

Why do you want this end comma anyway?

 

[JamesLean]

If I use an extra column it puts quotes around the last one

Have you tried making the extra column an integer column, rather than a char-based column? It should only put the quotes around character data.

--James

 

[UHNSTrust]

I would need each line in the created CSV file to look like this

"Jonathan","Test","Help",

so the actual file in notepad would be like this

"Jonathan","Test","Help",
"Kevin","Test1","Help1",
"Kate","Test2","Help2",
"Kerry","Test3","Help3",

Hope this makes it clear

Jonathan

 

[jby1]

Did you try adding a null integer column at the end?

 

[dbomrrsm]

you can build the statement

select '"'+cast(id as varchar)+'"'+','+'"'+Name+'"'+','
from dbo.Brotherhood_of_the_Ring

rememberting to cast any int or numerics to varchar above gives output of

"1","Gandalf",
"2","Aragorn",
"3","Frodo",
"4","Bilbo",
"5","Elrond",
"6","Legolas",
"7","Arven",
"8","Galadriel",
"9","Arvem",

[blue]DBomrrsm[/blue]

 

[UHNSTrust]

Sorry it has taken so long to come back to this thread (been on leave/courses etc).

Thanks for all your relies. The solution I chose was to add the null integer field at the end of the CSV file.

All now working. Thanks again

Jonathan