Two Dimensional Split - SQL Server Forums

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

SQL Server Development (2000)

Two Dimensional Split

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-11-17 : 15:50:11

A user wants to update some prices "easily". I reckon this means giving them a spreadsheet with the ProductCode, ListPrice and DiscountPrice in three columns.

The user wants to put in the prices as per the "price increase" - and then their boss wants to "fiddle" with them. And so on.

I've tried Cut&Paste from XLS into a web <FORM>'s <TEXTAREA> and they come in neatly TAB delimited (with line-break between rows).

So I think I would like to have a 2-dimension Splitter - but other ideas are welcome.

It occurs to me that something that would convert this to XML would be very easy to process - basically I've got to a) list which price changes are to be performed and then b) UPDATE the prices.

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-17 : 20:30:13

i would:
1. create an interface for the edit
2. add a field or table that will tell me which price was being edited with a flag if it's to be committed to the "real table"
3. the user can modify whatever he wants to modify, then this won't still be committed until the boss commits them.

it worked for accounting posts/ledger, not sure if the same principle applies.

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2004-11-18 : 00:44:30

Thanks Jen, but the background needs to be considered:

Client has 99 products

Our application lets them set up a "new price list", and then that price list gets activated "at a given date/time"

Much like your suggestion really ...

Client says going into a number of different pages to set up prices is "way too much trouble"

Client wants to pay $2,000 for a solution that lets them edit the prices in Excel

Based on this information I absolutely agree that that is the best solution

Every spreadsheet the client has used has come back with "to be confirmed" in money-value columns, new columns have been inserted for "extra data", and column names have been changed etc.

The solution needs to widen to allow "out of stock" and other types of data grids (in Excel!)

I reckon converting the Excel to XML and pushing through a schema to validate only when there are no "to be confirmed" and other crud in there would be a good way forward (then paste the XML into a <TEXTAREA> and thus on to SQL SProc). However, the XML stuff I have come across to work like this is string & chewinggum.

So I came to the proposed solution of paste some columns from Excel into <TEXTAREA> and go from there.

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-18 : 00:55:18

I also AGREE... he he he

, take note, they want to pay...

quote:
Originally posted by Kristen

Client wants to pay $2,000 for a solution that lets them edit the prices in Excel

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-18 : 05:43:27

don't know if it helps but have you considered OWC (office web components)? maybe you could use it?
you can very simply convert excel to xml...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-11-18 : 14:47:38

I'll have a look - the user's tendancy to muck about with the "shape" of spreadsheets makes me think that it won't be easy to "automate" in that way, but you're right - its worth a look. Thanks

Kristen

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-18 : 14:53:38

Instead of worrying about XML perhaps you can just import the spreadsheet as a comma seperated values file into sql server staging table. Then you could do whatever you want with the data.

Dustin Michaels

Kristen
Test

22859 Posts

Posted - 2004-11-18 : 15:06:07

That's my 2 dimensional split. WHat's the easiest way to do that?

I'm not that keen on DTS because a) the client can't initiate that (web server at ISP etc.) and b) the likihood of the Sheet / columns / etc. having vaguely the same name and layout as expected is zero!

I reckon just cut&paste to a <TEXTAREA> on the web page - that gives me TABs between cells and CR+LF between rows. So I just need a 2 dimensional splitter??

Or am I missing a trick?

kristen

Kristen

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-18 : 15:10:39

Is this a one time load. or ongoing - If one time why not just build SQL insert statements in Excel then copy /paste these down the sheet. Then cut paste the sheet into QA.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 16:46:16

This isn't perfect, but here goes:

[CODE]
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Numbers]')
AND type = 'U')
drop table Numbers;
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'udfDelimCount')
and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfDelimCount
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'udfSplit')
and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfSplit
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'udfSplitColumn')
and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfSplitColumn
GO

CREATE TABLE Numbers (
Number int NOT NULL ,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED
(
Number
)
)
GO

