Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extract data into specific csv columns?


Extract data into specific csv columns?

Author
Message
Tony414
Tony414
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
Hello,
First off I am new to this forum and don't know much about sql. I guess that's why I am here :-) I hope I am in the right forum.

I'm hoping someone can lead me in the right direction here. I have a sql query that was written for me some time ago. I need to have the data placed in specific columns and rows within the csv file. Is it possible for me to do this? Here is what I have below... Lets say I need to place "ASSRREAL.Street" in column D row 3 of the csv file. Any help or ideas would be great. Even if there's a 3rd party tool that would allow me to do this? Thanks

SET NOCOUNT ON;
SELECT '"' + RTRIM(ASSRREAL.Unique_id) + '"' AS Uniqueid,
'"' + RTRIM(ASSRREAL.Taxpayer) + '"' AS Name,
'"' + '"' As Name2,
'"' + RTRIM(ASSRREAL.In_Care_Of) + '"' AS Careof,
'"' + RTRIM(ASSRREAL.Street) + '"' AS Address1,
'"' + RTRIM(ASSRREAL.Street_Mailing_Addr2) + '"' AS Address2,
'"' + RTRIM(ASSRREAL.City) + '"' AS City,
'"' + RTRIM(ASSRREAL.State) + '"' AS State,
'"' + RTRIM(ASSRREAL.Zip1 + ASSRREAL.Zip2) + '"' AS Zipcode,
ASSRREAL.Acreage AS Acres,
'"' + RTRIM(ASSRREAL.Map_Block_Lot + Xtr_Mbl) + '"' AS MBL,
'"' + CONVERT(varchar(5), ASSRREAL.Volume) + '"' AS Volume,
'"' + CONVERT(varchar(5), ASSRREAL.Page) + '"' AS Page,
'"' + RTRIM(LTRIM(ASSRREAL.Prop_Loc_St_No)) + '"' AS Street_Number,
'"' + RTRIM(ASSRREAL.Prop_Loc_St_Name) + '"' AS Street_Name,
'"' + RTRIM(ASSRREAL.Prop_Loc_Unit) + '"' AS Street_Unit
FROM ASSRREAL
WHERE ASSRREAL.Delete_Flag = 'N' AND
ASSRREAL.GSequence = 0 AND
ASSRREAL.Record_Year = 2013
ORDER BY ASSRREAL.Prop_Loc_St_Name, ASSRREAL.Prop_Loc_St_No, ASSRREAL.Prop_Loc_Unit
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8271 Visits: 14368
Tony414 (4/23/2013)
Hello,
First off I am new to this forum and don't know much about sql. I guess that's why I am here :-) I hope I am in the right forum.

I'm hoping someone can lead me in the right direction here. I have a sql query that was written for me some time ago. I need to have the data placed in specific columns and rows within the csv file. Is it possible for me to do this? Here is what I have below... Lets say I need to place "ASSRREAL.Street" in column D row 3 of the csv file. Any help or ideas would be great. Even if there's a 3rd party tool that would allow me to do this? Thanks


SET NOCOUNT ON;
SELECT '"' + RTRIM(ASSRREAL.Unique_id) + '"' AS Uniqueid,
'"' + RTRIM(ASSRREAL.Taxpayer) + '"' AS Name,
'"' + '"' AS Name2,
'"' + RTRIM(ASSRREAL.In_Care_Of) + '"' AS Careof,
'"' + RTRIM(ASSRREAL.Street) + '"' AS Address1,
'"' + RTRIM(ASSRREAL.Street_Mailing_Addr2) + '"' AS Address2,
'"' + RTRIM(ASSRREAL.City) + '"' AS City,
'"' + RTRIM(ASSRREAL.State) + '"' AS State,
'"' + RTRIM(ASSRREAL.Zip1 + ASSRREAL.Zip2) + '"' AS Zipcode,
ASSRREAL.Acreage AS Acres,
'"' + RTRIM(ASSRREAL.Map_Block_Lot + Xtr_Mbl) + '"' AS MBL,
'"' + CONVERT(VARCHAR(5), ASSRREAL.Volume) + '"' AS Volume,
'"' + CONVERT(VARCHAR(5), ASSRREAL.Page) + '"' AS Page,
'"' + RTRIM(LTRIM(ASSRREAL.Prop_Loc_St_No)) + '"' AS Street_Number,
'"' + RTRIM(ASSRREAL.Prop_Loc_St_Name) + '"' AS Street_Name,
'"' + RTRIM(ASSRREAL.Prop_Loc_Unit) + '"' AS Street_Unit
FROM ASSRREAL
WHERE ASSRREAL.Delete_Flag = 'N'
AND ASSRREAL.GSequence = 0
AND ASSRREAL.Record_Year = 2013
ORDER BY ASSRREAL.Prop_Loc_St_Name,
ASSRREAL.Prop_Loc_St_No,
ASSRREAL.Prop_Loc_Unit




Welcome to SSC.

Tools like bcp or SSIS will allow you to extract data from a database and write it to a text file, of which a csv is just one type. bcp can only extracts data into text files. SSIS can extract data into almost any file format as well as talk to other database platforms. If you're just starting out bcp is a simpler option, but if you invest time in learning SSIS it will pay you back 100-fold since it will allow you to bridge the gap between your SQL Server database and every other relevant data platform in use.

As for how to get your data into the third line of the file? Most data extraction tools will write the resultset to the file as it is returned from the database. If you need a specific ordering in your file, then you would control that via an ORDER BY clause onyour SELECT query, of which I see there is one. If you want your data in line 3 of the file, what would you expect to be in line 1 and 2?


PS In future posts if you surround your code with the IFCode for "sql code", [code="sql"][/code], then your post will show up with syntax highlighting.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search