text file to array

[goodmanjl531]

Hope someone has some ideas on this.  I need to get a CSV file uploaded into an array so i can manipulated the data...

I have it working perfectly using _ExcelReadSheetToArray  My problem is I will be running this on a machine that only has excel viewer so it cant open the csv file..

the csv file i created is from a SQL query  the data pulled looks like this...

ITM_ID                  ,DEPT_NBR,CURT_PRC                ,ORIG_PRC                
------------------------,--------,------------------------,------------------------
1,1,0.01,1.75
2,2,0.01,1.75
3,7,0.01,1.75
4,4,0.01,1.75
.
.
.
99989,12,6.5,6.5
99990,8,2.75,5.5
99997,12,6,12
,,,
(51861 rows affected),,,

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <string.au3>

SplashTextOn("Markdowns", "Creating Markdown File" & @CRLF & "Please Wait...", "300", "100", "-1", "50", 0, "Arial Black", "12", "700")

If FileExists("C:\ITM-PULL.csv") Then FileDelete("C:\ITM-PULL.csv")
FileInstall("c:\autoit\Markdown.bat", "c:\temp\", 1)
FileChangeDir("c:\temp")
RunWait(@ComSpec & " /c " & 'markdown.bat', "")
FileDelete("C:\temp\markdown.bat")

Do
    Sleep(1000)
Until FileExists("C:\ITM-PULL.csv")

FileChangeDir("c:\")
$sFilePath1 = "C:\itm-pull.csv"
$oExcel = _ExcelBookOpen($sFilePath1, 0)
$aArray = _ExcelReadSheetToArray($oExcel, 3)
_ExcelBookClose($oExcel, 0, 0)
If FileExists("c:\wtm32\markdown.p") Then FileDelete("c:\wtm32\markdown.p")
$numofrows = $aArray[0][0]
$file = FileOpen("C:\WTM32\Markdown.p", 1)

For $x = 1 To $numofrows - 2
    $y = 1
    $text = Number($aArray[$x][$y])
    Select
        Case $text < 10
            FileWrite($file, "0000" & $text)
        Case $text < 100
            FileWrite($file, "000" & $text)
        Case $text < 1000
            FileWrite($file, "00" & $text)
        Case $text < 10000
            FileWrite($file, "0" & $text)
        Case Else
            FileWrite($file, $text)
    EndSelect

    $y = 2
    $text = Number($aArray[$x][$y])
    Select
        Case $text < 10
            FileWrite($file, "0" & $text)
        Case Else
            FileWrite($file, $text)
    EndSelect

    $y = 3
    $text = Number($aArray[$x][$y]) * 100
    Select
        Case $text < 10
            FileWrite($file, "0000" & $text)
        Case $text < 100
            FileWrite($file, "000" & $text)
        Case $text < 1000
            FileWrite($file, "00" & $text)
        Case $text < 10000
            FileWrite($file, "0" & $text)
        Case Else
            FileWrite($file, $text)
    EndSelect

    $y = 4
    $text = Number($aArray[$x][$y]) * 100
    Select
        Case $text < 10
            FileWrite($file, "0000" & $text)
        Case $text < 100
            FileWrite($file, "000" & $text)
        Case $text < 1000
            FileWrite($file, "00" & $text)
        Case $text < 10000
            FileWrite($file, "0" & $text)
        Case Else
            FileWrite($file, $text)
    EndSelect
    ;FileWriteLine($file, @CRLF)
Next
;FileWriteLine($file, @CRLF)
FileClose($file)

SplashOff()

Thanks for any and all help  :-)

[mLipok]

I think this piece of knowledge you need it.

http://www.autoitscript.com/wiki/ADO

http://www.autoitscript.com/wiki/ADO_Example_TextFile

[mikell]

Otherwise you can try Csv2Array

http://www.thorsten-willert.de/Themen/AutoIt/_CSV2Array.au3?a

[Melba23]

goodmanjl531,

If you have the Beta 3.3.11.3 then _FileReadToArray will do it for you.

M23

[goodmanjl531]

Otherwise you can try Csv2Array

http://www.thorsten-willert.de/Themen/AutoIt/_CSV2Array.au3?a

This works perfect for what i need.  I should know this but having a hard time finding this.  I know for a fact the array will ALWAYS be 4 columns  I need to know how many rows so when i start to read thru each row to tweak the data i know when to stop..I used the 0,0 spot in the array when it pulled from the excel sheet but this function doesn't give the total # of rows or columns...

[Melba23]

goodmanjl531,

Dog eaten the Help file?

Try looking at UBound.

M23

[goodmanjl531]

goodmanjl531,

Dog eaten the Help file?

Try looking at UBound.

M23

I found it right after i replied,,,, sorry I so should have looked a few more minutes before asking thanks though