INSERT Numbers
SELECT n1.n+n2.n+n3.n+n4.n+1 Number
FROM
(
SELECT 1 as n
UNION ALL
SELECT 2 as n
UNION ALL
SELECT 3 as n
UNION ALL
SELECT 4 as n
UNION ALL
SELECT 5 as n
UNION ALL
SELECT 6 as n
UNION ALL
SELECT 7 as n
UNION ALL
SELECT 8 as n
UNION ALL
SELECT 9 as n
UNION ALL
SELECT 0 as n
) n1
CROSS JOIN
(
SELECT 0 as n
UNION ALL
SELECT 10 as n
UNION ALL
SELECT 20 as n
UNION ALL
SELECT 30 as n
UNION ALL
SELECT 40 as n
UNION ALL
SELECT 50 as n
UNION ALL
SELECT 60 as n
UNION ALL
SELECT 70 as n
UNION ALL
SELECT 80 as n
UNION ALL
SELECT 90 as n
) n2
CROSS JOIN
(
SELECT 0 as n
UNION ALL
SELECT 100 as n
UNION ALL
SELECT 200 as n
UNION ALL
SELECT 300 as n
UNION ALL
SELECT 400 as n
UNION ALL
SELECT 500 as n
UNION ALL
SELECT 600 as n
UNION ALL
SELECT 700 as n
UNION ALL
SELECT 800 as n
UNION ALL
SELECT 900 as n
) n3
CROSS JOIN
(
SELECT 0 as n
UNION ALL
SELECT 1000 as n
UNION ALL
SELECT 2000 as n
UNION ALL
SELECT 3000 as n
UNION ALL
SELECT 4000 as n
UNION ALL
SELECT 5000 as n
UNION ALL
SELECT 6000 as n
UNION ALL
SELECT 7000 as n
) n4
Order by Number
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION dbo.udfDelimCount
(
@param varchar(8000),
@delim as varchar(1)
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Out SMALLINT;
SELECT @Out = COUNT(*)
FROM dbo.Numbers n
WHERE SUBSTRING
(
@param,
n.Number,
LEN(@delim)
) = @delim
AND n.Number <= LEN(@param)
RETURN COALESCE(@out,0)+1;
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION dbo.udfSplit
(
@param VARCHAR(8000),
@delim VARCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
SELECT dbo.udfDelimCount(SUBSTRING(@param,1,n.Number),@Delim) AS ElementNumber,

SUBSTRING(@delim + @param + @delim, n.Number + len(@delim),
CHARINDEX(@delim, @delim + @param + @delim, n.Number + LEN(@delim))
- n.Number - LEN(@Delim)) AS ElementValue,

CHARINDEX(@delim, @delim + @param + @delim, n.Number + LEN(@delim))
- n.Number - LEN(@Delim) as Length, n.Number as BegPosition,

CHARINDEX(@delim, @delim + @param + @delim,
n.Number + LEN(@delim))-LEN(@delim)-1 as EndPosition
FROM dbo.Numbers n
WHERE SUBSTRING(@delim + @param + @delim, n.Number, LEN(@delim)) = @delim
AND n.Number <= LEN(@delim + @param)
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION dbo.udfSplitColumn
(
@param VARCHAR(8000),
@delim VARCHAR(1),
@DesiredColumn tinyint,
@returnNulls BIT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Out VARCHAR(8000);

SELECT @Out = z.ElementValue
FROM dbo.udfSplit(@param,@delim) z
WHERE z.ElementNumber = @DesiredColumn;

RETURN CASE @returnNulls
WHEN 1
THEN NULLIF(@out,'')
ELSE COALESCE(@out,'')
END;
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

select dbo.udfSplitColumn(ElementValue,';',1,1) Column1,
dbo.udfSplitColumn(ElementValue,';',2,1) Column2,
dbo.udfSplitColumn(ElementValue,';',3,1) Column3,
dbo.udfSplitColumn(ElementValue,';',4,1) Column4
from dbo.udfSplit('1adsf;1;8,,,2;4,3;5;;5',',')

/*
Column1 Column2 Column3 Column4
------- ------- ------- -------
1adsf 1 8 NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
2 4 NULL NULL
3 5 NULL 5
*/
[/CODE]

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 16:58:04

Keep in mind the limitations of the above:

Original string can't exceed 8000 characters and you must use a one character delimiter. My experiments with using multiple character delimiters all failed horribly and I haven't had time [or need] to fix it (if the delimiter is ',,', it has a problem with ',,,,,').

Otherwise, its pretty flexible. If you don't want a Numbers table, you can just use that cross join statement as a substitute, but it will use more cpu.

Kristen
Test

22859 Posts

Posted - 2004-11-18 : 19:37:28

"Is this a one time load. or ongoing"

Ongoing ... I already did the first one putting
="UPDATE MyTable SET MyPrice = " & B1 & " WHERE MyProductCode = '" & A1 & "'"
in column C and cut&paste to QA

lazerath: thanks for the code, that's very helpful. The 8000 character limit is fine for this job - say 10 characters for product code and another 10 for price = 400 products; client only has 100 - but could do in batches for more than that. We already have a Tally table.

Kristen

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-11-19 : 11:25:37

I had a similar challenge.
It was a shopping cart scenerio but I didn't like the way datagrids would allow editing by using an edit link to turn on the editing function then make the change, update, redisplay the datagrid. That is only edit a single row at a time.
So I built the grid so a column was always displaying a textbox

upon submit then each

 <ItemTemplate><asp:TextBox ID="NewPrice" Width="50" CssClass="TextBox" Runat="server" /></ItemTemplate></asp:TemplateColumn>

the whole grid is added to a hashtable and the hashtable is passed to the update method

intellisense says

returns a Systems.Collections.IDictionaryEnumerator that can iterate through the Systems.Collections.Hastable

I have a bunch of catalogs that the plant uses to order their consumable items. Like Shipping Recieving may need a bunch of roles of tape or boxcutters every month or so they go to the web and make an order. If they were setting prices it would work just as good (for shorter lists.)

The key idea here is datagrid with edits on multiple rows.

I wish someone would start an Official XML Rant Thread.

Kristen
Test

22859 Posts

Posted - 2004-11-19 : 12:56:57

Its a nice idea, but the client seems wedded to the idea of "Boss says to add 5% to everything" so they mess around in Excel to do that. Then they pass it to their boss, and he adjust a few prices as he sees fit. Then someone else gets a whole of it and then they mess with the rpices, then they just want to cut&paste it all back in.

Its not a bad concept, but its impossible to get decent validation on it, and they will all add their own columns for "My comment" - the last one came back with extra columns for "Mark out of stock", and rows on the end for three new products - I mean, they think that Product Code and Price is enough to put a proudct on the web? No description, no images, no categories, no stock level, No shipping codes, no VAT codes, etc. etc. I give up!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 13:08:15

if i get you correctly the perfect way for this to be done is to have an excel sheet on the web which the client can change update and such save it and then show it again?? data is read from the DataBase.
and they want to add columns to db and have that automated??? great.....

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 13:35:08

Can you at least create a template worksheet that holds the product codes, prices, and other columns you want to lock down? That way they can still use them, but you can put data validation on those cells. The other columns can be left open for them to add notes (you'll probably ignore them anyway). The point is to lock down the critical bits of data so that they do not move columns around or put invalid data into them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources