1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen

Textdateien automatisch verändern

Dieses Thema im Forum "AppleScript" wurde erstellt von atomfried, 16.05.07.

  1. atomfried

    atomfried Leipziger Reinette

    Dabei seit:
    02.04.05
    Beiträge:
    1.781
    hallo!
    nachdem ich nun weiß wie ich meine Kontoauszüge (im CSV Format) in mein Buchhaltungsprogramm importieren kann, stellt sich eine weitere Frage.
    Das Programm importiert nur Dateien bei denen ein Buchungskonto angegeben ist. D.h. ich kann zwar alles importieren, muss aber von Hand bei jeder Transaktion das Konto eingeben. Kann ich mit Automator oder AppleScript diese CSV Datei verändern?

    z.B. soll immer wenn an einer bestimmten Stelle das Wort "Geldautomat" steht automatisch am Ende der Zeile das Konto "2100" eingefügt werden und z.B. bei "Telekom" das Konto "6800" usw.

    Das wäre super wenn das funktionieren würde.
    Danke!
     
  2. Hobbes_

    Hobbes_ Gast

    Hallo atomfried,

    Die neue Version dieses Programms entspricht den mit Dir besprochenen Spezifikationen und sollte Deinen Zwecken dienen. Wie gesagt, ist es mein erstes Programm in AppleScript und hat deshalb sicher verbesserungswürdige Stellen - doch funktioniert es :)

    Einstellungen: Alle persönlichen Settings können komfortabel im ersten Teil eingegeben werden.
    • Je nach Text in einer der Spalten (entweder Spalte 4 oder 6 Deiner Tabelle) wird eine bestimmte Kontonummer gewählt (siehe account_description_column).
    • Die Definitionen, welcher Text (im Moment case sensitive) mit welcher Nummer assoziiert wird, ist in der Liste account_nr_list definiert. Name und Nummer werden durch $$ voneinander getrennt. Die Liste kann beliebig erweitert werden. Wenn ein Begriff nicht definiert ist, dann wird die Standardnummer "-9999" zurückgegeben. So erkennst Du, was noch fehlt. Auch diese Nummer kann angepasst werden.
    • Je nach Zahlenwert (+ oder -; Spalte mit value_column im Moment auf Deine Beispieldatei passend gesetzt) wird diese Nummer entweder in die neue Lastschrift oder Gutschrift Spalte gesetzt sowie das Standardkonto entsprechend ergänzt.
    Ich habe für dieses public posting nur Daten eingesetzt, die Du bereits in diesem Forum gepostet hast. Die übrigen Einstellungen kannst Du sicher einfach machen :)

    Funktionsweise: Das Programm wird gestartet. Dann fragt es Dich als erstes, welche .csv-Datei bearbeitet werden soll. Die Ausgabe wird in eine andere Datei auf dem Desktop geschrieben. Ich denke, dass ein Standardname auch für den folgenden Import praktisch ist. Auch passiert so der Original-Datei sicher nichts. Du kannst den Namen dieser Datei in den Settings oben eingeben. Das ist eigentlich schon alles :)

    Anbei nun der Code:
    Code:
    -- ----------------------------------------------------------------------------------------
    -- replace/add strings in text v0.4 (first: 16-05-2007; last: 17-05-2007)
    -- ----------------------------------------------------------------------------------------
    -- a simple *patchwork* applescript to replace/add strings in a specific .csv file
    -- by psc using many sources in the internet (referenced in the source)
    -- This is my *very first* applescript script!
    -- Therefore it is really not my best program I  have ever written,
    -- but it is increasingly getting better and the patchwork character is fading out :-)
    --
    -- This script is Freeware & Open Source - please drop a message if it helps you :-) 
    -- ----------------------------------------------------------------------------------------
    
    -- user settings ------------------------------------------------------------------------
    
    set output_file_name to "dummy.csv"
    set csv_delimiter to "\";\"" -- this is a shortcut but good enough for the desired purpose
    
    set title_row_exists to true -- set this to false if there is no title/header row
    set title_new_columns_name to ";\"Gutschrift\";\"Lastschrift\""
    
    set account_description_column to 6 -- this is used in account_nr_list
    set value_column to 9 -- this is used to select which account nr is debitor or creditor
    
    set main_account_nr to "1800"
    set error_account_nr to "-9999" -- dummy error flag value as preset if the settings are not complete
    set account_nr_list_delimiter to "$$"
    -- change list: search and add strings are concenated together by delimiter $$
    -- put all your desired account names and numbers in this list (attention: case sensitive)
    set account_nr_list to {"Geldautomat$$2100", "Telekom$$6800"}
    
    -- end of user settings - do not change anything below this line ---------------
    
    tell application "Finder"
    	-- select input file and read it
    	set my_lines to paragraphs of (read (choose file with prompt "CSV-Datei auswählen (output geht an " & output_file_name & " auf dem Desktop)"))
    end tell
    
    -- open default output file (see http://www.apple.com/applescript/guidebook/sbrt/pgs/sbrt.11.htm)
    set target_file to (((path to desktop folder) as text) & output_file_name)
    try
    	set open_target_file to (open for access file target_file with write permission)
    	set eof of open_target_file to 0
    	set first_line to true
    	
    	-- do the stuff (rather extended http://forums.macrumors.com/showthread.php?t=124419)	
    	repeat with next_line in my_lines
    		if length of next_line is greater than 0 then
    			if (title_row_exists and first_line) then
    				-- write header information
    				write next_line & title_new_columns_name & return to open_target_file
    				set first_line to false
    			else
    				-- do the changes
    				set next_line to do_changes(next_line)
    				-- write it in the new file			
    				write next_line & return to open_target_file
    			end if
    		end if
    	end repeat
    	
    	-- close output file
    	close access open_target_file
    on error
    	display dialog "Error" with icon caution
    	try
    		close access file target_file
    	end try
    end try
    
    -- well, here comes the working part. Optimizations will go in here best!
    on do_changes(my_line)
    	global account_nr_list
    	global csv_delimiter
    	global account_description_column
    	global value_column
    	global main_account_nr
    	global error_account_nr
    	global account_nr_list_delimiter
    	
    	set my text item delimiters to csv_delimiter
    	set itemized_line to every text item of my_line
    	
    	set my text item delimiters to account_nr_list_delimiter
    	set second_account_nr to error_account_nr
    	
    	repeat with this_set in account_nr_list
    		set this_list to every text item of this_set
    		if first item of this_list is equal to item account_description_column of itemized_line then
    			set second_account_nr to last item of this_list
    		end if
    	end repeat
    	
    	set my text item delimiters to ""
    	if item value_column of itemized_line is less than 0 then
    		return my_line & ";\"" & second_account_nr & "\";\"" & main_account_nr & "\""
    	else
    		return my_line & ";\"" & main_account_nr & "\";\"" & second_account_nr & "\""
    	end if
    	return my_line
    end do_changes
    
    -- ----------------------------------------------------------------------------------------
    -- old stuff, this was used during development of this script
    -- ----------------------------------------------------------------------------------------
    
    -- this sub could be used to just replace a certain part of the string by another
    -- by the way: this  alogrithm provided by apple is very cool!
    -- this sub is no longer used, I keep it here just for teaching purposes :-)
    on replace_chars(this_text, search_string, replacement_string)
    	-- modified from http://www.apple.com/applescript/guidebook/sbrt/pgs/sbrt.07.htm
    	set my text item delimiters to search_string
    	set item_list to every text item of this_text
    	set my text item delimiters to replacement_string
    	set this_text to item_list as string
    	set my text item delimiters to ""
    	return this_text
    end replace_chars
    
    -- modified sub to add "the replacement" at the end of the line
    -- for simplicity, this algorithm bases on the algorithm seen above in replace_chars()
    -- this sub is no longer used, I keep it here just for teaching purposes :-)
    on add_chars(this_text, search_string, add_string, add_delimiter)
    	set my text item delimiters to search_string
    	set item_list to every text item of this_text
    	set my text item delimiters to ""
    	if (count of item_list) is greater than 1 then
    		return this_text & add_delimiter & add_string
    	else
    		return this_text
    	end if
    end add_chars
    
    Ich weiss, dass man diese Fragestellung evtl sogar besser in Perl, in einem Shell-Script oder in Excel lösen kann. Doch interessierte mich eben mal AppleScript...

    Ich bin froh, wenn mir allfällige Bugs mitgeteilt werden (am einfachsten per PM), so dass ich sie noch ausmerzen kann und das Programm besser wird...


    Anmerkung: Dies ist mein 500. Post in diesem Forum. Da dachte ich mir, wieso nicht mal wieder etwas spezielles? Eben zum beispiel mein allererstes AppleScript? :cool:
     
  3. drlecter

    drlecter Wöbers Rambur

    Dabei seit:
    04.11.06
    Beiträge:
    6.442
    In Perl währe das recht einfach zu handhaben. AppleScript war aber gewünscht.
     
  4. Hobbes_

    Hobbes_ Gast

    Tausender-Trennzeichen (.) entfernen...

    Anbei das neue Programm wie besprochen gepostet, da zu lange für PM. Die neue Version entfernt das Tausendertrennzeichen im Betrag (.). Anpassung der individuellen Parameter und Testserie vor produktivem Einsatz wie besprochen.

    Gruss
    psc

    Code:
    -- ----------------------------------------------------------------------------------------
    -- replace/add strings in text v0.5 (first: 16-05-2007; last: 28-05-2007)
    -- ----------------------------------------------------------------------------------------
    -- a simple *patchwork* applescript to replace/add strings in a specific .csv file
    -- by psc using many sources in the internet (referenced in the source)
    -- This is my *very first* applescript script!
    -- Therefore it is really not my best program I  have ever written,
    -- but it is increasingly getting better and the patchwork character is fading out :-)
    --
    -- v0.4: first public beta
    -- edit v0.5: added functionality to delete . as mark for thousands
    --
    -- This script is Freeware & Open Source - please drop a message if it helps you :-) 
    -- ----------------------------------------------------------------------------------------
    
    -- user settings ------------------------------------------------------------------------
    
    set output_file_name to "dummy.csv"
    set csv_delimiter to "\";\"" -- this is a shortcut but good enough for the desired purpose
    
    set title_row_exists to true -- set this to false if there is no title/header row
    set title_new_columns_name to ";\"Gutschrift\";\"Lastschrift\""
    
    set account_description_column to 6 -- this is used in account_nr_list
    set value_column to 9 -- this is used to select which account nr is debitor or creditor
    
    set thousand_sign to "."
    
    set main_account_nr to "1800"
    set error_account_nr to "-9999" -- dummy error flag value as preset if the settings are not complete
    set account_nr_list_delimiter to "$$"
    -- change list: search and add strings are concenated together by delimiter $$
    -- put all your desired account names and numbers in this list (attention: case sensitive)
    set account_nr_list to {"Geldautomat$$2100", "Telekom$$6800"}
    
    -- end of user settings - do not change anything below this line ---------------
    
    tell application "Finder"
    	-- select input file and read it
    	set my_lines to paragraphs of (read (choose file with prompt "CSV-Datei auswählen (output geht an " & output_file_name & " auf dem Desktop)"))
    end tell
    
    -- open default output file (see http://www.apple.com/applescript/guidebook/sbrt/pgs/sbrt.11.htm)
    set target_file to (((path to desktop folder) as text) & output_file_name)
    try
    	set open_target_file to (open for access file target_file with write permission)
    	set eof of open_target_file to 0
    	set first_line to true
    	
    	-- do the stuff (rather extended http://forums.macrumors.com/showthread.php?t=124419)	
    	repeat with next_line in my_lines
    		if length of next_line is greater than 0 then
    			if (title_row_exists and first_line) then
    				-- write header information
    				write next_line & title_new_columns_name & return to open_target_file
    				set first_line to false
    			else
    				-- do the changes
    				set next_line to do_changes(next_line)
    				-- write it in the new file			
    				write next_line & return to open_target_file
    			end if
    		end if
    	end repeat
    	
    	-- close output file
    	close access open_target_file
    on error
    	display dialog "Error" with icon caution
    	try
    		close access file target_file
    	end try
    end try
    
    -- well, here comes the working part. Optimizations will go in here best!
    on do_changes(my_line)
    	global account_nr_list
    	global csv_delimiter
    	global account_description_column
    	global value_column
    	global main_account_nr
    	global error_account_nr
    	global account_nr_list_delimiter
    	global thousand_sign
    	
    	set my text item delimiters to csv_delimiter
    	set itemized_line to every text item of my_line
    	
    	set my text item delimiters to account_nr_list_delimiter
    	set second_account_nr to error_account_nr
    	
    	repeat with this_set in account_nr_list
    		set this_list to every text item of this_set
    		if first item of this_list is equal to item account_description_column of itemized_line then
    			set second_account_nr to last item of this_list
    		end if
    	end repeat
    	
    	-- remove sign of thousands
    	set my text item delimiters to thousand_sign
    	set itemized_value to every text item of item value_column of itemized_line
    	set my text item delimiters to ""
    	set item value_column of itemized_line to itemized_value as string
    	set my text item delimiters to csv_delimiter
    	set my_line to itemized_line as string
    	
    	set my text item delimiters to ""
    	if item value_column of itemized_line is less than 0 then
    		return my_line & ";\"" & second_account_nr & "\";\"" & main_account_nr & "\""
    	else
    		return my_line & ";\"" & main_account_nr & "\";\"" & second_account_nr & "\""
    	end if
    	return my_line
    end do_changes
    
    -- ----------------------------------------------------------------------------------------
    -- old stuff, this was used during development of this script
    -- ----------------------------------------------------------------------------------------
    
    -- this sub could be used to just replace a certain part of the string by another
    -- by the way: this  alogrithm provided by apple is very cool!
    -- this sub is no longer used, I keep it here just for teaching purposes :-)
    on replace_chars(this_text, search_string, replacement_string)
    	-- modified from http://www.apple.com/applescript/guidebook/sbrt/pgs/sbrt.07.htm
    	set my text item delimiters to search_string
    	set item_list to every text item of this_text
    	set my text item delimiters to replacement_string
    	set this_text to item_list as string
    	set my text item delimiters to ""
    	return this_text
    end replace_chars
    
    -- modified sub to add "the replacement" at the end of the line
    -- for simplicity, this algorithm bases on the algorithm seen above in replace_chars()
    -- this sub is no longer used, I keep it here just for teaching purposes :-)
    on add_chars(this_text, search_string, add_string, add_delimiter)
    	set my text item delimiters to search_string
    	set item_list to every text item of this_text
    	set my text item delimiters to ""
    	if (count of item_list) is greater than 1 then
    		return this_text & add_delimiter & add_string
    	else
    		return this_text
    	end if
    end add_chars
    
     
  5. atomfried

    atomfried Leipziger Reinette

    Dabei seit:
    02.04.05
    Beiträge:
    1.781
    super! vielen dank. ich test es sofort.
     

Diese Seite empfehlen