asked on

Need to change columns of data to something more useful.

I've tried a few times, but i cant get the time, date and data to align up

,, Load Forecast - PSP_Integrated - Last Update on Oct. 07 at 09:12 Central Prevailing Time
Hour,Yesterday Actual,10/07/11,10/08/11,10/09/11,10/10/11,10/11/11,10/12/11,10/13/11
 1, 4458, 4472, 4437, 4054, 3973, 4177, 4193, 4165
 2, 4238, 4295, 4231, 3888, 3851, 3998, 4014, 3991
 3, 4100, 4108, 4065, 3761, 3768, 3868, 3880, 3860
 4, 4001, 4124, 3990, 3713, 3777, 3855, 3863, 3844
 5, 4108, 4224, 3985, 3699, 3885, 3937, 3944, 3926
 6, 4409, 4550, 4082, 3746, 4241, 4273, 4267, 4250
 7, 5072, 5217, 4319, 3877, 4890, 4902, 4883, 4881
 8, 5434, 5611, 4498, 3989, 5273, 5288, 5283, 5278 
 9, 5537, 5756, 4725, 4174, 5421, 5418, 5432, 5411
10, 5737, 5944, 4949, 4353, 5549, 5505, 5533, 5522
11, 5887, 6116, 5099, 4487, 5706, 5622, 5655, 5632
12, 6056, 6229, 5214, 4629, 5817, 5709, 5751, 5711
13, 6183, 6327, 5275, 4718, 5903, 5773, 5814, 5751
14, 6251, 6403, 5273, 4744, 5963, 5813, 5847, 5762
15, 6277, 6439, 5282, 4772, 5962, 5823, 5853, 5751
16, 6230, 6410, 5275, 4800, 5922, 5796, 5814, 5688
17, 6170, 6364, 5299, 4877, 5868, 5767, 5774, 5631
18, 6031, 6208, 5288, 4961, 5787, 5708, 5703, 5541
19, 5901, 6017, 5256, 5029, 5713, 5647, 5609, 5453
20, 6089, 6074, 5380, 5170, 5793, 5756, 5711, 5589
21, 5972, 5912, 5232, 5063, 5647, 5629, 5581, 5460
22, 5634, 5543, 4922, 4783, 5280, 5297, 5261, 5140
23, 5193, 5116, 4609, 4467, 4817, 4851, 4824, 4740
24, 4774, 4709, 4304, 4143, 4412, 4447, 4422, 4356
,, Temp Forecast - PSP_Integrated - Last Update on Oct. 07 at 09:12 Central Prevailing Time
Hour,Yesterday Actual,10/07/11,10/08/11,10/09/11,10/10/11,10/11/11,10/12/11,10/13/11
 1,   64,   66,   71,   62,   57,   61,   60,   58
 2,   63,   69,   70,   62,   56,   59,   59,   57
 3,   62,   69,   69,   60,   55,   56,   58,   56
 4,   59,   70,   68,   60,   55,   54,   56,   55
 5,   58,   67,   68,   59,   55,   53,   55,   54
 6,   57,   64,   67,   58,   55,   52,   55,   54
 7,   56,   64,   67,   58,   55,   52,   55,   54
 8,   57,   65,   67,   58,   57,   53,   57,   55 
 9,   60,   68,   67,   58,   59,   55,   59,   57
10,   64,   70,   68,   59,   61,   58,   61,   60
11,   69,   73,   70,   61,   65,   61,   64,   62
12,   76,   76,   73,   64,   68,   64,   66,   63
13,   79,   79,   74,   65,   72,   67,   68,   64
14,   81,   81,   74,   67,   72,   68,   70,   65
15,   82,   82,   76,   67,   72,   69,   70,   65
16,   82,   82,   76,   67,   72,   69,   70,   64
17,   82,   83,   74,   67,   69,   69,   69,   63
18,   79,   80,   73,   66,   69,   68,   68,   61
19,   75,   78,   71,   65,   67,   67,   66,   59
20,   73,   76,   70,   64,   64,   65,   64,   57
21,   71,   75,   69,   62,   62,   64,   62,   55
22,   71,   74,   68,   61,   60,   64,   60,   52
23,   69,   72,   66,   61,   59,   63,   58,   51
24,   68,   71,   65,   60,   56,   61,   58,   51

Open in new window

I want to convert it to:

