Sign in to follow this  
Followers 0
wutzke

Arrays - Transpose

15 posts in this topic

#1 ·  Posted (edited)

I have an array

Dec '04       146
Nov '04         78
Oct '04          53
Sep '04         38
Aug '04         48
Jul '04           54
Jun '04          27
May '04         19
Apr '04          68
Mar '04          45
Feb '04          46
Jan '04          38

That I want to transpose to

Dec '04 Nov '04 Oct '04 Sep '04 Aug '04 Jul '04 Jun '04 May '04 Apr '04 Mar '04 Feb '04 Jan '04
146     78        53        38       48       54         27       19          68         45      46      38

Using Excel is easy enough to using transpose via Paste Special

But I have a comma separated file with thousands of lines and think it might be quicker to use AU to pre-processor

I'm reading the array this way

$inc = 0
; Read in lines of text until the EOF is reached
While 1
    $line = FileReadLine($file)
    $inc = $inc + 1
    If @error = -1 Then ExitLoop
$info = StringSplit ($line, ",")
$myArray[$inc][1]=$info[1]
$myArray[$inc][2]=$info[2]
   MsgBox(0, $myArray[$inc][1], $myArray[$inc][2])
Wend
Edited by wutzke

Share this post


Link to post
Share on other sites



Try this. WARNING: NO ERROR CHECKING IS DONE! I leave that up to you!!

#include <array.au3>
#include <file.au3>

dim $lines
$input_filename = "c:\test.txt"
$output_filename = "c:\test_transp.txt"

_FileReadToArray($input_filename,$lines)

$outline1 = ""
$outline2 = ""

for $n = 1 to $lines[0]
    $splitted = StringSplit($lines[$n],",")
    $outline1 &= $splitted[1] & ","
    $outline2 &= $splitted[2] & ","
Next

FileWriteLine($output_filename,$outline1)
FileWriteLine($output_filename,$outline2)

test.txt looks like this:

Dec '04,146

Nov '04,78

Oct '04,53

Cheers

Kurt


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

Have a look at Ubound & ArrayAdd.

You could do a For/Next-loop to create a new array using [Re]Dim and Ubound.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

maybe

for $i = 0 to $arraycount
    $newArray1 = $array[0,$i]
    $newArray2 = $array[1,$i]
Next

where $arraycount equalling the columns

then write that to a csv

Edited by wutzke

Share this post


Link to post
Share on other sites

$sFile = @DesktopDir & '\sretest.txt'
MsgBox(0, '', _IDONTKNOWWHATTOCALLTHISFUNCTION($sFile))

Func _IDONTKNOWWHATTOCALLTHISFUNCTION($hFile)
    If Not FileExists($hFile) Then Return SetError(1, 0, 0)
    Local $sString = FileRead($hFile), $sHold = '', $sHoldNum
    Local $aDates = StringRegExp($sString, "(\w{3}\s+\'\d{2})\s", 3)
    Local $aNum = StringRegExp($sString, "\w{3}\s+\'\d{2}\s*(\d+)", 3)
    If Not IsArray($aDates) Or Not IsArray($aNum) Then Return SetError(2, 0, 0)
    For $iCC = 0 To UBound($aDates) - 1
        $sHold &= $aDates[$iCC] & @TAB
        $sHoldNum &= $aNum[$iCC] & _SpaceConvert(StringLen($aDates[$iCC]), StringLen($aNum[$iCC]))
    Next
    $sHold = StringTrimRight($sHold, StringLen(@TAB))
    $sHoldNum = StringTrimRight($sHoldNum, StringLen(@TAB))
    Return $sHold & @CRLF & $sHoldNum
EndFunc

Func _SpaceConvert($sLen1, $sLen2)
    Local $sHold = ''
    For $iCC = 1 To $sLen1 - $sLen2
        $sHold &= Chr(32)
    Next
    Return $sHold & @TAB
EndFunc


[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

That looks great

I'll see if that works with the entire data chuck.

2300-04-CLO          CLOTHING
     Sep '06      459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000   
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000   
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000   
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000   
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000   
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                           
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000

Share this post


Link to post
Share on other sites

That looks great

I'll see if that works with the entire data chuck.

2300-04-CLO          CLOTHING
     Sep '06      459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000   
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000   
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000   
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000   
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000   
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                           
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000
It worked here with the above... you can adjust the tabs and such to get more spacing.

[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

Thanks for your help here.

I started looking at this again in a different way.

The following code works right, except I get an error

called from the Array.au3

Array variable has incorrect number of subscripts or subcript dimension range exceeded

#include<Array.au3>
$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf



Dim $myArray[60][4]  

$lineCount = 0
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $lineLen = StringLen($line)
    $lineCount = $lineCount + 1
    $line = StringLeft($line, 107)
    $chunks = 0
    $inc = 0
    While $chunks < 4
        $chunkStart=($chunks*26)+1
        $strChunk=StringStripWS(StringMid($line,$chunkStart,27),3)
        $aDate=StringLeft($strChunk,7)
        $aNum=StringRight($strChunk,10)
        $inc = $inc +1      
        $myArray[$inc][1]=$aDate
        $myArray[$inc][2]=$aNum
;       MsgBox(0, $myArray[$inc][1], $myArray[$inc][2])
        $chunks = $chunks +1
    WEnd
Wend

FileClose($file)
_FileWriteFromArray("testout.txt",$myArray,1)

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Thanks for your help here.

I started looking at this again in a different way.

The following code works right, except I get an error

called from the Array.au3

Array variable has incorrect number of subscripts or subcript dimension range exceeded

Dim $myArray[60][4]

as I don't know your input file, I see some possible error sources.

1.) You dim the array with [60][4]. If your file contains more than 60 lines, you will run into that error.

2.) I suspect an error in your logic. You increase the array index within the second while loop. $inc = $inc +1. That can lead to the error you described, as problem nr. 1.) could occur much faster.

