Excel Array to text file with separated data

[Kabuzi]

Hello experts,

 

I am new to Autoit and having a bad time trying to read an excel column and getting each cell value separated by , and '

Ex: 'value1','value2','value3'

This is what i am doing:

expandcollapse popup

#include <File.au3>
#include <Excel.au3>
#include <Constants.au3>

;----------------------------------------------------------------------------

Local $sFilePath = @ScriptDir & "\bloqueios_excluidos.xlsx"
Local $oExcel = _Excel_Open()
Local $sColuna
Local $aResult

;----------------------------------------------------------------------------



; Script Start

If Not FileExists($sFilePath) Then
    MsgBox(16, '', 'Excel não encontrado!')
    Exit
EndIf
Local $oWorkbook = _Excel_BookOpen($oExcel,$sFilePath)

Local $sColuna = InputBox("Gerador de scripts", "Insira o primeiro e o ultimo campo separados por dois ponto EX: F2:F8 ",""," m")

"'" & $aResult & "'," = _Excel_RangeRead($oWorkbook, 1, $sColuna , 1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;_ArrayDisplay($aResult)

$TextFile = "TextFile.txt"
FileOpen($TextFile, 2)

    _FileWriteFromArray($TextFile, $aResult)
$oExcel.activeworkbook.saved = 1
$oExcel.quit
$oExcel = 0

FileClose($TextFile)
Run("NotePad.exe " & $TextFile,"", @SW_MAXIMIZE)

Anyone how should I do this?

Thank you!

[SadBunny]

26 minutes ago, Kabuzi said:

expandcollapsepopup

"'" & $aResult & "'," = _Excel_RangeRead($oWorkbook, 1, $sColuna , 1)

That contains multiple problems. First, you cannot assign something to a concatenated string. Second: as described in the helpfile, the _Excel_Rangeread returns an array for multiple cells and a string for just one cell.

Consider this example:

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

Local $sFilePath = "c:\tmp\Book1.xlsx"
Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)

dothing("A1")
dothing("A1:B1")
dothing("¡²³")

Func dothing($query)
    $aResult = _Excel_RangeRead($oWorkbook, Default, $query, 1)
    ; Note: $aResult could be 0 (indicating an error), or a string, or an array.
    if $aResult = 0 then ; or alternatively:    if @error then
        MsgBox(0, 0, "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ElseIf IsArray($aResult) Then
        _ArrayDisplay($aResult)
    Else
        MsgBox(0, 0, "Single value retrieved: " & $aResult)
    EndIf
EndFunc   ;==>dothing

I populated my c:\tmp\Book1.xlsx as follows:

Edited July 20, 2016 by SadBunny
Cleaner code

[water]

Read the whole column into an array, process the array and create the CSV string.

[Kabuzi]

Ty guys, I finally finished my code. I will leave it here in case someone need something like this in the future...

What I was trying to do is read a column from a .xlsx and then turn into SQL queries.

I followed what Water said and got this:

 

$i = 0
    While $i < $Linhas
        If IsNumber($aExcluir[$i]) Then
            $sScript = $sScript&"'" & $aExcluir[$i] & "',"
        EndIf
        $i = $i+1
    WEnd

- All the the information I got with excel _Excel_RangeRead is in $aExcluir.

- $Linhas is the number of positions in $aExcluir.

- This code is concatenating each value in the string $sScript .

At the end I put everything in a .sql file using FileWrite("Script.sql",$sScript)

 

Edited July 25, 2016 by Kabuzi