Copy massive Excel files into much easier to handle .ini's

[youknowwho4eva]

My first scripts to do this actually work in Lotus approach, but now it opens the dbf(database file) in excel, and cleans it out. My earlier scripts also did this one file at a time, and required special work for each one such as rows and columns and such. This code (is in the process of doing) opens 19 dbf's, one at a time and robs them of their data (except when I occasionally get an excel.au3 error). The string replace in there is because for some reason, when excel opens the dbf's it has _ instead of spaces. And it shows ever excel file because thats how I knew I was finally doing it right. Please excuse my extra includes, this code has been constantly evolving.

expandcollapse popup

#include <Excel.au3>
#include <ButtonConstants.au3>
#Include <GuiListView.au3>
#include <ClipBoard.au3>
#include <Array.au3>
DirCreate(@desktopdir & "\new\")
$search = FileFindFirstFile("s:\win95\data\*.dbf")
dim $list[100]
$f = 1
While 1
    $file = FileFindNextFile($search)
    if @error then ExitLoop
        $list[$f] = $file
        $f += 1
    WEnd
    FileClose($file)
    $f -= 1
    For $i = 1 to $f step +1
        
    
opt("TrayIconDebug",1)
$excel = _excelbookopen("S:\win95\data\" & $list[$i],1,True)
    $o = 1
do 
    $row = _excelreadcell($excel, 1, $o)
    $o += 1
Until StringLen($row) < 1
$o = $o - 2
    $c = 1
Do
    $col = _excelreadcell($excel, $c, 1)
    $c += 1
Until StringLen($col) < 1
$c = $c - 2
For $n = 1 to $c Step +1
    If $n = 1 Then
        $title = _excelreadarray($excel,$n,1,$o,0,1)
    Else
        For $r = 1 to $o Step +1
            if $r = 1 Then
                $model = _Excelreadcell($excel, $n, $r)
            Else
                $item = _Excelreadcell($excel, $n, $r)
                $newitem = StringReplace($item,"_"," ")
                If stringlen($item) > 0 Then IniWrite(@desktopdir & "\new\" & $list[$i] & ".ini",$model,$title[$r],$newitem)
            EndIf
        Next
    EndIf
Next
_ExcelBookClose($excel,0,0)
Next

[youknowwho4eva]

Hmmm, actually I just noticed the stringreplace isn't working. does anyone see why?

[DaRam]

Why would you not write the output to a CSV or XML File?

INI is not designed for use as a data store !

Reg. the string replace, temporarily inject a If StringInStr after with a message box to display the text that failed to replace.

Hmmm, actually I just noticed the stringreplace isn't working. does anyone see why?

[youknowwho4eva]

I'm putting them in an ini to have them auto fill my gui. I'm not familiar with CSV or XML. It will be for fast viewing of data on a more user friendly gui. That's why its 19 dbf's in this directory, I'm making it so you don't have to go to 5 different approach databases to view stuff, and Approach has issues with the amount of data in the database. I was thinking the string replace might not be working because the data isn't in the string yet. So I put a sleep in there, haven't tried it yet. Before I put that in there I had a seperate program that was

$search = FileFindFirstFile("T:\Documents and Settings\ALLEN PENROD\Desktop\new\*.ini")
dim $list[100]
$f = 1
While 1
    $file = FileFindNextFile($search)
    if @error then ExitLoop
        $list[$f] = $file
        $f += 1
    WEnd
    FileClose($file)
    $f -= 1

    For $i = 1 to $f
        $model = IniReadSectionNames("T:\Documents and Settings\ALLEN PENROD\Desktop\new\" & $list[$i])
        if @error Then MsgBox(0,"",$list[$i])
        For $r = 1 to $model[0]
        $keys = IniReadSection("T:\Documents and Settings\ALLEN PENROD\Desktop\new\" & $list[$i],$model[$r])
        For $t = 1 to $keys[0][0]
            $newkey = StringReplace($keys[$t][0],"_"," ")
            IniDelete("T:\Documents and Settings\ALLEN PENROD\Desktop\new\" & $list[$i],$model[$r],$keys[$t][0])
            IniWrite("T:\Documents and Settings\ALLEN PENROD\Desktop\new\" & $list[$i],$model[$r],$newkey,$keys[$t][1])
        Next
    Next
Next

and it worked well, but when the other crashed I decided to kill two birds with one stone.

Edit: helps if i put the string replace with the appropriate string

Edited November 6, 2008 by youknowwho4eva