3.) _FileWrtiteFromArray is NOT designed to write a 2-dim array to a file!

It would help if you could post your data source.

Cheers

Kurt

Edited by /dev/null

__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

Thanks,

Even if I do

;_ArrayDisplay ( $myArray, "results" )

instead of writing to data I get the error

Sep '06   459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000  
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000  
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000  
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000  
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000  
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                          
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Thanks,

Even if I do

;_ArrayDisplay ( $myArray, "results" )

instead of writing to data I get the error

well... same reason. NOT designed for 2-dim arrays!

Sep '06   459.0000   Dec '05      127.0000   Mar '05      184.0000   Jun '04       27.0000  
     Aug '06       68.0000   Nov '05       58.0000   Feb '05       50.0000   May '04       19.0000  
     Jul '06       74.0000   Oct '05       50.0000   Jan '05       30.0000   Apr '04       68.0000  
     Jun '06       51.0000   Sep '05      530.0000   Dec '04      146.0000   Mar '04       45.0000  
     May '06       40.0000   Aug '05       83.0000   Nov '04       78.0000   Feb '04       46.0000  
     Apr '06       34.0000   Jul '05       42.0000   Oct '04       53.0000   Jan '04       38.0000  
     Mar '06       44.0000   Jun '05       67.0000   Sep '04       38.0000                          
     Feb '06       32.0000   May '05       53.0000   Aug '04       48.0000                              
     Jan '06       38.0000   Apr '05       57.0000   Jul '04       54.0000
is this the format of your input file?

Cheers

Kurt

Edited by /dev/null

__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

well... same reason. NOT designed for 2-dim arrays!

is this the format of your input file?

Cheers

Kurt

here is something to get you started. There are still some errors in your logic, but I'm sure you'll figure that out ;)

#include<Array.au3>
#include<File.au3>

$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf



Dim $myArray[1] ; <== CHANGED THIS

$lineCount = 0
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $lineLen = StringLen($line)
    $lineCount = $lineCount + 1
    $line = StringLeft($line, 107)
    $chunks = 0
    $inc = 0
    While $chunks < 4
        $chunkStart=($chunks*26)+1
        $strChunk=StringStripWS(StringMid($line,$chunkStart,27),3)
        $aDate=StringLeft($strChunk,7)
        $aNum=StringRight($strChunk,10)
        ;$inc = $inc +1        
        ;$myArray[$inc][1]=$aDate
        ;$myArray[$inc][2]=$aNum
        _ArrayAdd($myArray,$aDate & " " & $aNum); <== ADDED THIS
        ;MsgBox(0, $myArray[$inc][1], $myArray[$inc][2])
        $chunks = $chunks +1
    WEnd
Wend

_ArrayDisplay($myArray,"Test"); <== ADDED THIS
FileClose($file)
_FileWriteFromArray("testout.txt",$myArray,1); <== CHANGED THIS

Cheers

Kurt


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

yes it is

what is a better way to write a multi-dimensional array to a file?

Edited by wutzke

Share this post


Link to post
Share on other sites

yes it is

what is a better way to write a multi-dimensional array to a file?

loop through the array and write each line of the outputfile with FileWriteLine() or FileOpen/FileWrite/FileClose. Why do you use a 2-dim array for the result anyway?

Cheers

Kurt


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

Share this post


Link to post
Share on other sites

I will be creating an Excel file later

#include<Array.au3>
#include<File.au3>
$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf

; Read in lines of text until the EOF is reached

; msg "configure next stock item"
;time out in 2 seconds and go to Next

;format  
;mgf           |date     |date
;stock Number  |num sales|num sales
; auto group for excel 


Dim $myArray[41][4]  
    $inc = 0
$lineCount = 0
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $lineLen = StringLen($line)
    $lineCount = $lineCount + 1
    $line = StringLeft($line, 107)
    $chunks = 0
    While $chunks < 4
        $chunkStart=($chunks*26)+1
        $strChunk=StringStripWS(StringMid($line,$chunkStart,27),3)
        $aDate=StringLeft($strChunk,7)
        $aNum=StringRight($strChunk,10)
        $inc = $inc +1      
        $myArray[$inc][1]=$aDate
        $myArray[$inc][2]=$aNum
        $chunks = $chunks +1
    WEnd
Wend

$rows = UBound($myArray)
$cols = UBound($myArray, 2)
$dims = UBound($myArray, 0)


FileClose($file)

    $outline1 =  ""
    $outline2 =  ""
$inc = 0
$output_filename = "testout.csv"
while $inc < $rows -1
    $inc = $inc +1  
    $outline1 =  $outline1 & $myArray[$inc][1] & "," 
    $outline2 =  $outline2 & $myArray[$inc][2] & "," 
wend    

    MsgBox(0,"",$outline1)
    FileWriteLine($output_filename,$outline1)
    FileWriteLine($output_filename,$outline2)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0