April 23, 2013 at 9:34 am
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
April 26, 2013 at 1:20 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply