Data Manupulation

[NMH]

Hi All,

Please find below sample CSV file.

Code 30/12/05 02/01/06 03/01/06 04/01/06 05/01/06 06/01/06 09/01/06 10/01/06 11/01/06
15105K 6.5 6.5 6.5 6.77 6.87 6.86 6.71 6.89 7.29
15105L 3.86 3.86 3.86 3.86 3.86 3.86 4.25 4.34 4.31
29061M 8.3 8.3 8.75 9.05 8.9 9.4 9.5 9.4 9.3
15105P 3.2701 3.2701 3.2 3.121 3.26 3.15 3.1 3.2 3.26

I need this data have to be denormalize in the following way by using Vb.net code.

Code date Value
15105K 30/12/2005 6.5
15105K 02/01/2006 6.5
15105K 03/01/2006 6.5
15105K 04/01/2006 6.77
15105K 05/01/2006 6.87
15105K 06/01/2006 6.86
15105K 09/01/2006 6.71
15105K 10/01/2006 6.89
15105K 11/01/2006 7.29
15105L 30/12/2005 3.86
15105L 02/01/2006 3.86
15105L 03/01/2006 3.86
15105L 04/01/2006 3.86
15105L 05/01/2006 3.86
15105L 06/01/2006 3.86
15105L 09/01/2006 4.25
15105L 10/01/2006 4.34
15105L 11/01/2006 4.31
29061M 30/12/2005 8.3
29061M 02/01/2006 8.3
29061M 03/01/2006 8.75
29061M 04/01/2006 9.05
29061M 05/01/2006 8.9
29061M 06/01/2006 9.4
29061M 09/01/2006 9.5
29061M 10/01/2006 9.4
29061M 11/01/2006 9.3
15105P 30/12/2005 3.2701
15105P 02/01/2006 3.2701
15105P 03/01/2006 3.2
15105P 04/01/2006 3.121
15105P 05/01/2006 3.26
15105P 06/01/2006 3.15
15105P 09/01/2006 3.1
15105P 10/01/2006 3.2
15105P 11/01/2006 3.26


Could you please help me anyone.....thanks in advance

Cheers
NMH

 

[cjard]

the posted data is not CSV, it is SSV. THis will be your first problem

I'd do this manually.. read the first line, split it and keep it as a map array
read the next line, split and pull the first element as the date, then for all other elements on the line:

Dim mapArrayOfDates() as String = firstLine.Split(" ")

Dim sb as New StringBuilder
For i as Integer = 1 to lineSplit.Length -1
  sb.AppendLine(lineSPlit(0), mapArrayOfDates(i), lineSplit(i))
Next i

I'll leave you to put the rest of the logic; not going to do all your homework for you