Jump to content
Sign in to follow this  
youknowwho4eva

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

Recommended Posts

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.

#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

Giggity

Share this post


Link to post
Share on other sites
youknowwho4eva

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


Giggity

Share this post


Link to post
Share on other sites
DaRam

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

INI is not designed for use as a data store ! :mellow:

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?

Share this post


Link to post
Share on other sites
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: :mellow: helps if i put the string replace with the appropriate string

Edited by youknowwho4eva

Giggity

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.