resorting columns

[Robert H]

I use data that is export from electrical test equipment in CSV
format. For years I have been developing vba code to manipulate this
data for analysis. One problem with the data is that it is grouped in
columns in a way that makes analysis and charting cumbersome. I
should have worked this out long ago but must have enjoyed the
struggle.

the data come in in columns like:
TYPE, DATE, DC_RES, IMP_100_Hz, PHASE_100_Hz, LC_100_Hz, QD_100_Hz,
IMP_200_Hz, PHASE_200_Hz, LC_200_Hz, QD_200_Hz, IMP_400_Hz,
PHASE_400_Hz, LC_400_Hz, QD_400_Hz, IMP_1_kHz, PHASE_1_kHz, LC_1_kHz,
QD_1_kHz
The data runs higher in frequency

I need to reorganize the data so that each data type is grouped
together. For instance TYPE, DATE, DC_RES, IMP_100_Hz, IMP_200_Hz,
IMP_400_Hz, IMP_1_kHz, etc

a push towards efficient way to do this would be helpful.
thanks
Robert

 

[Chip Pearson]

Probably the easiest way would be to open the csv file directly using
VBA's I/O routines, put the data in one large string, then use Split
to break the data apart by some delimiter (a comma), and finally
picking elements out of the array in the desired order and writing
those values out to the worksheet.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Michael]

This is what I would do, since your sorting may require multipe steps (Excel
Sorts based in Alphabetical order)

First I'd bring the file as it is, setting up an instruction that will split
it in columns using comma as my delimiter.
The second instruction is the one that may be a little slow, depending of
the number of rows in your file...
I would set a range for all of the rows and loop through it, sending each
row to a "Pivot Sheet" using the Paste Special Transpose option, the applying
the sort.
This may require an extra step, since your Hertz and Kilohertz may be mixed
up due to the fact that excel will place IMP_1_KHZ before IMP_100_HZ.
So you probably have to split each string based on your underscore, then do
the sort.
Once is properly sort you will have to do a Paste Special Transpose again
and send it back to the source sheet.
Subsequently saving it as a CSV file.

 

[Robert H]

Unfortunately I'm dealing with more rows than can transpose to
columns

 

[Robert H]

Chip, if the data is already delimited do I need to put the data into
a string and then split it?
and what method would I use to pick the data out. I was thinking
something that says find IMP, record that location, find next IMP
select column, cut, paste at IMP1 + 1, repeat. Once all IMP is done,
repeat foe the next data type.

I have the ideas, I just get lost in the methods :O

 

[RadarEye]

Chip, if the data is already delimited do I need to put the data into
a string and then split it?
and what method would I use to pick the data out. I was thinking
something that says find IMP, record that location, find next IMP
select column, cut, paste at IMP1 + 1, repeat. Once all IMP is done,
repeat foe the next data type.

I have the ideas, I just get lost in the methods :O




- Tekst uit oorspronkelijk bericht weergeven -

Hi Robert,

It seems to me that you are a newbee.
I have worked out Chips idea using Excel2003:


Sub SplitRobertH()
Dim strFileName As String
Dim strDataLine As String
Dim varSplited As Variant
Dim lngFree As Long

strFileName = Application.GetOpenFilename _
("Testdata (*.csv),*.csv", , "Select datafile")
If strFileName = vbNullString Then Exit Sub
lngFree = FreeFile
Open strFileName For Input As lngFree
Do While Not EOF(lngFree)
Line Input #lngFree, strDataLine
varSplited = Split(strDataLine, ",")
ActiveCell.Value = varSplited(0)
ActiveCell.Offset(0, 1).Value = varSplited(1)
ActiveCell.Offset(0, 2).Value = varSplited(2)
ActiveCell.Offset(0, 3).Value = varSplited(3)
ActiveCell.Offset(0, 4).Value = varSplited(7)
ActiveCell.Offset(0, 5).Value = varSplited(11)
ActiveCell.Offset(0, 6).Value = varSplited(15)
ActiveCell.Offset(0, 7).Value = varSplited(4)
ActiveCell.Offset(0, 8).Value = varSplited(8)
ActiveCell.Offset(0, 9).Value = varSplited(12)
ActiveCell.Offset(0, 10).Value = varSplited(16)
ActiveCell.Offset(0, 11).Value = varSplited(5)
ActiveCell.Offset(0, 12).Value = varSplited(9)
ActiveCell.Offset(0, 13).Value = varSplited(13)
ActiveCell.Offset(0, 14).Value = varSplited(17)
ActiveCell.Offset(0, 15).Value = varSplited(6)
ActiveCell.Offset(0, 16).Value = varSplited(10)
ActiveCell.Offset(0, 17).Value = varSplited(14)
ActiveCell.Offset(0, 18).Value = varSplited(18)
ActiveCell.Offset(1, 0).Select
Loop
Close lngFree
End Sub

This code opens the fele select dialog so you are free to select any
CSV file.
There is no error handling present so it is up to you to select a CSV
file with the corrrect number of columns.

HTH,

Wouter

 

[Robert H]

Wouter, I played around with that this morning. I have to do some
tweaking but its doing just what I need. After steping the code a few
times I understand what Chip was driving at.
Thanks for giving me a good starting point.
Robert