Jump to content
Sign in to follow this  
Haugaard

Searh doc for 7 digit numbers?

Recommended Posts

I need to searh a doc fiel for seven digit numbers and copy all of them to a excel sheet.

I have no real ide on how to do this task.

The lines look like this:

374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000-

And only 7696917 is needed

Share this post


Link to post
Share on other sites

I need to searh a doc fiel for seven digit numbers and copy all of them to a excel sheet.

I have no real ide on how to do this task.

The lines look like this:

374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000-

And only 7696917 is needed

Like this with StringRegExp:

#include <array.au3>

$sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _
        "374 3130 1234567 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _
        "374 3130 0987654 B+C DS RYGNINGAFSLUTNING START 1,000- "
$avFinds = StringRegExp($sString, "\d{7}", 3)

If @error Then
    MsgBox(16, "Error", "@error = " & @error)
Else
    _ArrayDisplay($avFinds, "Results")
EndIf

I modifies your input string to show getting multiple results.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Like this with StringRegExp:

#include <array.au3>

$sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _
        "374 3130 1234567 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _
        "374 3130 0987654 B+C DS RYGNINGAFSLUTNING START 1,000- "
$avFinds = StringRegExp($sString, "\d{7}", 3)

If @error Then
    MsgBox(16, "Error", "@error = " & @error)
Else
    _ArrayDisplay($avFinds, "Results")
EndIf

I modifies your input string to show getting multiple results.

:)

That was i wanted but cant i use a file instead of writeing it into the script?

couse its 135 pages in word i have to edit.

And when its C/P to a excel sheet it allso give me the [0]7696917

So it would be better if it could just write it directly to a excel sheet

Edited by Haugaard

Share this post


Link to post
Share on other sites

That was i wanted but cant i use a file instead of writeing it into the script?

couse its 135 pages in word i have to edit.

And when its C/P to a excel sheet it allso give me the [0]7696917

So it would be better if it could just write it directly to a excel sheet

$sString is only there for demo purposes. Of course you could just FileRead() the data. You don't even have to save it to a variable first, since AutoIt supports nested functions:

#include <ExcelCOM_UDF.au3>

$sTxtFile = "C:\Temp\Test.txt"
$sExcelFile = "C:\Temp\Test.xls"

; Create txt file for demo
$sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _
        "374 3130 1234567 B+C DS RYGNINGAFSLUTNING START 1,000- " & @CRLF & _
        "374 3130 0987654 B+C DS RYGNINGAFSLUTNING START 1,000- "
$hFile = FileOpen($sTxtFile, 2)
FileWrite($hFile, $sString)
FileClose($hFile)

; Get all 7-digit numbers from file
$avFinds = StringRegExp(FileRead($sTxtFile), "\d{7}", 3)

; Create Excel spreadsheet and save data
$oXL = _ExcelBookNew()
_ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0)oÝ÷ ØêÞ½éàyé"z¶®¶­sc²7&VFRW6VÂ7&VG6VWBæBF7ÆFF¢b33c¶õÂÒôW6VÄ&öö´æWr¥ôW6VÅw&FT'&b33c¶õÂÂÂÂ7G&æu&VtWfÆU&VBb33c·5GDfÆRÂgV÷C²b3#¶G³wÒgV÷C²Â2ÂÂ

What you give up in doing that is the opportunity for error checking before the values are passed to the next function up in the nested functions.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

@PsaltyDS - Using FileRead() on a MS Word Document isn't going to give the desired results.

Try this...

#include <Word.au3>
#include <Array.au3>
#include <ExcelCOM_UDF.au3>

$sWordFile = "C:\Temp\Test.doc"
$sExcelFile = "C:\Temp\Test.xls"

; Open MS Word Document
$oWordApp = _WordCreate($sWordFile)
; Get reference to the document object
$oDoc = _WordDocGetCollection($oWordApp, 0)
; Store document text to a variable
$sText = $oDoc.Range.Text
; Close MS Word
_WordQuit($oWordApp)

; Get all 7-digit numbers from text
$avFinds = StringRegExp($sText, "\d{7}", 3)

; Create Excel spreadsheet and save data
$oXL = _ExcelBookNew()
_ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0)

Share this post


Link to post
Share on other sites

@PsaltyDS - Using FileRead() on a MS Word Document isn't going to give the desired results.

Good catch, I missed that the source was a Word doc. I tested the demo with the plain text file shown.

To quote the noted philosopher, Homer: "Doh!" :)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

