Jump to content
Sign in to follow this  
Queener

Import Text to Excel

Recommended Posts

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).


Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
mikell

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

Share this post


Link to post
Share on other sites
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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
water

I can second that.

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.


Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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 ",")

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Queener

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


Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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).


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
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 by asianqueen

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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 by boththose

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
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...


Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites
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)

  • Like 1

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
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


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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 by asianqueen

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

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
Sign in to follow this  

×