Load Forecast - PSP_Integrated,10/07/11,1:00,4458,
Load Forecast - PSP_Integrated,10/07/11,1:00,4238,

etc

then the 2nd Set

Temp Forecast - PSP_Integrated,10/07/11 1:00,64,
Temp Forecast - PSP_Integrated,10/07/11 2:00,63,

etc

Its beyond me, anybody see a way to do it?

Bill Prew

I don't understand what you are looking for, this looks like a CSV file, which are typically not "formatted" to align columns, etc?

Can you share a before and after look at a sample set of data, that might help.

~bp

JCS_Unlimited

ASKER

Hey Billprew.

If you look above I showed the before in a code window and a example of output below it. Here is what I want it to look like.

I want to convert it to:

Load Forecast - PSP_Integrated,10/07/11,1:00,4458,
Load Forecast - PSP_Integrated,10/07/11,1:00,4238,

etc

then the 2nd Set

Temp Forecast - PSP_Integrated,10/07/11 1:00,64,
Temp Forecast - PSP_Integrated,10/07/11 2:00,63,

etc

Bill Prew

Okay, trying to understand enough to propose an approach.

For the first set, you indicated:

Load Forecast - PSP_Integrated,10/07/11,1:00,4458,
Load Forecast - PSP_Integrated,10/07/11,1:00,4238,

(1) Where did the "1:00" on each line come from, the neader seemed to indicate 9:12?

(2) The first line of data was:

1, 4458, 4472, 4437, 4054, 3973, 4177, 4193, 4165

are the numbers after the 4458 ignored, or will you also want tose output, with the correct day from the header?

For the second set, you indicated:

Temp Forecast - PSP_Integrated,10/07/11 1:00,64,
Temp Forecast - PSP_Integrated,10/07/11 2:00,63,

(1) Where did the "1:00" and "2:00" come from?

~bp

JCS_Unlimited

ASKER

Your right BP,

I just relized that I was not specific enough

If you look in the code above you will see the first line is un needed. It specifies that "Load Forecast - PSP_Integrated" which I want each line to begin with.

The next line is the date specified, you can see it is columns. Below that is rows with times.

What I need is:

Load Forecast - PSP_Integrated ((firstdate)) ((firsttime)) number

Is that better?

Sorry

Bill Prew

Okay, see if this gets close to what you want. Save as a VBS and run it passing two command line parms, the input file and the output file.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
 
' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No input filename specified."
  WScript.Quit
End If

' Get input file name from command line parm
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  WScript.Echo "No output filename specified."
  WScript.Quit
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Open output file
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Loop through all lines of the file
For Each strLine in arrLines
   If strLine <> "" Then
      strBegin = Left(strLine, 3)
      Select Case strBegin
         Case ",, "
            strTitle = Trim(Mid(strLine, 4, Instr(Instr(1, strLine, "-", 1) + 1, strLine, "-", 1) -4))
         Case "Hou"
            arrDate = Split(strLine, ",")
            strDate = arrDate(2)
         Case " 1,", " 2,", " 3,", " 4,", " 5,", " 6,", " 7,", " 8,", " 9,", "10,", "11,", "12,", "13,", "14,", "15,", "16,", "17,", "18,", "19,", "20,", "21,", "22,", "23,", "24,"
            arrValue = Split(strLine, ",")
            strTime = Trim(arrValue(0)) & ":00"
            strData = Trim(arrValue(1))
            objFile.WriteLine strTitle & "," & strDate & "," & strTime & "," & strData & ","
      End Select
   End If
Next
    objFile.WriteLine()

' Close output file
objFile.Close

Open in new window

~bp

JCS_Unlimited

ASKER

BP,

That worked for the first column,

If you see the days are broken up in the coulums I was talking about:

Hour,  Yesterday Actual,   10/07/11,  10/08/11,  10/09/11,  10/10/11,  10/11/11,  10/12/11,  10/13/11

Open in new window

Id love to skip yesterdays actual and start with the first date and pull its column of data like you are showing me, then next column/date etc

Bill Prew

Can you please provide a full and complete output file that you want generated from your test input file.

~bp

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JCS_Unlimited

ASKER

BP,

That worked perfectly. I was able to change it a little bit and work with a few other files as well.

Thanks a lot.

Bill Prew

Great, glad we got to something useful.

~bp