14.05.2007Convert fixed length column to Excel

I had to convert the output from sqlplus to an excel sheet. The problem was, that I had no influence on the creation, so I could spool the file directly to excel.

All I got were spool files like that:

      CLASS_ID SUPER_CLASS_ID   COUNT(*)
============== ============== ==========
       128           8765         18
       128           8765        131
       128           8765          7

My idea was to take the line used to seperate the column titles and the data as the data that gives me the length of each column (refered as the column line in my function below). Then just simply extract a block from start to end (as given) based on the gathered column length information.

Maybe someone can use this piece of VBA code:

Public Sub convertFixed()
    Dim sFile As String
    Dim iCount As Long
    Dim iSubCount As Long
    Dim iLastCount As Long
    Dim i As Integer
    Dim Length() As Integer
    Dim sValue As String

    Dim iStart As Integer
    Dim iEnd As Integer
    Dim iColumn As Integer


    ''clear previous data
    Cells.ClearContents

    ''choose the file with the windows file dialog
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Choose log file to convert"
    .InitialFileName = ThisWorkbook.Path
    .InitialView = msoFileDialogViewDetails
    .Filters.Clear

    ''the filter is set to .log .spool and .txt files
    .Filters.Add "Log file", "*.log, *.spool, *.txt"

    .ButtonName = "Load"
    If .Show = -1 Then
        sFile = .SelectedItems(1)
    Else
        ''stop execution if no file is selected
        Exit Sub
    End If
    End With


    ''we need the following informtion (gather with InputBoxes)
    ''line to scan the column lengh (space delimiteted) -> line number
    ''begin of block (line number)
    ''end of block (line number)
    iStart = InputBox("Enter line number where block starts:", "Line number of Start")
    iEnd = InputBox("Enter line number where block ends:", "Line number of End")
    iColumn = InputBox("Enter line number of fixed length specification", "Line number for fixed length")

    ''the first loop is to get the correct column length
    'open the file
    Open sFile For Input Access Read As #1
    iCount = 1
    ''loop until end of file (EOF) is reached
    Do Until EOF(1)
    Line Input #1, sLine
    If (iCount = iColumn) Then
        ''exit if the column line (to scan the length) is reached
        Exit Do
    End If
    iCount = iCount + 1
    Loop
    ''close the file
    Close #1

    iCount = 0
    iSubCount = 1
    iLastCount = 0
    ''sLine now contains a line that gives us the fixed length of the columns
    sLine = RTrim(sLine)
    Do Until iSubCount > Len(sLine)
    If (Mid(sLine, iSubCount, 1) = " " Or iSubCount = Len(sLine)) Then
        ''the Length array stores the length of each column (needs to be ReDim'ed with each new column)
        ReDim Preserve Length(iCount)
        Length(iCount) = iSubCount - iLastCount
        iLastCount = iSubCount
        iCount = iCount + 1
    End If
    iSubCount = iSubCount + 1
    Loop

    ''open the file to extract the block based on the column length stored in the Length-Array
    Open sFile For Input Access Read As #1
    iCount = 1
    iSubCount = 1

    ''loop until the end of the block is reached
    Do Until iCount > iEnd
    Line Input #1, sLine
    iLastCount = 1
    If (iCount >= iStart) Then
        For i = 0 To UBound(Length)
            sValue = Trim(Mid(sLine, iLastCount, Length(i)))
            ''prevent application errors with adding of "'" in front of special characters
            If (Left(sValue, 1) = "=" Or Left(sValue, 1) = "-" Or Left(sValue, 1) = "+") Then
                sValue = "'" & sValue
            End If
            ''insert the data to the current Excel sheet
            Cells(iSubCount, i + 1).Value = sValue
            iLastCount = iLastCount + Length(i)
        Next i
        iSubCount = iSubCount + 1
    End If
    iCount = iCount + 1
    Loop
    ''close the file
    Close #1

End Sub

27.12.2006Excel to CSV with Python

I wanted to handle a simple task: convert a bunch of XSLs to CSVs. Well I wrote a small Script in VBScript and it worked. Then I needed to change the List separator, so I did that in my System settings, and then my Script didn’t work anymore, because it just ignored my changed settings.

I googled a lot and didn’t find the solution (only people with the same problem). It seems that VBScript (or WSH) is using it’s own locale that you can not change.

So I began to look for alternatives. Python can handle the COM interface when you install PyWin32 (the version must match the python version you’re using). So I just rebuild my script with python and it’s COM-Interface. Et voil�: it takes the user defined settings and converts my files in the way I want them.

#import win32 stuff
from win32com.client import Dispatch
#import other needed
import glob,sys,os

path = os.path.split(sys.argv[0]) #gather the path of the script
path = path[0]
path = path + '\\' #append \ at the end of path (when using *NIX you must use / instead
files = glob.glob('*.xls') #get list of files

xlApp = Dispatch("Excel.Application") #create Excel COM-Object
xlApp.DisplayAlerts = False
for file in files:
    xlWB = xlApp.Workbooks.Open(path + file)
    xlWB.SaveAs(path + file.replace(".xls",".csv"), 6 ) #6 = xlcsv
    xlWB.Close()
xlApp.Quit()
del xlApp

27.12.2006Information about Oracle Directories

I know what I write is quite newbieish, but I needed information about a directory I’ve created, means which path it’s pointing to:

select * from all_directories dir;

There simply is no userdirectories, and with allobjects I didn’t get what I wanted.

12.12.2006SQL Start-Script generator

Sometimes it’s so simple:

import glob
files = glob.glob('*.*');
f=open('start_script.sql', 'w')
f.write('PROMPT This is an automatic generated start script\n');
f.write('PROMPT\n');
f.write('SPOOL start_script.LOG\n\n');
for filename in files:
    if filename == 'start_script.sql':
        continue
    f.write('PROMPT Starting script ' + filename + '\n');
    f.write('@@' + filename + '\n');
f.write('\nPROMPT\n');
f.write('PROMPT Finished.\n');
f.write('SPOOL OFF\n');
f.close();
exit();

Explanation:

First get a file list of the current directory (glob.glob(‘.’), this won’t return you directories since they don’t have an extension, if your files doesn’t have an extension, either: your fault). Then open a file called start_script.sql which will held our start-script (pretty self-explanatory). Then we generate a “@@” line for each file (this means if you have non-SQL-files in that directory you have to clean-up your script or edit it that it only takes .sql files (tip: glob.glob(‘*.sql’)). The start-script itself is excluded but the python script will be included (can easily be made excluded as well). After the loop the file gets closed and that’s it.

07.11.2006Delete duplicate records with Orcle

Don’t ask me why I needed to do that, but I had a lot (about 14 Mio.) duplicate records in a table.

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

helped me out. (thanks to www.devx.com)

Next time I write my queries to filter those out before inserting them…

02.11.2006PISA-Test

Danke Baba fürs schicken des Artikels ;)

War so im Tagi (weiss leider nicht genau von welchem Tag):

18.10.2006Need to generate a GUID with Oracle?

I just searched the web for this simple task, and it was difficult to find.

Maybe anyone working a bit with Oracle knows this, but it helped me, maybe I can help someone out there too, so here it is:

select sys_guid() from dual

This generates a 32 character GUID (fits best in a RAW(16) field).

24.01.2006Psychologie vs. Psychiatrie

Wie das so ist, wenn man gemütlich zusammen sitzt, vorzugsweise mit gutem Essen, spricht man über Gott und die Welt und plötzlich:

Was ist eigentlich der Unterschied zwischen einem Psychologen und einem Psychiater?

Solche Dinge brennen auf der Zunge, man gibt sich der Unwissenheit zu erkennen, hat aber gute Aussichten nach dem Gespräch mehr Wissen zu haben, denn schon das Fernsehen hat uns gelehrt: “Wer, wie was; wieso, weshalb, warum? Wer nicht fragr bleibt dumm!”

Nun frei nach Kopf, die Definition die ich aus dem Abend mitgenommen habe:

Psychologe

befasst sich mit dem menschlichen Verhalten, wie wir reagieren, wie wir ticken.

Wann gehe ich dahin?

Einen Psychologen besuche ich, wenn ich mich beispielsweise in einer Lebenskrise befinde und dafür Hilfe will.

Psychiater

befasst sich mit psychischen Krankheiten wie Psychosen, Sucht usw.

Wann gehe ich dahin?

Meist werde ich von einem Psychologen oder dem Hausarzt an einen Psychiater verwiesen. Es handelt sich dabei ebenfalls um einen Dr.med., der sich spezialisiert hat.

25.10.2005CSV seperator character

In my particular case I got an excel sheet from the customer to import in a DB. The idea is to use a CSV as the base for an Oracle external table and then import the data in the real table with a “insert into select”.

When I loaded the excel sheet and saved it as a CSV, the semicolon (“;”) was used as a seperator. I therefore created my external table and worked with the data. Then i realized that the order of the fields get messed up, because there were free text fields that contained “;” as data.

I didn’t find an option in excel to change the seperator character, but as usually Google is your friend.

This character is saved directly in your system in the “Region and Language Options” in the “Control Panel” (yes, I’m talking about MS Windows). You can selected there the “Customize” button and change the “List separator” to the value you want.

First I tried to set this to “###”, but this didn’t work, excel then used a single space as separator. It seems it’s not allowed to use multiple characters. Now, I use “#” and everything works fine.

19.04.2005Schengen Propaganda

Tatsächlich, wir sind an einem Punkt angekommen, der beweist, dass Menschen schnell vergessen, der beweist, das alte Methoden trotz Intelligenz Wirkung zeigen.

Ich spreche hier von Propaganda. Als Einstieg lohnt es sich folgendes zu lesen:

Auf dieser Seite wird das Prinzip der Propaganda erläutert. Wie sie funktioniert, warum sie funktioniert, was genau dahinter steckt.

Ich habe ein wenig recherchiert (Google weiss fast alles, wenn man nett fragt) und will hier Parallelen ziehen. Eines vorweg: ich will weder das tun der Nazis verharmlosen noch die Propaganda der Schengen-Gegner mit den Nazis gleichsetzten. Ich will nur zeigen, dass mit den gleichen Mitteln gearbeitet wird. Man setzt auf Angst, verunsichert den Betrachter und versucht ihn schliesslich zu überrennen.

Als ich zum ersten mal die Schengen-Nein Plakate gesehen habe, haben sie mich unweigerlich an die Nazi-Propaganda Plakate aus dem Geschichtsunterricht erinnert.

Schengen Plakate

Schengen Nein PlakatSchengen
Nein Plakat

Nazi Propaganda Plakate

Nazi-Plakat "Mütter kämpft für eure Kinder" Nazi-Plakat: "Unsere letzte Hoffnung Hitler"

Bitte denkt erst nach bevor ihr mich verurteilt. Kommentare wie immer erwünscht.

Links zum Thema: German Propaganda Archive @calvin.edu Schengen-Nein Komitee