STATA-FORUM.DE

[domibrown]

Hallo,

ich sitze gerade an der Aufgabe mehrere Excel Dateien gleichzeitig über den in Stata 12 integrierten Befehl "import excel .." zu importieren. Für eine einzelne Datei ist es mir mit folgendem do file code gelungen:

version 12.1
set more off
clear
clear matrix
cap log close

global Output_Path = "Adresse"
global Input_Path = "Adresse"
global Converted_Path = "Adresse"
global Merged_Path ="Adresse"
global Inputfilename1 = "Name der Exceldatei"
global Outputfilename1 = "Name der zu speichernden Datei"
global Mergedfilename1 = "Name der verschmolzenen Datei"

/*--------------- Import ---------------*/

import excel using "${Input_Path}${Inputfilename1}", first
save "${Converted_Path} ${Outputfilename1}", replace
drop _all

/*--------------- Merge ---------------*/

use "${Converted_Path} ${Outputfilename1}.dta"

mer 1:1 VARIABLE using "${Converted_Path} ${Outputfilename2}.dta"

save "${Merged_Path} ${Merdgedfilename1}", replace

cap log close
exit

Meine Frage ist nun, ob es eine Möglichkeit gibt, alle Excel Dateien im Inputordner (das ist der wo die Excel Dateien liegen) gleichzeitig zu importieren und durch den merge befehle zusammenschmelzen zu lassen. Ist es darüber hinaus möglich den Code so zu schreiben, dass der Name der Excel Dateien keine Rolle mehr spielt? Das do file soll also einfach die erste bis zur letzten Datei im Ordner importieren und miteinander verschmelzen lassen.


Vielen Dank schonmal und liebe Grüße Domi

[daniel]

Für meinen Geschmack werden hier zu viele -globals- benutzt. Ich würde Dateinamen niemals als -global- definieren, aber bin auch nicht sicher, was genau Dein Ziel ist. Willst Du am Ende ein "richtiges" Programm (ado-file) haben, inkl. Eingabefehler- und diversere anderer checks etc., oder genügt es, einen code zu haben, der die beschriebene Aufgabe erfüllt?

Hier ist ein Entwurf

Code: Alles auswählen

loc allxls : dir `"<input path>"' file "*.xls"
loc firstfile 1
foreach f of loc allxls {
   import excel using `"<input path>/`f'"' ,first
   gettoken fn : f, p(".")
   sa `"<converted path>/`fn'"' ,replace
   if (`firstfile') {
      sa `"<merged path>/merged.dta"' ,replace
      loc firstfile 0
      continue
   }
   mer 1:1 <id vars> using `"<merged path>/merged.dta"' ///
   ,nogen
   sa `"<merged path>/merged.dta"' ,replace
}


Der code ist nicht gestest. Alle Elemente in <> müssen entsprechend ausgetauscht werden. Compound double quotes sind nicht zwingend nötig. Es werden nur Dateien mit der Endung .xls aus dem <input path> geladen.

Dieser Ansatz ist sehr fehleranfällig, u.a. weil die Ergebnisse der einzelenen -merge- Befehle nicht gecheckt werden. Zudem wird im <merged path> nur ein einziges file erstellt. Ich denke der code kann dennoch als Ausgangspunkt dienen.

[domibrown]

Also erstmal vielen vielen Dank

Leider bin ich in STATA nicht so fit, dass ich viele verschiedene Gestaltungsmöglichkeiten für das von mir genannte Problem kenne. Bsp: Ich bin mir nicht sicher was ein ADO-file ist

Zunächst hab ich deinen Code eingebaut und es funktioniert soweit, dass er alle .xls Dateien aus dem Verzeichnis in Stata importiert und diese dann in dem Output Path speichert. Dafür vielen Dank.

Meine Aufgabe ist folgende:

