Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to parse one column into multiple fields..

Posted on 2010-09-07
9
Medium Priority
?
464 Views
Last Modified: 2013-11-28
Hi Experts,

I have a table that was passed to me by a colleague. The table only has two columns, the first is just an index (specifying order) and the second contains the contents of a flat file that contains multiple rows which represent ONE record where each row has a text field with a keyword followed by a value.

Example:

Table: SOURCE_TABLE
IDX   |   KEYS
1     |    STARTKEY  333
2    |     KEY_A   4000
3    |     KEY_B    5555
4    |     KEY_C    6656
5    |    
6    |     STARTKEY  766
7    |     KEY_A   3000
8    |     KEY_B    2363
9 ....

I would like to parse through every "line" of this table and pull out the real rows to insert into another table.

For instance, the final table would look like this:

Table: DEST_TABLE
START_KEY   |   KEY_A   |   KEY_B   |   KEY_C   |   .....
333      |      4000      |     5555     |     6656     |     ....
766      |      3000      |     2363     |    .....

All key names are known, there are about 30 of them.  I guess I need something to first loop through all records in the source table and then for each row, look at the key names and record their values and also, if the key name changes to "START_KEY", then write out all of the keys found since the last "START_KEY"...

In VB, I would probably start by defining all known keys like this:

DIM START_VALUE=0
DIM KEY_A_VALUE=0
DIM KEY_B_VALUE=0
DIM KEY_C_VALUE=0
.....

But that's where I'm stuck...  How do I "select * from SORUCE_TABLE" and then feed the data to a parser that will split the data and record the values to "insert" a row in my other table?


0
Comment
Question by:bganoush
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 33619490
here is one which is extremely inefficient I guess ;)

SELECT idx, val, keys,
(select top 1 val from source s_a where s_a.idx>s.idx and keys='KEY_A' order by idx) as key_a,
(select top 1 val from source s_b where s_b.idx>s.idx and keys='KEY_B' order by idx) as key_b,
(select top 1 val from source s_c where s_c.idx>s.idx and keys='KEY_C' order by idx) as key_c,
(select top 1 val from source s_d where s_d.idx>s.idx and keys='KEY_D' order by idx) as key_d
from source s
where keys='STARTKEY'
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 33619545
with two columns table like you have

SELECT idx, val, keys,
(select top 1 val from source s_a where s_a.idx>s.idx and keys like 'KEY_A*' order by idx) as key_a,
(select top 1 val from source s_b where s_b.idx>s.idx and keys like 'KEY_B*' order by idx) as key_b,
(select top 1 val from source s_c where s_c.idx>s.idx and keys like 'KEY_C*' order by idx) as key_c,
(select top 1 val from source s_d where s_d.idx>s.idx and keys like 'KEY_D*' order by idx) as key_d
from source s
where keys like 'STARTKEY*'
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 33619584
if you save this query as q_source

SELECT source.idx,  source.keys,
left(keys, instr(1, keys," ")-1) as key,
right(keys,len(keys)- instrRev(keys," ")) as val
FROM source;

you can use the one posted @ 33619490 by changing the table to query

SELECT idx, val, key,
(select top 1 val from q_source s_a where s_a.idx>s.idx and key='KEY_A' order by idx) as key_a,
(select top 1 val from q_source s_b where s_b.idx>s.idx and key='KEY_B' order by idx) as key_b,
(select top 1 val from q_source s_c where s_c.idx>s.idx and key='KEY_C' order by idx) as key_c,
(select top 1 val from q_source s_d where s_d.idx>s.idx and key='KEY_D' order by idx) as key_d
from q_source s
where key='STARTKEY'
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33619791
Haven't fully tested HainKurt's SQL, but this appears to be working against your example.

Please see the attached sample file; query SQL statement is below.


