XProfan

English

Christof
Neuß

Hi,

I hexe time again large CSV-Files around. wants these possible quick and simply in a SQLite-data base überführen. the functions too fundamentally, but its me on some to put yet too slow.

2 Alternativen verfolge I straight
a) reading the CSV set for set, Anpassung on Insert-commands and reading
b) construction of/ one suitable CSV-File for Import-commands the SQLite-Konsole and then over Batchdatei started.

Variante a) is here (first) not maßgeblich, there set-Input eh sooner too slow is.

with Variante b) I get it simply not there, The SQLite-Konsole with a Parameter To slip, the TAB as Feldtrenner setting (\t not working), therefore would like I The File (To 1 GB big) before of TAB release and z.B. ";" as Feldtrenner settle. I read moreover over BlockRead always 1.000.000 Bytes one, machine these and write tappt im dunkeln into a new file. Experimentiert have I with Translate$ and Translate as nproc. both need by me ca. 30 sec, until 1 MB changed having. I have then The nproc something abgeändert, I only one characters swap wants

Compile Mark Separation

nProc translateONE(string src,fnd,rpl)

    {

        long p=1,cc=len(src), nude=0
        case cc<1 : return src
        case src=="" : return ""
        case fnd=="" : return src
        long fndCH=fnd[1]
        string sret=""', akt=""

        while 1

            {

                case p > cc : break

                if src[p]<>fndCH

                    {

                        nude=src[p]
                        sret=sret+chr $(nude)

                    } else

                    {

                        sret=sret+rpl

                    }

                    add p,1
                    continue

                }

                return sret

            }

need but almost just as long.

sees of/ one a Possibility, that yet - definitive - To speed?

its (too) therefore not integrally simply, The CSV-Files a SQLite-DB To transfer, because I do not always sure before white, with welchem characters The boxes separated are and whether it itself circa a File with einfachen or WideChar deals. both wars I in the meantime but relatively quick and sure out.

here the complete code the Moduls:

Compile Mark Separation