1. Aus einem Ordner mit sehr vielen Excel Dateien sollen zunächst sehr viele (in Stata importierte) Stata Dokumente gemacht werden.
2. Danach sollen alle Dokumente in eine große Datenbank zusammengeschmolzen werden. Dafür beinhaltet jede Excel Datei eine eindeutige Kundennummer als Identifikation.
3. die Schritte 1-2 sollen so fehlerresistent und automatisch wie es nur geht geladen werden. Am besten so, dass ein fachfremder diese Arbeit ohne viel nachzudenken ausführen kann.


Falls dir da eine Herangehensweise einfällt, die über deinen oben genannten Code hinausgeht, würde ich mich sehr freuen. (falls nicht - auch nicht schlimm )

LG domi

[daniel]

Ein ado-file ist im Prinzip ein do-file, das ein (oder mehr) Programm/e definiert. Viele Stata Befehle sind als ado-files implementiert (z.b. -recode-). Ein Fachfremder könnte also dein ado-file genau wie jeden anderen Stata Befehl aufrufen. Je nachdem wie Fachfremd derjenige ist, ist ein ado-file, mit zugehörigem help file sicher sinnvoll. Der aufwändige Part kommt dann ins Spiel, wenn

ein fachfremder diese Arbeit ohne viel nachzudenken ausführen

(meine Hervorhebung)

soll. Je weniger der user nachdenken muss, bzw. je weniger der user weiß, wie genau das Programm tut, was es tut, desto höher der Aufwand mögliche Eingabefehler zu antizipieren und abzufangen.

Mein code könnte die Basis für ein solches Progamm darstellen. Die Fehleranfälligkeit kann (minimal) verbessert werden, indem das Ergebnis des -mere-ens geprüft wird. Statt

Code: Alles auswählen

mer 1:1 <id vars> using `"<merged path>/merged.dta"' ///
,nogen


könnte man z.B.

Code: Alles auswählen

mer 1:1 <id vars> using `"<merged path>/merged.dta"' ///
,nogen assert(3)


schreiben. So wird sichergestellt, dass alle Fällle, die im bereits gespeicherten file sind, auch im neu anzuhängenden file vorhanden sind.

Ich würde das Problem allerdings, wie Du andeutets vielleicht in zwei Schritte unterteilen. Hier ist ein einfaches Programm für Schritt 1.

Code: Alles auswählen

*! version 1.0.0 06sep2012 Daniel Klein

pr impxlsfolder
   vers 12.1
   
   syntax using/ [, SAve(str) replace EXTension(str) clear * ]
   
   // set options
   if ("`clear'" == "") loc clear clear
   if (`"`save'"' == "") loc save `using'
   if (`"`extension'"' == "") loc extension .xls
   else if !(inlist(`"`extension'"', ".xls", ".xlsx")) {
      di as err "extension must be one of .xls or .xlsx"
      e 198
   }
   
   // get working directory and check directories
   loc pwd `c(pwd)'
   qui cd "`using'"
   qui cd "`pwd'"
   qui cd "`save'"
   qui cd "`pwd'"
   
   // get all files in target folder
   loc allf : dir "`using'" file "*`extension'"
   
   // import and save as .dta
   preserve
   foreach f of loc allf {
      import excel using "`using'/`f'" ,clear `options'
      gettoken fn : f ,p(".")
      sa "`save'/`fn'.dta" ,`replace'
   }
end


Den code in einen do-file Editor kopieren und unter c:/ado/impxlsfolder.ado oder c:/ado/plus/i/impxlsfolder.ado speichern. In Stata -discard- tippen. Der Befehl -impxlsfolder- kann nun verwendet werden. Die Syntax lautet

impxlsfolder using ["]filename["] [, save(path) replace extension(ext) import excel options ]

filename ist das Verzeichnis, in dem die Excel Dateien liegen.

Die Option ...

save(path) spezifiziert das Verzeichnis, in dem die .dta Dateien gespeichert werden. Fall nicht angegeben wird das Verzeichnis, in dem die Excel Datein liegen verwendet. Anführungszeichen sind nicht nötig.

replace erlaubt das Überschreiben bereits vorhandener .dta Dateien.

extension(ext) spezifiziert die Excell Dateierweiterung. ext kann entweder .xls (default) oder .xlsx sein.