SELECT s2.KEYS AS StartKey, Max(IIf(z.KeyName = "KEY_A", z.KeyValue, Null)) AS KEY_A, Max(IIf(z.KeyName = "KEY_B", z.KeyValue, Null)) AS KEY_B, Max(IIf(z.KeyName = "KEY_C", z.KeyValue, Null)) AS KEY_C, Max(IIf(z.KeyName = "KEY_D", z.KeyValue, Null)) AS KEY_D, Max(IIf(z.KeyName = "KEY_E", z.KeyValue, Null)) AS KEY_E, Max(IIf(z.KeyName = "KEY_F", z.KeyValue, Null)) AS KEY_F, Max(IIf(z.KeyName = "KEY_G", z.KeyValue, Null)) AS KEY_G, Max(IIf(z.KeyName = "KEY_H", z.KeyValue, Null)) AS KEY_H, Max(IIf(z.KeyName = "KEY_Z", z.KeyValue, Null)) AS KEY_Z
FROM SOURCE_TABLE AS s2 INNER JOIN [SELECT CLng(DMax("IDX","SOURCE_TABLE","[KEYS] Like 'STARTKEY*' AND [IDX] < " & s.[IDX])) AS MyKey, s.IDX, Left(s.[KEYS],InStr(1,s.[KEYS] & " "," ")-1) AS KeyName, Val(Mid([s.KEYS],InStr(1,s.[KEYS] & " "," ")+1)) AS KeyValue
FROM SOURCE_TABLE s
WHERE s.KEYS Like "KEY*"
ORDER BY s.IDX]. AS z ON s2.IDX = z.MyKey
GROUP BY s2.KEYS
ORDER BY s2.KEYS;

Open in new window

Q-26456478.mdb
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33619811
Better formatting:


SELECT s2.KEYS AS StartKey, 
    Max(IIf(z.KeyName = "KEY_A", z.KeyValue, Null)) AS KEY_A, 
    Max(IIf(z.KeyName = "KEY_B", z.KeyValue, Null)) AS KEY_B, 
    Max(IIf(z.KeyName = "KEY_C", z.KeyValue, Null)) AS KEY_C, 
    Max(IIf(z.KeyName = "KEY_D", z.KeyValue, Null)) AS KEY_D, 
    Max(IIf(z.KeyName = "KEY_E", z.KeyValue, Null)) AS KEY_E, 
    Max(IIf(z.KeyName = "KEY_F", z.KeyValue, Null)) AS KEY_F, 
    Max(IIf(z.KeyName = "KEY_G", z.KeyValue, Null)) AS KEY_G, 
    Max(IIf(z.KeyName = "KEY_H", z.KeyValue, Null)) AS KEY_H, 
    Max(IIf(z.KeyName = "KEY_Z", z.KeyValue, Null)) AS KEY_Z
FROM SOURCE_TABLE AS s2 INNER JOIN 
    [SELECT CLng(DMax("IDX","SOURCE_TABLE","[KEYS] Like 'STARTKEY*' AND [IDX] < " & s.[IDX])) AS MyKey, 
        s.IDX, Left(s.[KEYS],InStr(1,s.[KEYS] & " "," ")-1) AS KeyName, 
        Val(Mid([s.KEYS],InStr(1,s.[KEYS] & " "," ")+1)) AS KeyValue
    FROM SOURCE_TABLE s
    WHERE s.KEYS Like "KEY*"
    ORDER BY s.IDX]. AS z ON s2.IDX = z.MyKey
GROUP BY s2.KEYS
ORDER BY s2.KEYS;

Open in new window

0
 

Author Comment

by:bganoush
ID: 33620360

It gives a syntax error at 'startkey*'
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 33620465
bganoush,

Please see the sample file I uploaded, which very closely replicates the example from your question.  The query "qryResults" works perfectly there.

I suggest that you post a sample file of your own.  Please be sure to sanitize any sensitive data first.

Patrick
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 33623457
did you try my posts (especially 2nd & third one)? any comment?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 33632297
Do you need more comments? I can't check all previous comments.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question