Kabuzi

Excel Array to text file with separated data

4 posts in this topic

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:

#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!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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:

BOLpGXs.png

Edited by SadBunny
Cleaner code

Roses are FF0000, violets are 0000FF... All my base are belong to you.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by Kabuzi

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