AutoHotkey Homepage AutoHotkey Community
Let's help each other out
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Little Prog to re-arrange TXT file to import in Access

 
Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions
View previous topic :: View next topic  
Author Message
Fabiolus



Joined: 20 Aug 2006
Posts: 29
Location: Ottawa

PostPosted: Mon Aug 21, 2006 5:40 pm    Post subject: Little Prog to re-arrange TXT file to import in Access Reply with quote

I manage database and sometimes customer provide their shipping information in a TXT file and the data is not arrange to import in Access. All data is in 1 line.

Made this little macro to re-align all the data to be able to import in Access or Excel. You have to specify what to search for, in this exemple I search for "5,h,"

Here is an exemple of file that is given to me You will see that once the file is completed that all fields are align, it beats doing it manually heheheh

5,H,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,1,23232323000096235,967,3665,0,0,0,0,0,44348 Rue Bdfnnière, App. 301, ,Montréal,PQ,H1X2M4,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,haksdana_jesde@yasdo.com,0,0,0, ASTRID BICAsdPAKA SHEMA,5sdsdsds983,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,2,23232323000096242,967,3855,0,0,0,0,0,10395 Rancourt,,montréal,QC,H2B2P2,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,louise.quesdnsdlle@gsdorex.ca,0,0,0, MARsdLIPPE TÉsdAULT,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,3,23232323000096259,967,3940,0,0,0,0,0,449 Bourbonnais,,LaSalle,QC,H8R2Z2,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,e.gausdn@videsddn.ca,0,0,0,STÉPHANIE GAUDRON,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,4,23232323000096266,967,3005,0,0,,10395 Rancourt,,montréal,QC,H2B2P2,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,lousde.qudsdille@gesconsdrex.ca,0,0,0, Lsd-MAUDE TÉTREsdLT,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,5,23232323000096273,967,2580,0,0,0,0,0,787 13e avenue,,Lachine,QC,H8S3K4,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,pepito1@videosdn.ca,0,0,0,VANsdSSA PsdIN,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,6,23232323000096280,967,3290,0,0,0,0,0,33 Mérineau,,Kirkland,QC,H9J3V8,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,d.dusdois@ssss.gsdsd.ca,0,0,0,GsdEVIsdE BOsdARD,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,7,23232323000096297,967,5665,0,0,0,0,0,3625 dfdfdfdfdfdf,,Trois-Rivières,QC,G9B2B6,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.csdm,,,0,0,0,Jsd-PHILsdPEsdERS,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,8,23232323000096303,967,2000,0,0,0,0,0,231 place pedfdfn,,Yamacdfdfhe,QC,G0X3L0,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,cmsdin_19@symsdico.ca,0,0,0,ANTsdNE BOURAsdA,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,9,23232323000096310,967,2615,0,0,0,0,0,7925 Boul Gouin ,,Montreal,QC,H1E2A2,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,,0,0,0,sdsd-Ève Duchesne,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,10,23232323000096327,967,760,0,0,0,0,0,11775 Boul. Bécancour,,Bécancour,QC,G9H2K1,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,,0,0,0,AUDREY HÉLIE,,,,,,,0,5,h,5,10912,I086,F2423423434208,Matériel Scolaire,06082006,23232323,23232323,0,0,0,0,11,23232323000096334,967,1680,0,0,0,0,0,1291 Foch,,Verdun,QC,H4H2R4,CA,0.00,0.00,0,0.00,0,1,0,0,0,0.00,1,0,0,0,0,0,1,0,0,0,weeuf@gvwec.com,,chsdvinmc@hosdail.com,0,0,0,GENEVsdE CADIEUX,,,,,,,0,
_________________
There is always something new to learn.
_________________
http://autohotkey.net/~Fabiolus/
Back to top
View user's profile Send private message Visit poster's website
Fabiolus



Joined: 20 Aug 2006
Posts: 29
Location: Ottawa

PostPosted: Mon Aug 21, 2006 5:40 pm    Post subject: Here is the code Reply with quote

See post below for snippet
_________________
There is always something new to learn.
_________________
http://autohotkey.net/~Fabiolus/


Last edited by Fabiolus on Tue Aug 29, 2006 1:01 pm; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
PhiLho



Joined: 27 Dec 2005
Posts: 6836
Location: France (near Paris)

PostPosted: Tue Aug 22, 2006 7:54 am    Post subject: Reply with quote

It seems (mostly from a previous post in the Ask for Help section) that you are using Notepad to do your text processing.
This is very ineficient, you should use the built-in good string processing facilities of AutoHotkey.
_________________
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")
Back to top
View user's profile Send private message Visit poster's website
Fabiolus



Joined: 20 Aug 2006
Posts: 29
Location: Ottawa

PostPosted: Tue Aug 22, 2006 1:19 pm    Post subject: Re: PhiLho Reply with quote

Not sure about the built in string thing you mention, I will be looking into that for sure.

I just started using AHK and find it very interesting.

I ended up with this version to complete my task.

Feel free to add your version if you wish.

**************************************************

FileRead, MyFileContent, MyFile.txt
FileSelectFile, SelectedFile, 1, , Open a file, Text Documents (*.txt; *.cp1)
if ErrorLevel
ExitApp

Run, %SelectedFile%
Sleep, 1000

msgbox, 64, Fabiolus File Fixer Version 6, Press ESCAPE to cancel job in progress

InputBox, UserInput,, Enter what to search for.
if ErrorLevel
ExitApp
Send {F3}
GoSub, CheckIfFound
sleep, 10 ;add more sleep for slower computer
send %UserInput%
Send {Enter}
Send {Enter}
Send {Esc}
Send {Enter}
Send %UserInput%

Loop
{
Send {F3}
GoSub, CheckIfFound
sleep, 10 ;add more sleep for slower computer
Send {Enter}
Send %UserInput%


If GetKeyState( "Esc", "P" )
Break
}

;*************Subroutines**************
CheckIfFound:
IfWinActive, , Cannot find ; Will appear if search criteria is not found
{
Sleep 10
Send {Enter} ; Closes Find dialog box
MsgBox, 64, Fabiolus File Fixer Version 6, File Done !!!!`n`nNow simply save your file.
ExitApp
}
Return
_________________
There is always something new to learn.
_________________
http://autohotkey.net/~Fabiolus/
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions All times are GMT
Page 1 of 1

 
Jump to:  
You can post new topics in this forum
You can reply to topics in this forum


Powered by phpBB © 2001, 2005 phpBB Group