import excel options sind alle Optionen, die bei import excel erlaubt sind.

Du würdest also tippen

Code: Alles auswählen

impxlsfolder using "<input path>" ,sa(<output path>) ,first


Es wäre sinnvoll ein help file anzulegen, dass die Syntax und Optionen erklärt, dazu habe ich aber gerade keine Zeit.

Für Schritt 2 schau mal unter

. findit mmerge

oder

. findit mergemany

nach. Vielleicht ist da was passendes dabei.

[domibrown]

Also erstmal vielen vielen Dank für die schnelle Hilfe. Ich werde mich heute den ganzen Tag mit deinem Code auseinandersetzen und hoffentlich alles zum Laufen bringen. Ich gebe auf jedenfall nochmal ein Feedback wie die Sache gelaufen ist. Solltest du mal Hilfe bei VWL brauchen, meld dich einfach bei mir

Liebe Grüße domi

[domibrown]

Hallo, also Teil 1 funktioniert, mit einer Ausnahme, wunderbar!!

Code: Alles auswählen

impxlsfolder using "<input path>" ,sa(<output path>) replace extension(.xls) first


Kann ich auch simultan die .xlsx Dateien importieren?

Code: Alles auswählen

impxlsfolder using "<input path>" ,sa(<output path>) replace extension(.xls) first replace extension(.xlsx)


funktioniert leider nicht auch die Verbindung in der Klammer durch & oder oder (|) funktioniert nicht ^.^

lg domi

[daniel]

Ich habe gestern Nacht eine leicht modifizierte Version des Programms, inkl. help file geschrieben, die als default beide files simultan einliest. Leider habe ich z.Zt. keinen Zugang dazu, weil das ado-file auf meinem Privatrechner liegt. Ich werde die neue Version und das help file später hier kopieren. Bis dahin ändere in Version 1.0.0 06sep2012 die Zeilen

Code: Alles auswählen

if (`"`extension'"' == "") loc extension .xls
else if !(inlist(`"`extension'"', ".xls", ".xlsx")) {
    di as err "extension must be one of .xls or .xlsx"
     e 198
}


in

Code: Alles auswählen

if (`"`extension'"' == "") loc extension .xls?
else if !(inlist(`"`extension'"', ".xls", ".xlsx", "xls?")) {
    di as err "extension must be one of .xls or .xlsx"
     e 198
}


Das ist keine ganz sauberer Lösung, weil Stata dann z.B. auch Dateien mit der Endung .xlsa (was auch immer das für dateien sein mögen) importieren würde. Bei dem Versuch würde es natürlich zu einem Fehler kommen. In Deinem Fall, wenn also ausschließlich .xls und .xlsx files vorhanden sind, macht das nichts aus.

Nach den Änderungen speicherst Du das file und tippst Du

Code: Alles auswählen

discard
impxlsfolder using "<input path>" ,sa(<output path>) replace first


[daniel]

Ist etwas später geworden, aber hier das Programm und das help file. Den Programmcode einfach in einen Texteditor (z.B. do-file editor) kopieren, und unter c:/ado/impxlsfolder.ado oder c:/ado/plus/i/impxlsfolder.ado speichern.

Das help file ebenfalls in einen Texteditor (z.B. do-file editor) kopieren und unter c:/ado/impxlsfolder.sthlp oder c:/ado/plus/i/impxlsfolder.sthlp speichern.

Beim Neustart von Stata (oder nach -discard- Befehl) kann der Befehl -impxlsfolder- verwendet werden. Mit

Code: Alles auswählen

help impxlsfolder


wird das help file aufgerufen.

Hier der code

Code: Alles auswählen

*! version 1.0.1 07sep2012 Daniel Klein

