Code: Select all
/*
This script will read and update a csv file. It should be easy to modify to suit your needs
Currently it reads a csv file with 8 fields - to use it create a csv file with the information below
or a blank one as it can also add records.
Sample CSV File:
Copy the text below and paste into notepad and save the file with a csv extension
First_Name,Last_Name,SR,Street,City,State,Zip,
Donald,Smith,,5036 Oakland Rd,Auburndale,FL,33823,3709
Dale,Evans,,5073 Mandy St,Auburndale,FL,33823,2219
Roy,Rogers,,12408 Burlington Ct,Auburndale,FL,33823,2361
Jamie,Farr,,27710 Ariana Blvd,Auburndale,FL,33823,3102
Uncle,Feaster,,1205 Deen Blvd,Auburndale,FL,33823,2553
*/
//Change the two lines below to match the saved csv file.
Let>csvfile=test.csv
Let>csvpath=\\X-series\keith\Files
Dialog>Dialog1
Caption=Main Menu
Width=307
Height=190
Top=CENTER
Left=CENTER
Max=0
Min=0
Close=1
Resize=0
Edit=ifn,116,52,121,
Label=First Name,62,56,true
Edit=iln,116,84,121,
Label=Last Name,61,88,true
Button=Search,38,122,75,25,1
Button=Exit,186,122,75,25,2
Button=Add Record,112,122,75,25,3
Label=Manage CSV Address List,88,24,true
EndDialog>Dialog1
Dialog>Dialog2
Caption=Add Record
Width=502
Height=220
Top=CENTER
Left=CENTER
Max=1
Min=1
Close=1
Resize=1
Edit=msEdit1,103,44,121,
Edit=msEdit2,291,44,121,
Edit=msEdit3,453,44,28,
Edit=msEdit4,103,72,311,
Edit=msEdit5,103,105,133,
Edit=msEdit6,270,105,24,
Edit=msEdit7,338,105,47,
Edit=msEdit8,103,136,121,
Button=Add,267,149,75,25,1
Button=Exit,341,149,75,25,2
Label=First Name,48,48,true
Label=Last Name,232,48,true
Label=Suffix,424,48,true
Label=Street,70,80,true
Label=City,81,109,true
Label=State,240,109,true
Label=Zip,320,109,true
Label=Undefined,49,140,true
Label=Add a New Record,201,16,true
EndDialog>Dialog2
Dialog>Dialog4
Caption=Search Results
Width=502
Height=247
Top=CENTER
Left=CENTER
Max=1
Min=1
Close=1
Resize=1
Edit=msEdit1,103,44,121,%Firstnamer%
Edit=msEdit2,291,44,121,%LastNamer%
Edit=msEdit3,453,44,28,%Suffixr%
Edit=msEdit4,103,72,311,%Streetr%
Edit=msEdit5,103,105,133,%Cityr%
Edit=msEdit6,270,105,24,%Stater%
Edit=msEdit7,338,105,47,%Zipr%
Edit=msEdit8,103,136,121,%Undefinedr%
Button=Next Match,267,149,75,25,1
Button=Exit,341,173,75,25,2
Button=Update,267,173,75,25,3
Button=Delete,341,149,75,25,4
Label=First Name,48,48,true
Label=Last Name,232,48,true
Label=Suffix,424,48,true
Label=Street,70,80,true
Label=City,81,109,true
Label=State,240,109,true
Label=Zip,320,109,true
Label=Undefined,49,140,true
EndDialog>Dialog4
Let>comma=,
Show>Dialog1
Label>displaysearchloop
GetDialogAction>Dialog1,result
If>result=1,Searchsub
If>result=2,EOF
If>result=3,Addrecord
Wait>.01
Goto>displaysearchloop
SRT>Addrecord
CloseDialog>Dialog1
ResetDialogAction>Dialog1
Show>Dialog2
Label>Addrecordloop
GetDialogAction>Dialog2,2result
If>2result=1,addfile
If>2result=2,Main
Goto>Addrecordloop
SRT>addfile
CloseDialog>Dialog2
Let>newrecord=%Dialog2.msEdit1%%comma%%Dialog2.msEdit2%%comma%%Dialog2.msEdit3%%comma%%Dialog2.msEdit4%%comma%%Dialog2.msEdit5%%comma%%Dialog2.msEdit6%%comma%%Dialog2.msEdit7%%comma%%Dialog2.msEdit8%%CRLF%
WriteLn>%csvpath%\%csvfile%,result,%newrecord%
If>%result%=0
MDL>The Record was added
Else
MDL>Failed to add record - will now Exit
Goto>EOF
Endif
Let>Dialog2.msEdit1=
Let>Dialog2.msEdit2=
Let>Dialog2.msEdit3=
Let>Dialog2.msEdit4=
Let>Dialog2.msEdit5=
Let>Dialog2.msEdit6=
Let>Dialog2.msEdit7=
Let>Dialog2.msEdit8=
ResetDialogAction>Dialog2
Show>Dialog2
END>addfile
Label>Main
CloseDialog>Dialog2
Let>Dialog2.msEdit1=
Let>Dialog2.msEdit2=
Let>Dialog2.msEdit3=
Let>Dialog2.msEdit4=
Let>Dialog2.msEdit5=
Let>Dialog2.msEdit6=
Let>Dialog2.msEdit7=
Let>Dialog2.msEdit8=
ResetDialogAction>Dialog2
Show>Dialog1
END>Addrecord
SRT>Searchsub
CloseDialog>Dialog1
ResetDialogAction>Dialog1
Let>Fname=%Dialog1.ifn%
Let>LName=%Dialog1.iln%
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%csvpath%;Extended Properties='text;HDR=NO;FMT=Delimited;Option=1'
DBConnect>ConStr,dbH
Let>SQL=select * from %csvfile%
DBQuery>dbH,SQL,fields,num_recs,num_fields
Let>rec=0
Repeat>rec
Let>rec=rec+1
Let>Firstnamer=fields_%rec%_1
Let>Lastnamer=fields_%rec%_2
Let>Suffixr=fields_%rec%_3
Let>Streetr=fields_%rec%_4
Let>Cityr=fields_%rec%_5
Let>Stater=fields_%rec%_6
Let>Zipr=fields_%rec%_7
Let>Undefinedr=fields_%rec%_8
//check to see if there is something in the first name to check for
Length>%Fname%,fnl
If>%fnl%>1
Position>%FName%,fields_%rec%_1,1,pm
If>%pm%>0
// If>fields_%rec%_1=%FName%
Goto>lnamematch
Else
Goto>checknextrecord
Endif
Endif
Label>lnamematch
Length>%LName%,fnl
If>%fnl%>1
Position>%LName%,fields_%rec%_2,1,pm
If>%pm%>0
// If>fields_%rec%_1=%LName%
Goto>Recordfound
Else
Goto>checknextrecord
Endif
Endif
Label>Recordfound
If>testc=2
Let>Dialog4.msEdit1=%Firstnamer%
Let>Dialog4.msEdit2=%Lastnamer%
ResetDialogAction>Dialog4
Endif
Let>Match=YES
Show>Dialog4
Label>displayresultloop
GetDialogAction>Dialog4,4result
If>4result=1,Searchagain
If>4result=2,EOF
If>4result=3,Update
If>4result=4,Deleterecord
Wait>.01
Goto>displayresultloop
Label>Deleterecord
//Get current line in csv and delete it
Let>replacestring=%Firstnamer%%comma%%Lastnamer%%comma%%Suffixr%%comma%%Streetr%%comma%%Cityr%%comma%%Stater%%comma%%Zipr%%comma%%Undefinedr%%CRLF%
ReadFile>%csvpath%\%csvfile%,temp
DeleteFile>%csvpath%\%csvfile%
StringReplace>%temp%,%replacestring%,,temp
WriteLn>%csvpath%\%csvfile%,result,%temp%
Goto>Searchagain
Label>Update
//Get current line in csv and replace it with the text in the dialog
Let>replacestring=%Firstnamer%%comma%%Lastnamer%%comma%%Suffixr%%comma%%Streetr%%comma%%Cityr%%comma%%Stater%%comma%%Zipr%%comma%%Undefinedr%%CRLF%
Let>newString=%Dialog4.msEdit1%%comma%%Dialog4.msEdit2%%comma%%Dialog4.msEdit3%%comma%%Dialog4.msEdit4%%comma%%Dialog4.msEdit5%%comma%%Dialog4.msEdit6%%comma%%Dialog4.msEdit7%%comma%%Dialog4.msEdit8%%CRLF%
ReadFile>%csvpath%\%csvfile%,temp
DeleteFile>%csvpath%\%csvfile%
StringReplace>%temp%,%replacestring%,%newString%,temp
WriteLn>%csvpath%\%csvfile%,result,%temp%
Goto>Searchagain
Label>Searchagain
CloseDialog>Dialog4
ResetDialogAction>Dialog4
Let>testc=2
Label>lnamematch
Label>checknextrecord
Until>rec=num_recs
If>%Match%=YES,havematch
MDL>No Matches were found
Label>havematch
Show>Dialog1
END>Searchsub
Label>EOF
DBClose>dbH
Exit>0
