December 28, 2004 at 1:04 am
I'm trying to convert a "paper type" report into transactions that can be used in SQL Server. The paper report does not have the account number on each line, although the account number is in the same position so I can grab it.
My problem is getting the account number on each line of the report, to create transactions that can be used in SQL Server.
I put this link on the net with a better example, and how I'd solve it in a spreadsheet.
http://oberman.info/sql/examplesql.htm
Thanks !!
December 28, 2004 at 10:46 am
I've been messing with this for hours, I don't think it can be done ???
December 29, 2004 at 5:36 am
I was thinking, if there is a way to make a stored procedure process line-by-line, then I could make this work. Is that possible?
December 29, 2004 at 9:24 am
Hello swtrans, here's an approach for you. This code assumes that the Identity values have been assigned sequentially (!). It also assumes that the AcctNo entries are all actually fixed length, with "AcctNo" in columns 1-6, and the account number value in columns 8 through the end, as shown in your example. Otherwise, you'll have to do some comma-specific parsing. (See below, step 4).
Example data:
Identity Data
1 AcctNo,10001
2 FirstName,Ed
3 LastName,Jones
4 City,Chicago
5 Start,01012001
6 End,05072003
7 100
8 300
9 500
10 AcctNo,10002
11 FirstName,Tom
12 LastName,Jinx
13 City,Dallas
14 Start,07012002
1. Select just the AcctNo entries; and parse out the AcctNo value.
You can put this in a UDF, a view, or stash the result into a temp table:
CREATE VIEW acct_id_vw AS
SELECT Identity AS first_id
, SUBSTRING(Data,8,LEN(RTRIM(Data))-7) AS acct_no
FROM Example_data AS d
WHERE SUBSTRING(Data,1,6)='AcctNo'
GO
2. Using results from step 1, find first and last ID numbers that
apply to each account. Note also we need to get an entry for
the last account:
CREATE VIEW acct_range_vw AS
SELECT a.acct_no, a.first_id, MIN(b.first_id)-1 AS last_id
FROM acct_id_vw AS a
, acct_id_vw AS b
WHERE b.first_id > a.first_id
GROUP BY a.first_id
UNION
SELECT n.acct_no, a.max_id AS first_id, MAX(t.Identity) AS last_id
FROM (SELECT MAX(first_id) AS max_id FROM acct_id_vw) AS a
, acct_id_vw as n
, Example_data AS d
WHERE a.max_id = n.first_id
AND d.Identity > a.max_id
GROUP BY a.max_id
GO
3. Finally! Select the data joined to the account range view:
SELECT r.first_id, r.acct_no, d.Identity, d.Data
FROM Example_data AS d
, acct_range_vw AS r
WHERE d.Identity >= r.first_id
AND d.Identity <= r.last_id
4. BTW, here's a way to parse the Example_data into 3 columns: id, field_nm, field_value.
SELECT Identity AS id
, field_nm = CASE WHEN CHARINDEX(Data,',') = 0 THEN NULL
ELSE SUBSTRING(Data,1,CHARINDEX(Data,',')-1) END
, field_value = CASE WHEN CHARINDEX(Data,',') = 0 THEN RTRIM(Data)
ELSE SUBSTRING(Data,CHARINDEX(Data,',')+1,LEN(RTRIM(Data))-CHARINDEX(Data,',')) END
FROM Example_data
5. Clearer to some, and maybe faster, too:
SELECT Identity AS id
, field_nm = CASE WHEN comma = 0 THEN NULL
ELSE SUBSTRING(Data,1,comma-1) END
, field_value = CASE WHEN comma = 0 THEN RTRIM(Data)
ELSE SUBSTRING(Data,comma+1,LEN(RTRIM(Data))-comma) END
FROM
(SELECT *, CHARINDEX(Data,',') AS comma FROM Example_data) AS d
Bob Monahon
December 29, 2004 at 3:01 pm
Thank you. I'm on the road and will try this when I get home.
December 30, 2004 at 2:10 am
That worked like a charm. I had to add a GROUP BY acct_no in part 2
Thanks again
December 30, 2004 at 7:01 am
Glad to help. Good luck with the report import!
Bob Monahon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply