Import Text to Excel

[water]

ProgAndy has written which work pretty fast and handle complex CSV files very well.

The StringSplit approach doesn't handle a text field with an embedded comma correctly.

[water]

And I don't think the function works as it should (sorry mate)

Have you tried to import multi-line text fields? It scrambles your import

Try to import this file:

System - Tables and fields (sqlite_master).txt

I hope you can fix it or better, I hope I am wrong...

GreenCan

Hi GreenCan,

Function _Excel_OpenText just calls an Excel method. And as far as I understand the CSV definition multi-line records aren't supported.

[GreenCan]

You need to use one of the beta versions > 3.3.9.x to run the ExcelEX UDF. The instance parameter for ObjGet isn't available in AutoIt 3.3.8.1.

OK thanks

[GreenCan]

Hi GreenCan,

Function _Excel_OpenText just calls an Excel method. And as far as I understand the CSV definition multi-line records aren't supported.

Might be but I am not talking about official standards but what is required in the practical world . (It's everyday use here at the office) and the attached text file in my previous post is a perfect example that it works.

The example is an export from my SQLite Report Generator, if you still have it on your PC, you can test it yourself, the name of the file is the name of the report.

Otherwise, just rename the .txt file to .csv and double click on it, you will see that Cell E2 is a multi-line cell.

The most complex thing is to create the csv format, _Excel_BookSaveAs won't work for this but by the end it is fairly simple, the only thing that has to be done is quoting the data and it will work. This would require another function.

Note also that in practice, the decimal symbol is often an issue, your example does function on my PC because I have the European format 123.000,10 but if the number is formatted 123,000.10 then it results in Excel interpreting this cell as a string. Strictly speaking, it is not an issue for _Excel_BookOpenText but I just want to mention this because of the code hereunder...

This is how I do it

Global $ListDelimiter = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sList") ; Retrieve standard delimiter symbol
Global $sDecimal = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sDecimal") ; decimal symbol
; I don't handle this but if you export Thousand symbol, mostly not the case in a csv file but...
Global $sThousand = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sThousand") ; Thousand symbol
; $ColItem is the Cell that is currently processed
If StringIsFloat($ColItem) Then
     $ColItem = StringReplace($ColItem, ".", $sDecimal) ; convert Floating Point with decimal symbol compliant with Excel
Else ; If StringInStr($ColItem,@LF)>0 Then
     $ColItem = Chr(34) & StringReplace($ColItem, Chr(34), "'") & Chr(34) ;Replace all " by ' before putting the string between double quotes to avoid Excel conflicts
EndIf
$Row &= $ColItem & $ListDelimiter

And for reading the csv, I use to less difficult approach, I shellexecute the. csv

[water]

I just imported your example text file by renaming it to CSV and doing a double click. It opens in Excel 2010 just fine. If I then open the same file with _Excel_OpenText it looks the same.

Here's the screenshot.

I'm working with the latest version of the UDF.

[GreenCan]

I just imported your example text file by renaming it to CSV and doing a double click. It opens in Excel 2010 just fine. If I then open the same file with _Excel_OpenText it looks the same.

Here's the screenshot.

I'm working with the latest version of the UDF.

You are right!!!! Why did it fail here? let me check

Edit: OK, it does not work with the pipe delimited format, this is what I get after making a carriage return in cell B2

Edited May 8, 2013 by GreenCan

[water]

Note also that in practice, the decimal symbol is often an issue, your example does function on my PC because I have the European format 123.000,10 but if the number is formatted 123,000.10 then it results in Excel interpreting this cell as a string. Strictly speaking, it is not an issue for _Excel_BookOpenText but I just want to mention this because of the code hereunder...

_Excel_BookOpenText allows to specify the DecimalSeparator and the ThousandsSeparator.

Unfortunately Excel decides to ignore it. I was not able to properly import "123,000.30". Will have to investigate ...

[GreenCan]

Yes, I had the same problem with my project, therefore I decided to use the registry settings

I always give you extra work, I feel shame

It looks like the negative numbers are also on the left side (Col E) when using pipes...

[water]

Isn't the minus on the left side correct? I have no Excel available at the moment but OpenOffice displays the minus on the left side too.

Looks like the default format, but can be changed.

[GoldenMike]

Hi i've been reading this topic, and have done all updates to AutoIt Beta 3.3.9.4, and update SCiTe Editor to latest version.

I have also made sure that all Excel Rewrite files are located in the proper script directory.

And that i am running the script in AutoIt Beta

I am running the test script for _Excel_BookAttach because nothing seems to be working.

Local $sWorkbook = @ScriptDir & "\_Excel1.xls"
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
ConsoleWrite(@ScriptDir)
If @error <> 0 Then Return MsgBox(16, "Excel UDF: _Excel_BookAttach Example 1", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox(64, "Excel UDF: _Excel_BookAttach Example 1", "Search by 'filepath':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of cell B3: " & $oWorkbook.Activesheet.Range("B3").Value)

And keep getting this error message:

error: 'Return' not allowed from global scope. which is pointing to the "If @error..." line.

I've been using AutoIt for about a year or two now, and have not encountered a problem like this at a basic level.

Is there something i am missing, or doing wrong?

[water]

Is this the whole script you run?

[GoldenMike]

yes.

i have a larger script that involves getting Data of internet site and moving it to excel. I was having problems with Excel in that script, which prompted me to do some research and update everything.

That script still wasn't working so I started from the basics and just ran an example script to see if that would work.

Which it didn't, so i'm not really sure what to do now.

[BrewManNH]

That snippet you posted has no function in it, so that is why you get the error message about Return in the global scope. Return can't be used anywhere other than inside a function.

[water]

Make sure that you call _Excel_Open before you call any other function of my UDF.

Function _Excel_BookAttach returns the workbook object. To get the Excel application object (might be needed by further functions) use $oWorkBook.Parent.

Change Return to Exit in the script snippet to solve the problem.

Edited May 8, 2013 by water

[GoldenMike]

Water,

Sorry i'm a little lost.

You are telling me to call _Exel_Open, but what if the Excel document is already open, shouldn't the _Excel_BookAttach work? that's how i have always done it with the older versions.

And when you are telling me to use $oWorkBook.Parent would I use it for something like $oWorkbook.Parent.Application.Activesheet.... etc. ?

[GreenCan]

Isn't the minus on the left side correct? I have no Excel available at the moment but OpenOffice displays the minus on the left side too.

Looks like the default format, but can be changed.

Shifting to Oo?

Yes, the format in the text is absolutely correct.

I have made some tests for you all with _Excel_BookOpenText, only the .csv succeeds

The zip file contains the text formats and the xlsx results for each file

_Exceltest.zip

GreenCan

[GoldenMike]

Water,

I figured it out, I understand how to use the excel rewrite scripts now to attach and access files. Thanks.

I'm actually having a bizarre problem with excel which is what lead me to Excel Rewrite in the first place.

I thought the Rewrite would solve it, but it just happened again.

I am running a loop to go through rows in an excel file to extract data, put it into a website, and then take new data from website and place back into excel file.

The first time around everything works fine.

But when it loops for the second time, i believe it is not attaching to the excel file.

I'm getting an @error message = 5 after a _Excel_RangeRead(....) line

I'm not quite sure how to use the excel error handler on this.

Does anyone hear have any pointers, suggestions, or reference to what a 5 error message is, or how to effectively use the error handlers to figure out exactly what is going on?

[Queener]

$oExcel = _ExcelBookNew(1)
$L = 1

for $i = 1 to $aCSV[0]
$aLine = stringsplit($aCSV[$i], ",")
_ExcelWriteArray($oExcel , $L , 1 , $aLine , 0 , 1)
$L += 1

works great, but if I add an if statement to check if file exist; it would only import the first row to excel.

if I change $i = 2 instead of $i = 1; it would only import the second rows onto excel. Any idea on how to import the whole list instead of individual rows?

Here's the whole code:

while 1
If GuiCtrlRead($input1) = "" Then
   MsgBox(0, "Error", "Input cannot be empty")
   ExitLoop

ElseIf FileExists(GuiCtrlRead($input1)) Then
   
_FileReadToArray(GuiCtrlRead($input1), $aCSV)

$oExcel = _ExcelBookNew(1)
$L = 1

for $i = 1 to $aCSV[0]
$aLine = stringsplit($aCSV[$i], ",")
_ExcelWriteArray($oExcel , $L , 1 , $aLine , 0 , 1)
$L += 1

ExitLoop
Else

MsgBox(0, "File Existen", "File does not exist")
ExitLoop

Next
EndIf
ExitLoop
WEnd

correct me if my if statement is badly formatted.

[water]

Water,

Sorry i'm a little lost.

You are telling me to call _Exel_Open, but what if the Excel document is already open, shouldn't the _Excel_BookAttach work? that's how i have always done it with the older versions.

And when you are telling me to use $oWorkBook.Parent would I use it for something like $oWorkbook.Parent.Application.Activesheet.... etc. ?

I hope I can shed some light on the subject.

The part about _Excel_Open is just an information. If you don#t need it, drop it.

Every function of the ExcelEX UDF needs the Excel application object, the workbook object and/or the worksheet object passed as a parameter. Except: _Excel_BookAttach. _Excel-BookAttach returns the workbook object (e.g. $oWorkbook). If you later need the application object you simply use $oWorkbook.Parent.

_Excel_BookAttach should just work fine for you. Nothing else needed.

[water]

Shifting to Oo?

Yes, the format in the text is absolutely correct.

I have made some tests for you all with _Excel_BookOpenText, only the .csv succeeds

The zip file contains the text formats and the xlsx results for each file

_Exceltest.zip

GreenCan

GreenCan, thanks for taking the time to test the _Excel_OpenText function! I will have a look at the data you provided as soon as I find some spare time.

No, I'm not shifting to Oo. But Excel refuses to run on my Ubuntu PC at home. I have Windows running in a VirtualBox but no Office installed.

I hope I can release a new Alpha version the next days for you to play with. I attend a security conference and will be unavailable for a few days.