nProc translateONE(string src,fnd,rpl)

    {

        long p=1,cc=len(src), nude=0
        case cc<1 : return src
        case src=="" : return ""
        case fnd=="" : return src
        long fndCH=fnd[1]
        string sret=""', akt=""

        while 1

            {

                case p > cc : break

                if src[p]<>fndCH

                    {

                        nude=src[p]
                        sret=sret+chr $(nude)

                    } else

                    {

                        sret=sret+rpl

                    }

                    add p,1
                    continue

                }

                return sret

            }

            Proc CSV_in_SQLite_2'here is with one Batchfile worked

                'Dateiname, the be read should
                'Bezeichnung the scheduler in SQLite
                '0=keine Message, 1=MessageBox on the Schluss
                Parameters CSVDateiname$, EinleseTabelle$, MsgBox&
                Var TempErgebnis$="Temp_CSV_f_SQLite.CSV"
                Var IstWideChar&=0
                Var EinleseDaten$=""
                Declare EinleseDaten#
                Var EinleseBytes&=1000000'here can tuned go, wieviele Bytes at BlockRead each time be read
                Var Eingelesen&=0
                Var EinleseSatz$=""
                Var EinleseFelder$=""
                Var AnzEinleseFelder&=0
                Var FeldTrenner$=""
                Var SQLiteDLL&=db("slUseDLL","SQLite3.DLL")
                Var dbODVAss&=db("slInit","ODVAss.DB")
                Var SQLBefehl$=""
                Var x&=0
                Var XX&=progress()
                Var Zaehl&=0
                Erase TempErgebnis$
                Assign #1, CSVDateiname$ : OpenRW #1
                Assign #2, TempErgebnis$ : OpenRW #2
                'conversion in File without WideChar...
                Dim EinleseDaten#, EinleseBytes&
                Eingelesen&=BlockRead(#1,EinleseDaten#,0,EinleseBytes&)

                if Char$(EinleseDaten#,0,1)=Chr$(255)'File with WideChar

                    IstWideChar&=1

                endif

                if IstWideChar&=1

                    EinleseDaten$=EinleseDaten$+WideChar(EinleseDaten#,0,Eingelesen&)

                else

                    'normal Zeichensatz
                    EinleseDaten$=EinleseDaten$+Char$(EinleseDaten#,0,Eingelesen&)

                endif

                EinleseFelder$=Left$(EinleseDaten$,InStr(Chr$(10),EinleseDaten$)-1)'so having we The Überschriften
                EinleseFelder$=Translate$(EinleseFelder$,Chr$(9),"¦")
                'Einlesedaten$ remaining simply so. then are The Überschriften in the first row
                'EinleseDaten$ =Mid$(EinleseDaten$,InStr(Chr$(10),EinleseDaten$)+1,InStr(Chr$(0),EinleseDaten$)-Len(EinleseFelder$)-2) 'so are The Überschriften out

                if Instr(Chr$(0),EinleseDaten$)<>0

                    EinleseDaten$=Left$(EinleseDaten$,Instr(Chr$(0),EinleseDaten$)-1)

                endif

                Zaehl&=Zaehl&+Len(EinleseDaten$,Chr$(10))
                Print "Anfang"
                EinleseDaten$=TranslateONE(EinleseDaten$,Chr$(9),"¦")
                Print "Ende first Satz"
                Block Write #2, EinleseDaten$

                WhileLoop 10000'The worth becomes well never access go

                    SetText XX&, stature$("###,###,###",&Loop)
                    Dispose EinleseDaten#
                    EinleseDaten$=""
                    Dim EinleseDaten#, EinleseBytes&
                    Eingelesen&=BlockRead(#1,EinleseDaten#,0,EinleseBytes&)

                    if IstWideChar&=1

                        EinleseDaten$=WideChar(EinleseDaten#,0,Eingelesen&)

                    else

                        'normal Zeichensatz
                        EinleseDaten$=Char$(EinleseDaten#,0,Eingelesen&)

                    endif

                    if Instr(Chr$(0),EinleseDaten$)<>0

                        EinleseDaten$=Left$(EinleseDaten$,Instr(Chr$(0),EinleseDaten$)-1)

                    endif

                    Print "Anfang "+Str $(&Loop)
                    EinleseDaten$=TranslateONE(EinleseDaten$,Chr$(9),"¦")
                    Print "Ende "+Str $(&Loop)
                    Zaehl&=Zaehl&+Len(EinleseDaten$,Chr$(10))
                    Block Write #2, EinleseDaten$

                    if eof(#1)

                        Break

                    endif

                EndWhile

                Close #1
                Close #2
                'create Table in dbODVAss...
                'Datenbanktabelle create/bereinigen

                if db("slTableExists",dbODVAss&,EinleseTabelle$)<>0'scheduler existing, then delete

                    SQLBefehl$="Drop Table "+EinleseTabelle$
                    db("slSQLExec",dbODVAss&, SQLBefehl$,1)

                endif

                'boxes in scheduler Tab_Felder present or. before available delete

                if db("slTableExists",dbODVAss&,"Tab_Felder")<>0'scheduler existing

                    SQLBefehl$="Delete from Tab_Felder where TabName='"+EinleseTabelle$+"'"
                    db("slSQLExec",dbODVAss&, SQLBefehl$,1)

                else

                    SQLBefehl$="Create Table Tab_Felder (TabName, FeldName, FeldBezei, FeldArt)"
                    db("slSQLExec",dbODVAss&, SQLBefehl$,1)

                endif

                'Feldtrenner in Originaldatei to determine

                if Len(EinleseFelder$,"¦")>Len(EinleseFelder$,";")

                    FeldTrenner$="¦"

                else

                    FeldTrenner$=";"

                endif

                if Len(EinleseFelder$,",")>Len(EinleseFelder$,FeldTrenner$)

                    FeldTrenner$=","

                endif

                'boxes from the actually scheduler present  ==> Feldtrenner is then one comma
                EinleseFelder$=TranslateONE(EinleseFelder$,Chr$(9),";")
                EinleseFelder$=TranslateONE(EinleseFelder$,",",".")
                EinleseFelder$=TranslateONE(EinleseFelder$,";",",")
                EinleseFelder$=Translate$(EinleseFelder$,",,,",",0,0,")
                EinleseFelder$=Translate$(EinleseFelder$,",,",",0,")
                EinleseFelder$=Translate$(EinleseFelder$,", ,",",0,")
                EinleseFelder$=TranslateONE(EinleseFelder$,":","")
                AnzEinleseFelder&=Len(EinleseFelder$,",")

                WhileLoop AnzEinleseFelder&

                    SQLBefehl$="Insert into Tab_Felder (TabName, FeldName, FeldBezei) Values ('"+EinleseTabelle$+"','F"+stature$("0000",&Loop)+"','"+Substr$(EinleseFelder$,&Loop,",")+"')"
                    db("slSQLExec",dbODVAss&, SQLBefehl$,1)

                EndWhile

                EinleseFelder$="("

                WhileLoop AnzEinleseFelder&'thereafter are EinleseFelder$ The (new) F...-terms

                    EinleseFelder$=EinleseFelder$+"F"+stature$("0000",&Loop)+","

                EndWhile

                EinleseFelder$=left$(EinleseFelder$,Len(EinleseFelder$)-1)+")"
                SQLBefehl$="Create Table "+EinleseTabelle$+" "+EinleseFelder$
                db("slSQLExec",dbODVAss&, SQLBefehl$,1)
                db("slDone",dbODVAss&)
                FreeDll SQLiteDLL&
                'reading Datensätze in dbODVAss...
                'SL-File create
                Erase "SQLite_Import.sl"
                Assign #1, "SQLite_Import.sl" : Rewrite #1
                Print #1, "Delete from "+EinleseTabelle$+";"
                'Print #1, ".separator "+FeldTrenner$
                'Print #1, ".fashion csv"
                Print #1, ".import Temp_CSV_f_SQLite.CSV "+EinleseTabelle$
                Close #1
                'Batchdatei create
                Erase "SQLite_Import.bat"

                if FeldTrenner$=Chr$(9)

                    FeldTrenner$=Chr$(34)+"\\t"+Chr$(34)

                endif

                Assign #1, "SQLite_Import.bat" : Rewrite #1
                Print #1, "@echo off"
                Print #1, "SQLite3.exe -separator "+FeldTrenner$+" ODVAss.DB < SQLite_Import.SL"
                'Print #1, "Pause"
                Close #1
                ChDir $PROGDIR
                SQLBefehl$="SQLite_Import.bat"
                WinExecWait(SQLBefehl$,1)
                DestroyWindow(WegwerfFenster&)
                Erase TempErgebnis$

                if MsgBox&=1

                    MessageBox("Daten get into data base loaded!","Meldung",0)

                endif

            Endproc

Merci and greetings

Christof

XProfan X3
Win10 16 GB RAM

09/10/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

- Page 1 -

H.Brill

3 Mio. Lines is ne crowd

I faith, so 260.000 is the supreme, what The Listboxliste
take in can. one could itself but behelfen, because
one whom index (2.Parameter) screen. If this z.B.
250.000 access, simply The list delete. around the etappenweise
To manage, must one hold with index MOD 250.000
to check on.
ought to one time try.

Benutze XPROFAN X3 + FREEPROFAN
Wir sind die XProfaner.
Sie werden von uns assimiliert.
Widerstand ist zwecklos!
Wir werden alle ihre Funktionen und Algorithmen den unseren hinzufügen.

Was die Borg können, können wir schon lange.

09/10/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Jörg
Sellmeyer

must You The File because absolutely line by line Edit? If you The data as CSV exist have, can you The still simply in a String loading and the once with Translate verwursten and already should any Trenner supplant his.
If I it correctly. in recollection have, is the maximum String-Length by the Hauptspeicher terminable - ought to means then no ernsthaftes trouble present.

XProfan X4
Windows XP SP2 XProfan X4
... und hier mal was ganz anderes als Profan ...

09/10/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Christof
Neuß

Tja... very so laboriere I already a couple days rum...

- with ca. 260.000 Lines is Schluss. would be indeed To beautiful been circa true To his. Related to the second Parameter have I do not understood.

- in the first can 1 GB not time so simply in a String reading (there comes sooner one Speicherfehler, as one believes) and secondly have I something like already attempts... After over 2 hours Laufzeit have I the program aborted.

will be I well yet something tüfteln must...

XProfan X4
Win10 16 GB RAM

09/10/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

H.Brill

The second Parameter is hold the index.
with Move("ArrToList", a[]) is it z.B. the
index the Arrays a[index]. with FileToList
should it then The jeweilige row the File his.

the yields The Possibility z.B. only pieces one
Arrays into Listboxliste To schieben, because
one whom index with > or < abfragt.

The MoveListProc shining there neither too
quick To his :

Compile Mark Separation

Declare String a[200000]
Cls
Mat a[] = "Hallo, You, there !"
Print "Fertig..."
Set("MoveListMode", 1)
Print Move("ArrToList", a[])
Print GetString$(0, 100000)
MoveListProc
Parameters String s, int index
Declare String z

If Get("MoveListMode") = 1

    z = Translate$(s, ",", "|")
    AddStrings z

EndIf

ENDPROC

WaitKey

here sees one beautiful, How quick one MAT goes
to the Proc. Schade, that with MAT only solid
expressions weg.
Übrigens : You need Yes with the MoveListProc The
Listboxliste none To using. it's located Yes in your
Gusto, what You with the string s and the Int i anstellst.

roughly said, position Roland whom index and the moreover gehörenden
worth (as String) the To movenden list/ aray/File to
Disposal. The MoveListProc is then, like a Schleifenkörper :
WhileLoop .... EndWhile

09/10/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Christof
Neuß

Hi,

thanks. again what learnt.

time see, whether/How I the use can.

XProfan X4
Win10 16 GB RAM

09/10/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

H.Brill

Perhaps can indeed with the Inline-ASM
something nachhelfen. look time in Paules Forum.
at least, what creep concerns. too the
Translate$() ließe itself so supplant or.
would as ASM-Proc faster.

Evtl. can you diesbezüglich someone what basteln.
Volkmar is there a good contact persons.

faster as ASM is not More.

09/11/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Christof
Neuß

BTW: gives it a Begrenzung with BlockRead and/or Block Write?
If I More as 1 MB reading wants, I get a Error Message.

Exception EAccessViolation in Module...
Access violation with address...

Hab' The boundary not very ausgetestet, but with 1.000.000 Bytes hat's yet functions, with 1.200.000 not any more.

XProfan X4
Win10 16 GB RAM

09/13/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

E.T.

.... strain time probiert and via BlockRead 23,5 MB in a area read, without Problems....

(neckline a Progg, where I the so without Problems use) :

Compile Mark Separation

'list$ is a before selected File
Dim area#, FileSize(list$)
BlockRead(list$, area#, 0, FileSize(list$))

and then work I simply with the area moreover, is Yes then The whole File drinne what about me can so make, I wants...

If nat. the RAM then ausgeht, must one it "zerhackstückeln"

Grüße aus Sachsen... Mario
WinXP, Win7 (64 Bit),Win8(.1),Win10, Win 11, Profan 6 - X4, XPSE, und 'nen schwarzes, blinkendes Dingens, wo ich das alles reinschütte...

09/13/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

H.Brill

what one too take can, is Frank Abbings List view.dll.

Compile Mark Separation

Declare Memory area, Long hdll, anz
hdll = UseDLL("Listview.dll")
ImportFunc(hdll, "ExchangeSeparator", "TranslateX")
CLS
Dim area, FileSize("E:\list.txt")
Assign #1, "E:\list.txt"
OpenRW #1
anz = BlockRead(#1, area)
Close #1
Print "Fertig..."
Print "Taste to that Austausch"
WaitKey
TranslateX(area, FileSize("E:\list.txt"), Ord(","),Ord("|"),1)
Print "Fertig..."
Waitkey
Dispose area
FreeDLL hdll
End

there's no Time messbar, so quick does it.

PS: The ProSpeed.dll of Frank has there yet More
in things reaches To afford.

09/15/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

- Page 2 -

H.Brill

with ASM for a single characters To supplant has me Volkmar
a Proc made :

Compile Mark Separation

Declare Memory area
CLS
Dim area, 1000
String area, 0 = "1,", "2,", "3,", "4,", "5,", "6,", "7,", "8,", "9,", "10"
//TranslateX(area#, Size&, OrgChar%, NewChar%)
ASM "TranslateX", 4
PUSH   EBX
PUSH   ECX
PUSH   EDX
PUSH   ESI
MOV    EDI, Par1// address Quellstring
MOV    ECX, Par2// length Quellstring
MOV    EBX, Par3// code Suchzeichen
MOV    EDX, Par4// code Ersatzzeichen
MOV    AL, BL
MOV    AH, DL
XOR    EDX, EDX// EDX delete
Search:
OR     ECX, ECX// Quelllänge 0?
JZ     IsLen// length 0 access, String through
DEC    ECX// length down count
SCASB
JNZ    Search// byte not found
MOV    [EDI - 1], AH// supplant
INC    EDX// supplant count (can entfallen)
JMP    Search
IsLen:
MOV    EAX, EDX
POP    ESI
POP    EDX
POP    ECX
POP    EBX
EndASM
Print Char$(area, 0, 40)
Print "Stellen supplant : ", Str $(TranslateX(area, SizeOf(area), Ord(","), Ord("|")))
Print Char$(area, 0, 40)
WaitInput
Dispose area
End

Perhaps can you it Yes need.

09/15/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Christof
Neuß

Hello H.

many Thanks for your endeavours. Yes, the helps objectively again a little bit moreover. goes too 'ne corner faster.

Genial would now following ASM-Loop:

appeal with TranslateX(Addr(Text$),Len(Text$)-1,a,b,c,d,e,f,g,h,k,l,m,n,o,p,q,r)
where a-h letters(codes) are, to them sought and is k-r The letters(codes) are, The for inserted go.
so could I in a Aufwasch same several Ersetzungen make (z.B. tab against "|" and "/" against " " and ...
thereby becomes The length the Strings not changed. One characters becomes against very one other characters ausgetauscht.

Also have I (unfortunately) too yet such a thing To supplant:
"|||" against "|0|0|". but the would The length the Strings change. must not absolutely his. the goes letztlich too over Translate$ quite fix.

Greeting

Christof

XProfan X4
Win10 16 GB RAM

09/17/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

H.Brill

Perhaps help you there too The regulären expressions moreover.
with Set("RegEx", 1) eingeschaltet, wirken also at
XProfan-own Translate$.
straight the latest, "|||" against "|0|0|" to exchange, would not heavy.
the Suchmuster this would :
neuerstring$ = Translate$(alterstring$, "[|||]{3,3}", "|0|0|")

too for the Search of groups [a-h] o.ä. are so The reg. expressions
very suitable.

Lies you times the Help moreover through. with ask can you you Yes report.

09/17/18 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

◄