Help with cleaning data export

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with cleaning data export

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-10 : 11:18:19

Good morning

Perhaps you Ladies & Gentlemen can offer some assistance.
I have a query (view) which exports data into a .csv file
The query works fine and exports the correct data, however I was hoping there was a way to clean-up the data.

for example:

The [Posting Date] exports in the followig format (03-Sep-08)
is there a way so that the [Posting Date] will export in a (09/03/08)
format.

Also, the file exports with lots of comma's (,) or (") or (').
Is there a way to clean these out of the query results

(Query below)
SELECT TOP 100 PERCENT dbo.[TransNet Corporation$Sales Invoice Line].No_ AS [Item/Res No.],
dbo.[TransNet Corporation$Sales Invoice Line].[Document No_] AS [Inv No.],
dbo.[TransNet Corporation$Sales Invoice Header].[Sell-to Customer No_] AS [Cust No.], dbo.[TransNet Corporation$Customer].Name AS [Cust Name],
dbo.[TransNet Corporation$Sales Invoice Line].Quantity AS QTY, dbo.[TransNet Corporation$Sales Invoice Line].[Unit Price] AS [Sell Price],
dbo.[TransNet Corporation$Sales Invoice Line].Description AS [Desc], dbo.[TransNet Corporation$Sales Invoice Line].[Posting Date],
dbo.[TransNet Corporation$Salesperson_Purchaser].Name AS SalesPerson,
dbo.[TransNet Corporation$Sales Invoice Line].[Unit Cost (LCY)] AS [Buy Price],
dbo.[TransNet Corporation$Sales Invoice Line].[Gen_ Prod_ Posting Group], dbo.[TransNet Corporation$Sales Invoice Header].[Salesperson Code],
dbo.[TransNet Corporation$Sales Invoice Header].[Inside Salesperson], dbo.[TransNet Corporation$Sales Invoice Header].[Transnet Project],
dbo.[TransNet Corporation$Sales Invoice Header].[Customer PO_ No_] AS [Cust PO No.],
dbo.[TransNet Corporation$Sales Invoice Header].[Bill-to County], dbo.[TransNet Corporation$Customer].[Customer Posting Group]
FROM dbo.[TransNet Corporation$Sales Invoice Line] INNER JOIN
dbo.[TransNet Corporation$Sales Invoice Header] ON
dbo.[TransNet Corporation$Sales Invoice Line].[Document No_] = dbo.[TransNet Corporation$Sales Invoice Header].No_ INNER JOIN
dbo.[TransNet Corporation$Salesperson_Purchaser] ON
dbo.[TransNet Corporation$Sales Invoice Header].[Salesperson Code] = dbo.[TransNet Corporation$Salesperson_Purchaser].Code INNER JOIN
dbo.[TransNet Corporation$Customer] ON
dbo.[TransNet Corporation$Sales Invoice Header].[Sell-to Customer No_] = dbo.[TransNet Corporation$Customer].No_
WHERE (dbo.[TransNet Corporation$Sales Invoice Line].No_ <> '') AND (dbo.[TransNet Corporation$Sales Invoice Line].[Posting Date] > CONVERT(DATETIME,
'2008-08-31 00:00:00', 102))
ORDER BY dbo.[TransNet Corporation$Sales Invoice Line].[Posting Date]

Thanks for any assistance you may be able to provide in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 12:05:55

1) Use CONVERT function when formatting your dates.
2) Use REPLACE function to change your unwanted characters.

E 12°55'05.63"
N 56°04'39.26"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-10 : 12:09:05

Hi Peso

Thanks for your reponce

Could you provide an example of using the REPLACE function in my query to change my unwanted characters
and an example of a CONVERT function to change the [Posting Date] from (03-Sep-08) to (09/03/08) format

Thanks for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 12:14:05

Books Online didn't give you the answer? Strange.

1) SELECT ... , CONVERT(CHAR(8), dbo.[TransNet Corporation$Sales Invoice Line].[Posting Date], 1), ...
2) SELECT ... , REPLACE(dbo.[TransNet Corporation$Salesperson_Purchaser].Name, ',', ' '), ...
(for converting a comma to a space.)

E 12°55'05.63"
N 56°04'39.26"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-10 : 12:53:21

Hi Peso

I looked at the books online per your suggestion (I don't have much experience with SQL).
I tried using SQL Analyzer with your suggested syntax, but I keep getting error messages
Is there a particular area within my query statement I need to add your suggested syntax?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 14:55:55

Yes there is.
As seen with my example, the column [Posting date] need to be wrapped with a CONVERT function.
And use REPLACE function as seen in my example for every column you need to replace characters.

E 12°55'05.63"
N 56°04'39.26"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-11 : 09:37:57

Hi Peso

Got it, working perfectly now. (Thanks again for your help)
Here is my query/view (hopefully this may help someone else attempting to do this as well)

This converts the [Posting Date] to a (09/03/08) format, and also cleans-up the data by
removing all comma's (,) from the [Cust Name] & [Desc] columns

(Here's my view/query)
SELECT TOP 100 PERCENT [Item/Res No.], [Inv No.], [Cust No.], REPLACE([Cust Name], ',', ' ') AS [Cust Name], QTY, [Sell Price], REPLACE([Desc], ',', ' ') AS [Desc], REPLACE(CONVERT(VARCHAR, [Posting Date], 1), '/', '/') AS [Posting Date], SalesPerson, [Buy Price], [Gen_ Prod_ Posting Group], Salesperson Code], [Inside Salesperson], [Transnet Project],
[Cust PO No.], [Bill-to County], [Customer Posting Group]
FROM dbo.vw_Commissions
ORDER BY REPLACE(CONVERT(VARCHAR, [Posting Date], 1), '/', '/')

Question for you Peso, is there a way to your knowledge to insert an ID column into a (view)
which will auto-number each line? It would be awesome if there is a way to do this in a view.
If so please let me know how

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources