Sign in to follow this  
Followers 0
roofninja

converting txt file into an excel spreadsheet

24 posts in this topic

Several years ago, with the help of the community, we were able to create a program that would convert a txt file into an Excel spreadsheet. I would account for the file already having existed and having data already in it. Now that the Excel has been added into AutoIt, I would update it and with better code(I hoped). I am not sure what I am doing wrong here. I have been starring at this for several days now and can't get it to work.

Here is the code so far and a sample txt file.

I left extra things that I have tried in the past and the progress bar stuff. I wasn't sure if it was causing the program from working correctly.

#Include <Excel.au3>
#include <Array.au3>
#Include <File.au3>
;------------------------------------
#include <Misc.au3>
if _Singleton("Warning",1) = 0 Then
Msgbox(0,"Warning","An occurence of your program is already running")
Exit
EndIf
;-------------------------------------
Local $max
;$count = 5
Dim $nsheet[1], $sheet[1]
;--------------------------------
Local $bb, $count
;-------------------------------------
$file = FileOpenDialog("Inventory",@ScriptDir,"(*.txt)")
If @error Then
MsgBox(4096,"","No File(s) chosen")
Exit
EndIf
;This section opens all files for use
;$file = FileOpen($file, 0)
;$file = FileOpen(@ScriptDir&"\inventory.txt", 0)
; Check if file opened for writing OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open inventory.txt data file.")
    Exit
EndIf
Local $oExcel = _ExcelBookOpen(@ScriptDir&"\test2_inv.xls",0, False);this will open the inventory.xls file.
If @error >= 1 Then
Local $oExcel = _ExcelBookNew(0);this is here to create the inventory.xls file if it does not exist.
;--------------------------------------------
;title line
_ExcelWriteCell($oExcel,"Room",1,1)
_ExcelWriteCell($oExcel,"Lab Type",1,2)
_ExcelWriteCell($oExcel,"Teacher",1,3)
_ExcelWriteCell($oExcel,"Roam",1,4)
_ExcelWriteCell($oExcel,"Make",1,5)
_ExcelWriteCell($oExcel,"Model",1,6)
_ExcelWriteCell($oExcel,"OS",1,7)
_ExcelWriteCell($oExcel,"CPU",1,8)
_ExcelWriteCell($oExcel,"RAM",1,9)
_ExcelWriteCell($oExcel,"Service Tag",1,10)
_ExcelWriteCell($oExcel,"FAIS",1,11)
_ExcelWriteCell($oExcel,"PS",1,12)
_ExcelWriteCell($oExcel,"NCWise",1,13)
;--------------------------------------------------
EndIf
#cs
_FileReadToArray($file,$nsheet)
$sheet =_ExcelReadSheetToArray($oExcel)
Do
if $nsheet<>";" then
_ExcelWriteCell($oExcel,$nsheet,($sheet[0][0]+1)) ;this writes all the data from the array
Else
EndIf
Until $nsheet=$nsheet[0]
;_ExcelWriteArray($oExcel,($sheet[0][0]+1),1,$nsheet)
#ce
;------------------------------------------------------
;This is where all the work is done.
;ProgressOn ("","Processing the records","0 percent",50,300)
;For $i = 10 to 100 step 10 ;for the progress bar
While 1
$line = FileReadLine($file)
If @error = -1 Then ExitLoop
;----------------------------------------------------
$skip = StringReplace($line,"*","",0,0)
if $skip<>";" then
  _ArrayAdd($nsheet,$line) ;this reads the file into an array
  $bb = $bb+1
EndIf
if $skip = ";" then
;ProgressSet($i, $i & " percent")
  $count = $count+1 ;this counts the number of records that are parcessing
  ;$sheet = _ExcelSheetNameGet($oExcel);finds the name of the active sheet
  ;$array = _ExcelSheetUsedRangeGet($oExcel,$sheet);finds the last line of data.
  $sheet =_ExcelReadSheetToArray($oExcel)
  $max = _ArrayMaxIndex($sheet) +1
  ;if $eof <= $array[3] Then ;this keeps us from copiing over data in sheet
  ;$eof = ($array[3] +1)
  ;EndIf
;-------------------------------------------------------
  if $bb = 13 then
   _ArrayDisplay($nsheet)
  for $aa = 1 to 13
   ;_ExcelWriteCell($oExcel,$nsheet[$aa],$sheet[1][0]+1) ;this writes all the data from the array
   _ExcelWriteCell($oExcel,$nsheet[$aa],$max,$aa) ;this writes all the data from the array
  Next
  EndIf
;-----------------------------------------
  for $ts = 1 to 13
  _ArrayDelete($nsheet,$ts) ;this deletes the array
  Next
  $bb = 0
EndIf ;$skip=";"
Wend
;Next ;for the progress bar
;ProgressSet(100 , "Done", "Complete")
;ProgressOff()
; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookSaveAs($oExcel,@ScriptDir&"\test2_inv.xls","xls",0,0)
; And finally we close out
_ExcelBookClose($oExcel)
FileClose($file) ;for inventory.txt
MsgBox(0,"Convert txt inventory","This program has converted "&$count&" records of Computers in the TXT inventory into your EXCEL inventory",5)
Exit

