Page 1 of 1
moving csv data from column to row
Posted: Fri Jul 27, 2007 9:37 pm
by shawnvdm
I seem to have lost my first entry, so here it is again. I last programmed some 20 yrs ago with a zxspectrum and am finding myself a bit rusty. I have a csv file with data arranged as follows:
data-name1, timestamp, useless-number-data, number-data
data-name1, timestamp, useless-number-data, number-data
etc
data-name2, timestamp, useless-number-data, number-data
data-name2, timestamp, useless-number-data, number-data
etc
I would like to put it into a csv file I can inport into excel that has the dataname as a column title with the data corresponding to the timesatmp underneath. However some of the data does not always have the same timestamp. e.g.
Timestamp, dataname1, dataname2, dataname3
timestamp1, data-dataname1, data-dataname2, blank
timestamp2, blank, blank, data-dataname3
I started out thinking this would be easy but I seem to have lost the understanding on how to do this.
Any suggestions?
Posted: Sat Jul 28, 2007 11:31 am
by Stoves
Could you post some of the contents of the original csv file? Are you trying to rearrange the data using QBasic? or some other language/program?
If I understand what you're needing, it might be easiest to just import the original csv file into Excel and then arange the data in Excel.
Posted: Sat Jul 28, 2007 1:05 pm
by shawnvdm
Hi Stoves
The data is
"Instrument 5", "2004/05/06 07:55", "554","1004"
"Instrument 5", "2004/05/06 07:58", "553","999"
"Instrument 5", "2004/05/06 08:05", "556","1100"
list carries on
"Instrument 6", "2004/05/06 07:55", "553","143"
"Instrument 6", "2004/05/06 07:57", "553","146"
"Instrument 6", "2004/05/06 07:58", "553","142"
"Instrument 6", "2004/05/06 08:05", "553","143"
carries on like this for multiple instruments
I could just import it but I seem to find evaluating the old data easier if I can look at a time and compare the instruments accross a row. I have tried programing excel but this object orientated stuff seems beyond me so I decided to switch back to good old basic that I knew at one stage. Ha, it seems its more a matter of thought than just knowing a few print me's. I no longer have a copy of qbasic, but have been playing with freebasic. It just appears to me that their seems to be more resources for qbasic. I may be under the wrong impression that they are similair?
My excel import would hopefully look like this:
Time Stamp Instrument 1 Instrument 2 Instrument 3 Instrument 4
2004/05/06 07:55..1004.........143.................155..................1100
2004/05/06 07:56..994...........123...............154................1099
(I used .... as the system deletes spaces) I have been playing with manually moving things, but what throws me out is that the timestamps are not always the same.
Posted: Sun Jul 29, 2007 7:46 pm
by shawnvdm
this is as far as I got.
Code: Select all
rem Declare variables
numrecords=10000000 rem make bigger than needed
dim readdataset$(numrecords)
dim shared testdataset$(numrecords)
dim shared readtimestamp$(numrecords)
dim mill$(numrecords)
dim readdata$(numrecords) rem as double
dim shared uniquechange(numrecords)
dim shared as integer uniquerecords
row=0
Rem Get file names
Input "Please supply the data file name to read (incl .csv): ";inputfile$
readfile$=inputfile$
Input "Please supply the output file name (incl .csv): ";outputfilename$
outfile$=outputfilename$
uniqueorderfile$="unique-data.cvs"
Open uniqueorderfile$ for output as #3
Rem Read file into matrix
Open readfile$ for input as #1
rownum=lof(1)
print "rownum = ",rownum
do until eof(1)
if row=0 then
input #1, datasetheader$,timestampheader$,millheader$,dataheader$
print "Row ";row;" ";datasetheader$;" ";timestampheader$;" ";millheader$;" ";dataheader$
print #3,"Row ";row;" ";datasetheader$;" ";timestampheader$;" ";millheader$;" ";dataheader$
row=row+1
else
input #1, readdataset$(row),readtimestamp$(row),mill$(row),readdata$(row)
print "Row ";row;" ";readdataset$(row);" ";readtimestamp$(row);" ";readdata$(row)
print #3, "Row ";row;" ";readdataset$(row);" ";readtimestamp$(row);" ";readdata$(row)
row=row+1
Rem input "press any key",key$
endif
loop
close #1
Rem find number unique headers
rowtotal=row-1
rowtest=0
rowkeep=0
change=0
while rowtest<rowtotal> cvsfile(index, x+1) Then
Swap cvsfile(0,x), cvsfile(0,x+1)
Swap cvsfile(1,x), cvsfile(1,x+1)
Swap cvsfile(2,x), cvsfile(2,x+1)
Swap cvsfile(3,x), cvsfile(3,x+1)
outoforder = true
End If
Next
Loop While outoforder
End Sub
Sub bubblesorttestdata
Dim As Integer outoforder, x
Do
outoforder = false
For x = 0 To uniquerecords-2
If testdataset$(x) > testdataset$(x+1) Then
Swap testdataset$(x), testdataset$(x+1)
outoforder = true
End If
Next
Loop While outoforder
End Sub
rem -----------------------------------------------
Open readfile$ For Input As #1
'' read in the file
numlines = 0
Do
''"Instrument 5", "2004/05/06 07:55", "554","1004"
Input #1, cvsfile(0,numlines),cvsfile(1,numlines),cvsfile(2,numlines),cvsfile(3,numlines)
numlines+=1
If numlines > arraysize Then
arraysize+=100
Redim preserve cvsfile(0 To 3, 0 To arraysize)
End If
Loop While Not Eof(1)
'' sort by instrument name
bubblesortcvs 0
'' sort by timestamp
bubblesortcvs 1
bubblesorttestdata
''print result
Rem First print the Header to the file
Rem Print "Time Stamp";
Rem for x=0 to (uniquerecords)
Rem print testdataset$(x);",";
Rem next x
Rem Rem Print the data
Rem temp = ""
Rem For i = 0 To numlines-1
Rem If cvsfile(1,i) <> temp Then
Rem temp = cvsfile(1,i)
Rem Print Rem start a new line
Rem Print temp; " "; rem print the timestamp
Rem found=false
Rem End If
Rem For instrument=0 to (uniquerecords)
Rem If cvsfile(0, i) = testdataset$(instrument) Then
Rem Print cvsfile(3, i);",";
Rem found = true
Rem Continue For rem don't know what this does
Rem End If
Rem If found= false Then
Rem Print ",";
Rem found = false
Rem endif
Rem next instrument
Rem Next
Print
Print "Please be patient as I write the file now "
Rem create output file
Open outfile$ for output as #2
Print #2, """TimeStamp""";
for x=0 to (uniquerecords)
print #2, """";testdataset$(x);""",";
next x
temp = ""
For i = 0 To numlines-1
If cvsfile(1,i) <> temp Then
temp = cvsfile(1,i)
Print #2,
Print #2, """";temp; """"; Rem the "," tells csv new record
found=false
End If
For instrument=0 to (uniquerecords)
If cvsfile(0, i) = testdataset$(instrument) Then
Print #2, """";cvsfile(3, i);""","; rem the "," says new record
found= true
Continue For rem don't know what this does
End If
If found= false Then
Print #2, ;",";
found = false
endif
next instrument
Next
rem input "press any key";key$