pr impxlsfolder
   vers 12.1
   
   syntax [anything(equalok)] using/ ///
   [, SAve(str) replace EXTension(str) ///
   clear DESCribe * ]
   
   // set options
   if (`"`extension'"' != "") ///
   & !(inlist(`"`extension'"', ".xls", ".xlsx")) {
      di as err "extension must be one of .xls or .xlsx"
      e 198
   }
   if (`"`save'"' == "") loc save `using'
   if ("`describe'" == "") loc clear clear
   
   // get working directory and check directories
   loc pwd `c(pwd)'
   qui {
      cd "`using'"
      cd "`pwd'"
      cd "`save'"
      cd "`pwd'"
   }
   
   // get all files in target folder
   if (`"`extension'"' != "") {
      gettoken dot ext : extension ,p(".")
      loc `ext' : dir "`using'" file "*.`ext'"
   }
   else {
      foreach ext in xls xlsx {
         loc `ext' : dir "`using'" file "*.`ext'"
      }
   }
   loc allf `"`xls' `xlsx'"'
   
   // import and save as .dta
   preserve
   foreach f of loc allf {
      import excel `anything' using "`using'/`f'" ///
      ,`describe' `clear' `options'
      if ("`describe'" != "") continue
      gettoken fn : f ,p(".")
      sa "`save'/`fn'.dta" ,`replace'
   }
end
e

1.0.1   07sep2012   allow loading a subset of Excel files
               allow option -describe-
               import xls and xlsx files
               code polish
1.0.0   06sep2012   published
               http://www.stata-forum.de/stata-syntax-f18/import-mehrerer-excel-dateien-gleichzeitig-t180.html



Hier das help file

Code: Alles auswählen

{smcl}
{* version 1.0.1 07sep2012}{...}
{cmd:help impxlsfolder}
{hline}

{title:Title}

{p 5}
{cmd:impxlsfolder} {hline 2} Import all Excel files from a directory
and save them as Stata datasets


{title:Syntax}

{p 8}
{cmd:impxlsfolder} [{it:{help import_excel##extvarlist:extvarlist}}]
{helpb using}
[{cmd:"}]{it:directory}[{cmd:"}] [{cmd:,} {it:options}]


{p 5}
where {it:directory} is the directory, where the Excel files are
stored


{title:Description}

{pstd}
{cmd:impxlsfolder} imports all Excel files in a directory and saves
them as Stata datasets. All {hi:.xls} and {hi:.xlsx} files in
{it:directory} are saved as {hi:.dta} files.


{title:Options}

{phang}
{opt sa:ve(dirc)} specifies the directory to store the Stata datasets.
Default {it:dirc} is {it:directory}. Double quotes may be omitted.

{phang}
{opt replace} allows Stata datasets to be replaced if they already
exist.

{phang}
{it:{help import excel:import excel options}} are options allowed with
{cmd:import excel}. If option {opt describe} is specified, no
{hi:.dta} files are saved.

{phang}
{opt ext:ension(ext)} specifies the file extension for Excel files.
One of {hi:.xls} or {hi:.xlsx} is allowed. If specified only files
with extension {it:ext} are imported from {it:directory}.


{title:Example}

{phang2}{cmd:. impxlsfolder using c:/users/myxlsfiles ,save("c:/users/mydtafiles")}{p_end}


{title:Author}

{pstd}Daniel Klein, University of Kassel, klein.daniel.81@gmail.com


{title:Also see}

{psee}
Online: {help import excel}, {help extended_fcn:extended functions},
{help foreach}
{p_end}



[domibrown]

OAR danke dir vielmals. Ich kann mich erst ab Montag wieder damit beschäftigen, aber soweit erstmal einen ganz lieben Dank!

lg domi

[domibrown]

Hallo Daniel,

also ich hab alles eingebaut, das Help file geringfügig ergänzt und einige Testläufe laufen lassen. Siehe da, es funktioniert alles wie gewollt. Dafür dir nochmal vielen Dank. Ich werd mich jetzt analog zu diesem Code an ein MMERGE ado.file setzen und schauen wie weit ich mit dem -mmerge- Befehl alle in STATA importierten Excel Dateien zusammenbekomme. Ich werde meinen Ansatz dann hier im Forum posten und kann ihn dann vielleicht ja auf Verbesserungen überprüfen lassen.

Bis dahin lg domi