@PsaltyDS - Using FileRead() on a MS Word Document isn't going to give the desired results.

Try this...

#include <Word.au3>
#include <Array.au3>
#include <ExcelCOM_UDF.au3>

$sWordFile = "C:\Temp\Test.doc"
$sExcelFile = "C:\Temp\Test.xls"

; Open MS Word Document
$oWordApp = _WordCreate($sWordFile)
; Get reference to the document object
$oDoc = _WordDocGetCollection($oWordApp, 0)
; Store document text to a variable
$sText = $oDoc.Range.Text
; Close MS Word
_WordQuit($oWordApp)

; Get all 7-digit numbers from text
$avFinds = StringRegExp($sText, "\d{7}", 3)

; Create Excel spreadsheet and save data
$oXL = _ExcelBookNew()
_ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0)

This works great but it makes 8 digit numbers down to 7 and post them in the excel sheet.

Share this post


Link to post
Share on other sites

This works great but it makes 8 digit numbers down to 7 and post them in the excel sheet.

That's my mistake, not big_daddy's. The RegExp just needs "\b" before and after the pattern so it only matches between word boundries:

#include <array.au3>

$sTxtFile = "C:\Temp\Test.txt"
$sExcelFile = "C:\Temp\Test.xls"

; Create txt file for demo
$sString = "374 3130 7696917 B+C DS RYGNINGAFSLUTNING 01234567 START 1,000- " & @CRLF & _
        "374 3130 1234567 B+C DS RYGNINGAFSLUTNING 012345678 START 1,000- " & @CRLF & _
        "374 3130 0987654 B+C DS RYGNINGAFSLUTNING 0123456789 START 1,000- "
$hFile = FileOpen($sTxtFile, 2)
FileWrite($hFile, $sString)
FileClose($hFile)

; Get all 7-digit numbers from file
$avFinds = StringRegExp(FileRead($sTxtFile), "\b\d{7}\b", 3)

_ArrayDisplay($avFinds, "Debug: $avFinds")

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Yearh that fixed it

Well heres all the code for the scrip incase someon else needs it or just for learning.

This made my 10 days workload in 3 hours, so im realy happy :)

Excel-2-AS400_711A.au2

#include <ExcelCOM_UDF.au3>; Include the function collection
$FopenMessage = "Browse for the input Excel file."
$Fopen = FileOpenDialog($FopenMessage, @WorkingDir, "Excel file (*.xls)", 1)

$oExcel = _ExcelBookOpen($Fopen); Open file

;$iColumn = InputBox("Data", "What column should I use data from?", "1","")
$icolumn = 1

For $Cell = 1 To 999991
    WinActivate("Microsoft Excel - x805.xls");Activate Excel
    $exdata = _ExcelReadCell($oExcel, $Cell, $icolumn);Read data
    If $ExData < 1  Then ExitLoop;Stop script if $data1 is = 9999999
    Sleep(1000)
    WinActivate("Session B - [24 x 80]");Activate AS400
    Send($ExData);Write the data from excel
    Sleep(500)
    Send("{ENTER}")
    Sleep(500)
    MouseClick("Left", 310, 61, 1);Mouseclick AS400 macro
    Sleep(500)
    Send("{ENTER}");Confirm use of macro
    Sleep(3500);Wait for the macro to run, big sleep
Next
Exit

AS400_pctxt-2-excel.au2

#include <Word.au3>
#include <Array.au3>
#include <ExcelCOM_UDF.au3>
$FopenMessage1 = "Browse for the Word example file."
$FopenMessage2 = "Browse for the Excel example file."
$sWordFile = FileOpenDialog($FopenMessage1, @WorkingDir, "Word example file (*.doc)", 1)
$sExcelFile = FileOpenDialog($FopenMessage2, @WorkingDir, "Excel example file (*.xls)", 1)

; Open MS Word Document
$oWordApp = _WordCreate($sWordFile)
; Get reference to the document object
$oDoc = _WordDocGetCollection($oWordApp, 0)
; Store document text to a variable
$sText = $oDoc.Range.Text
; Close MS Word
_WordQuit($oWordApp)

; Get all 7-digit numbers from text
$avFinds = StringRegExp($sText, "\b\d{7}\b", 3)

; Create Excel spreadsheet and save data
$oXL = _ExcelBookNew()
_ExcelWriteArray($oXL, 1, 1, $avFinds, 1, 0)

And thanks to all you guy´s helping me out!

Edited by Haugaard

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  

×
×
  • Create New...