Import Text to Excel

[Queener]

Currently I've been using short-cut key to import text:

WinWaitActivate("Book1 - Excel","")
Send("{ALTDOWN}ddd{ALTUP}")
WinWaitActivate("Select Data Source","")
sleep(1000)
Send("{TAB}{TAB}{TAB}")
sleep(1000)
Send("test.txt{ENTER}")

was wondering is there a better to do this including delimiting (For Delimit, I use the same method tabs and enter).

[mikell]

Did you try the _Excel* funcs from the udf Excel.au3 included in the autoit install ?

[BrewManNH]

Have you looked at the Excel functions that are in AutoIt, or the new being written by water? Either of these would be better than using Send with Excel.

[water]

I can second that.

I have no Excel available at the moment but what do you want to do?

[Queener]

I got a list of 4 columns (Testing)

id, name, last name, age

The database extracted tons of records and I wanted it to delimited by commas. After done some reading; I was able to open the excel apps by

Local $sFilePath1 = @DesktopDir & "\Test.txt" ;This file should already exist
   Local $oExcel = _ExcelBookOpen($sFilePath1)

Now stuck on how to delimited by commas. I couldn't find the right function to do that. I would assuming it's

stringsplit, but tested with no luck.

[water]

To import a comma delimited text file please check function _Excel_OpenText from my ExcelEX UDF.

[Queener]

Tried; we're missing Excel Rewrite.au3. In addition, I would assuming to delimited by commas it would be written like this?

Local $sTextFile = @DesktopDir & "\test.txt"
   _Excel_BookOpenText($xlDelimited ",")

[water]

Not exactly. Please have a look at the help file which comes for every function.

[Queener]

where do I find excel rewrite.au3 so I can test the script?

[water]

Click on the last entry in my signature (ExcelEX).

But be warned: It's an early alpha and some of the function names, parameters etc. might change in a next alpha or beta version.

[iamtheky]

The database extracted tons of records

can we see a sample of this extract. if you have all the data in a txt, and just need to delimit the data with commas, we need to see that data (or a small portion thereof). At that point excel should open the .csv natively (a simple shellexecute with no need for _Excel or _ExcelEx functions).

[Queener]

sorry Water for given you a hard time, but I'm getting all sorts of error coming at me that it doesn't know any function such as $oAppl, _excel_bookOpenText.au3. I included _excel_bookOpenText.au3; not sure why it's given me lots of errors.

Here's a simple and it will be in this format for all of the rest

First,Last,Age,Budget
james,bond,21,3000
john,bond,22,4000
jamie,bond,23,5000
jonu,bond,24,6000

Edited May 7, 2013 by asianqueen

[water]

Have a look at _Excel_OpenText.au3 and you'll see how it has to be done

[iamtheky]

So when you copy that into notepad and name it test.csv, and open that in excel

is that how you want the excel document to appear?

edit: Because it looks as I would expect, but I am late to this party and may be missing the objective, as per usual.

Edited May 7, 2013 by boththose

[Queener]

that would work... Yep; work out great if I saved as csv. But I still wants to know the import method with txt and delimited. No luck in getting it to work the way I want.

I figure what I did wrong with the other part. I rename ExcelEX to Excel Rewrite.au3. Works now, but stuck here on this error.

Only thing I'm not sure is; does it require

Local $aField1[2] = [1, $xlTextFormat]
    Local $aField2[2] = [2, $xlTextFormat]
    Local $aField3[2] = [3, $xlGeneralFormat]
    Local $aField4[2] = [4, $xlDMYFormat]
    Local $aField5[2] = [5, $xlTextFormat]
    Local $aFieldInfo[5] = [$aField1, $aField2, $aField3, $aField4, $aField5]

When I only ask to import and delimited by commas...

[iamtheky]

#Include <Excel.au3>
#Include <Array.au3>
#Include <File.au3>


Global $aCSV

_FileReadToArray("csv_import.txt" , $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
next

_ExcelBookSaveAs($oExcel , @ScriptDir & "\parse_test.xls")
_ExcelBookClose($oExcel)

[GreenCan]

Have a look at _Excel_OpenText.au3 and you'll see how it has to be done

water,

I get an error while testing _Excel_BookOpenText.au3

Rewrite.au3(289,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\Alain\Desktop\Excel rewrite\_Excel_BookOpenText.au3 - 1 error(s), 0 warning(s)

Strangely enough, I don't get the error when I run the example from the prompt, script abort only when started from SciTE...

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

[water]

Only thing I'm not sure is; does it require

Local $aField1[2] = [1, $xlTextFormat]
Local $aField2[2] = [2, $xlTextFormat]
Local $aField3[2] = [3, $xlGeneralFormat]
Local $aField4[2] = [4, $xlDMYFormat]
Local $aField5[2] = [5, $xlTextFormat]
Local $aFieldInfo[5] = [$aField1, $aField2, $aField3, $aField4, $aField5]

When I only ask to import and delimited by commas...

No, it is not needed. It's optional and only required if you need to tell Excel the format of the different columns.

You just need to pass the first two parameters: Excel object and the full path of the file you want to import.

[water]

water,

I get an error while testing _Excel_BookOpenText.au3

Rewrite.au3(289,56) : ERROR: ObjGet() [built-in] called with wrong number of args.
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\Alain\Desktop\Excel rewrite\_Excel_BookOpenText.au3 - 1 error(s), 0 warning(s)

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.

[Queener]

#Include <Excel.au3>
#Include <Array.au3>
#Include <File.au3>


Global $aCSV

_FileReadToArray("csv_import.txt" , $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
next

_ExcelBookSaveAs($oExcel , @ScriptDir & "\parse_test.xls")
_ExcelBookClose($oExcel)

works perfectly... Thanks a bunch!!! Glad ExcelEX is not needed XD! quite complicates. See, I thought stringsplit plays a part of it!!! hahahahaa.... I was reading about stringsplit, but just not sure how it's done. Edited May 8, 2013 by asianqueen