Home > Archive > AWK > November 2004 > Convert Columns to Rows
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Convert Columns to Rows
|
|
| Buck Turgidson 2004-11-17, 3:56 pm |
| I have a very large csv file from a spreadsheet. The first 2 columns are a
description, and the next 52 columns are a value for a w of the year
(using only 3 ws in this example)
Is there a way in Awk to transform these into a format that is loadable into
a relational database such as Oracle?
For example, I'd like to change the following into the latter format. The
first column would be a derived w number. That first column is something
I could live without if I had to.
Any help with a simple awk would be appreciated.
washer,sunday,33,44,55
dryer,monday,667,333,44
refrig,monday,555,876,99
1 washer sunday 33
2 washer sunday 44
3 washer sunday 55
1 dryer monday 667
2 dryer monday 333
3 dryer monday 444
1 refrig monday 555
2 refrig monday 876
3 refrig monday 99
| |
| Kenny McCormack 2004-11-17, 3:56 pm |
| In article <bebs62-3ff.ln1@turf.turgidson.com>,
Buck Turgidson <jc_va@hotmail.com> wrote:
....
Convert:
>washer,sunday,33,44,55
>dryer,monday,667,333,44
>refrig,monday,555,876,99
To:
>1 washer sunday 33
>2 washer sunday 44
>3 washer sunday 55
>1 dryer monday 667
>2 dryer monday 333
>3 dryer monday 444
>1 refrig monday 555
>2 refrig monday 876
>3 refrig monday 99
BEGIN {FS=","}
{
for (i=3; i<=NF; i++)
print i-2,$1,$2,$i
}
| |
| Ed Morton 2004-11-17, 3:56 pm |
|
Buck Turgidson wrote:
> I have a very large csv file from a spreadsheet. The first 2 columns are a
> description, and the next 52 columns are a value for a w of the year
> (using only 3 ws in this example)
>
> Is there a way in Awk to transform these into a format that is loadable into
> a relational database such as Oracle?
Sure:
gawk -F, '{for (i=3;i<=NF;i++) print i-2, $1, $2, $i}'
Ed.
| |
| Buck Turgidson 2004-11-17, 3:56 pm |
| >
> BEGIN {FS=","}
> {
> for (i=3; i<=NF; i++)
> print i-2,$1,$2,$i
> }
>
Just beautiful.....Thanks to both.
|
|
|
|
|