Textdateien automatisch verändern

[atomfried]

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!

 

[Hobbes_]

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:

-- ----------------------------------------------------------------------------------------
-- 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?

 

[drlecter]

In Perl währe das recht einfach zu handhaben. AppleScript war aber gewünscht.

 

[Hobbes_]

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

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

 

[atomfried]

super! vielen dank. ich test es sofort.