sample txt file

LG
NA
Smith
Yes
Dell
D520
XP
P4
1015
7LJCSC1
200-9999
Yes
Yes
;***************************************
roam
NA
Cabin
Yes
Dell
D520
XP
P4
1015
7LJCSC1
200-7777
Yes
Yes
;***************************************
ROAMT
NA
Lacy
No
Gateway
blank
XP
P4
2039
0475528
200-8888
No
No
;***************************************
WL2
NA
Jones
No
HP
Book2530p
XP
P4
2973
0432112
200-7777
No
No
;***************************************
ROAMT
NA
Wilson
No
Gateway
E295C
XP
P4
2039
0475528
400-4444
No
No
;***************************************

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites



I welcome anyone to re-write it.

What it currently is doing is that it is in an infant loop somewhere. Also I can't figure out how to tell the program the last line that has data on it and make it plus one for where to write the new data to the spreadsheet.


RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

The infinite loop is this:

;This is where all the work is done. ;ProgressOn ("","Processing the records","0 percent",50,300) ;For $i = 10 to 100 step 10 ;for the progress bar While 1

You dont have exitloop from there.

Share this post


Link to post
Share on other sites

I have been starring at this for several days now and can't get it to work.

Could you please describe what doesn't work? Do you get any error message, does the script crash?

What version of AutoIt and Office do you use?


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

This gets you the maximum row used:

$iMaxRow = $oExcel.ActiveSheet.UsedRange.Rows.Count

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

I see that now. .

Could you please describe what doesn't work? Do you get any error message, does the script crash? What version of AutoIt and Office do you use?

I am using Win7 x64 and office 2010. AutoIt version is 3.3.6.1.

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

Do you run the 32 bit version of Office?


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

The infinite loop is this: You dont have exitloop from there.

I see that now. Not sure how to do an EOF on the file. Should I read the file into an array first and would that be better?

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

Do you run the 32 bit version of Office?

64 bit version of Office.

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

If your script crashes or _Excel* functions return strange results test our script with a 32 bit version of Office and make sure that the script is compiled/executed for 32 bit.

There are some threads on this forum which state that even MS doesn't recommend to use the 64 bit version of Office at this time.


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

If your script crashes or _Excel* functions return strange results test our script with a 32 bit version of Office and make sure that the script is compiled/executed for 32 bit.

There are some threads on this forum which state that even MS doesn't recommend to use the 64 bit version of Office at this time.

I will keep this in mind as we get this program finished. I currently don't have a 32 bit version of Office running, but do have access one.

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

Did you check the "while" loop you have ?

It is infinite loop - it never ends.

Share this post


Link to post
Share on other sites

Did you check the "while" loop you have ?

It is infinite loop - it never ends.

The While loop has an exitloop right after the FileReadLine, so if @error = -1 (which is the EOF indicator for FileReadLine) it exits the While loop.

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

Better use:

If @error Then ExitLoop

In case the file is renamed or cant be accessed it will give @error=1 so the loop never exits


Teamspeak 3 User Viewer - Quick and functional TS3 Query script, which shows online users.Cached Screenshot Deleter - Deletes older Fraps Screenshots if they exceed a specified limit.Unresolved Topics:Intercept and modify dragdrop text behaviour in scite

Share this post


Link to post
Share on other sites

I think the real reason is because the fileopen command is commented out, and you're reading the same line of the file every time through the While loop because you're using FileReadLine with a filename and not a file handle.


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

I think the real reason is because the fileopen command is commented out, and you're reading the same line of the file every time through the While loop because you're using FileReadLine with a filename and not a file handle.

I thought that using FileOpenDialog replaced the FileOpen? Also, none of the examples in the help show that I need to use a FileOpen.

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

FIleOpenDialog only opens the File Open dialog, not the file, you need to use FileOpen in read mode to read the file because the only thing that FileOpenDialog returns is the file name. All the examples for FileReadLine are using a FileOpen command before it.


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

This gets you the maximum row used:

$iMaxRow = $oExcel.ActiveSheet.UsedRange.Rows.Count

Sorry, I missed this one. Where is this kind of usage in the help files? How did you know to use this? I must be missing some help files.

RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!!

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

Sorry, I missed this one. Where is this kind of usage in the help files? How did you know to use this? I must be missing some help files.

Check the help file inside of Excel for that one. Specifically under the Alt+F11 script entry. The Obj() programming feature closely follows the VBS syntax available inside Excel. Activate the developer tab, hit Alt+F11 and open the object browser to get a feel of the syntax you can use. Edited by Blue_Drache

Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache

Share this post


Link to post
Share on other sites

Is there a reason why you don't just open the txt files with Excel? If not, I suggest doing so to simplify your code. You can specify which row to start appending the data from your text file in the Excel file with a delimiter of your choice. Afterwards, you can add your row descriptions with a column insert.

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  
Followers 0