-1

First time question here..

I have 100's of CSV files I need to convert using whatever means you think necessary.

The format is an ever-accumulating value e.g.

01/01/2012,96,1000,1100,1150,1200,1400,1450,......
02/01/2012,96,1900,2050,2060,......

But I need this in a single value format, so I need the difference between each value e.g.

01/01/2012,96,100,50,50,200,50......
02/01/2012,96,150,10,......

96 is the no. of values in each row.

Thats the 1st part, the next is to ignore any row which doesn't have 96 values, so:

01/01/2012,1024,96,96.7,96.5,96.3,.....

would be ignored.

I'm very new to writing scripts/macros so baby steps please!

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2819573
  • 161
  • 1
  • 9

1 Answers1

0

UNTESTED. I suggest:
Import a .csv to Excel.
Turn on Macro recorder.
Text to Columns with , as delimiter.
Filter column containing 96 to select any value other than 96 and delete those rows.
Add a row for the differences.
Concatenate the results of 96 columns with the likes of =A2&","&B2&","….
Adjust code to suit.

pnuts
  • 58,317
  • 11
  • 87
  • 139