Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract data into specific csv columns? Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 1:24 PM
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

Post #1445527
Posted Friday, April 26, 2013 1:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 7,080, Visits: 12,571
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
Post #1447140
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse