moving csv data from column to row

[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?

[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.

[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.

[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$