[MySQL] import txt met vaste waarde ipv komma gescheiden

[Verwijderd]

Is het mogelijk een txt bestand in MySQL te importeren dat niet komma gescheiden is, maar vaste waarden heeft.
Normaal als je iets importeerd zijn de velden gescheiden door een lijstscheidingsteken (; , : oid). Maar het bestand dat ik eigenlijk moet gebruiken heeft vaste waarden. Dus 10 posities voor het eerste veld. Het veld dat daarop volgt is 40 posities en het daarop volgende veld is 25 posities. En zo komen er nog wat.
Nu wil ik dit dus graag importeren in een MySQL database. Is dat mogelijk en zo ja hoe? Ik gebruik phpmyadmin.

Een kleine tegenslag misschien, het gaat om +- 200.000 records. Dus even importeren in Excel en daarna als csv oid exporteren gaat niet lukken.

Ik hoop dat iemand suggesties heeft.

[BobDay]

volgens mij is dit niet mogelijk. Hier de syntax van LOAD DATA INFILE:

code:

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

Met een beetje slim te werk gaan kan dat met excel wel!

[gorgi_19]

Aangezien de import eenmalig is, kan mag het wel een tijdje duren, neem ik aan..

[gedachtenspinselmodus]
Open het tekstbestand.
Lees een regel
Splits deze op eerst op 40 spaties. (array van 2 waarden)
Splits vervolgens 1 van deze 2 op 25 spaties (heb je al drie kolommen)
Splits als laatste op 10 spaties (alle kolommen)
Nu heb je ze gescheiden kan je ze invoeren door middel van een Insert Statement.
Het is een redelijk inefficient script, maar het hoeft maar eenmalig gedaan te worden.
[/gedachtenspinselmodus]

[SPee]

OF maak een scrippie die het bestandje opent en na de eerste veld een scheidingsteken invoegt en na het tweede veld enz. dat doet.
Laat die het hele bestand dan doorlopen en dan heb je je scheidingstekens en kun je het gewoon importeren.

[marty]

ik zou er liever een nieuwe output van maken, met scheidingsteken, zoals SPee zegt, maar dan zou ik toch een combinatie doen van wat gorgi_19 ook zegt.
Per regel de boel splitten op die vaste waardes, dat je vier variabelen heb, daar een trim() over heen gooien en vervolgens weer een output regel genereren met scheidingstekens die je wegschrijft in een nieuw bestand

Als je namelijk gelijk gaat inserten en het gaat fout, zit je 200.000x iets fout in te voeren.

let ook ff op het bestaan van ' <- die kregen. die moeten wel ge-escaped worden

[Verwijderd]

Misschien heb je hier wat aan. Het beste kan je idd die lijst exporteren met excell naar een csv file, dan zit de import altijd goed. EN! importeren wil ook volgens mij wel als je een andere opmaak hebt. Er is een wizard in excel aanwezig.

PHP:

<?
 mysql_connect("localhost", "usr", "pass"); 
mysql_select_db("dbase"); 

$fcontents = file ('./bestand.csv'); 
# de bestandsnaam

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i]); 
      $arr = explode(";", $line); 
      # als bestand ; gescheiden is bovenstaande regel zo laten
      # anders:       $arr = explode("\t", $line);  // voor tab
 
      $sql = "insert into tabelnaam values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}

?>

[Verwijderd]

Verwijderd schreef op 11 november 2002 @ 08:49:
Misschien heb je hier wat aan. Het beste kan je idd die lijst exporteren met excell naar een csv file, dan zit de import altijd goed. EN! importeren wil ook volgens mij wel als je een andere opmaak hebt. Er is een wizard in excel aanwezig.

PHP:

<?
 mysql_connect("localhost", "usr", "pass"); 
mysql_select_db("dbase"); 

$fcontents = file ('./bestand.csv'); 
# de bestandsnaam

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i]); 
      $arr = explode(";", $line); 
      # als bestand ; gescheiden is bovenstaande regel zo laten
      # anders:       $arr = explode("\t", $line);  // voor tab
 
      $sql = "insert into tabelnaam values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}

?>

Bedankt voor jullie reacties. Klinkt toch hoopvoller dan ik dacht. Maar 2 dingen.
* Ik zou graag het bestand in Excel inlezen en het opnieuw exporteren, maar Excel accepteerd maar tot 65565 regels oid (kan iets meer of minder zijn, maar in de 65000). Daar mee leest is maar 1/3 van mijn bestand in.
Of hebben jullie een methode om dit probleem te omzeilen?

* Dan de 2e vraag: Wat doet dit laatste script precies? Als ik het goed begrijp haalt het witruimte weg. Maar is dit voor mij ook te gebruiken? Want het bestand is niet echt tab gescheiden, maar heeft echt vaste waarden.

[Verwijderd]

Even weer dit oude topic ophalen. Ik was even gestopt met bovenstaand, maar heb het deze week weer opgepakt. Ik heb nu wat meer kennis van PHP. Nier erg veel, maar wel meer En dat scheelt.

Heb nu het volgende script geschreven. Hiermee lees ik een txt bestand met vaste waarden in (0M2902135169Een titel en heeft 40 karakters Auteur komt erna 00002000). Nou ja zoiets ziet het bestand eruit zeg maar. Verschillende velden zonder lijstscheidingsteken, maar met een vaste breedte. Titel 40 tekens, auteur 26, prijs 8, enz.
Ik pak dus het bestand op, lees het in en plaats de output van een regel met lijstscheidingstekens in een nieuw bestand.
Maar dit duurt té lang. Na 5 minuten kapt mijn (web)server het script af. Ik heb het in de php.ini op 1800 seconden (30 minuten) gezet, maar ook na een reboot werkt dat niet.

Is iemand hier zo bij de hand dat ie het script wat efficiënter kan maken? Althans, mij tips geven hoe ík dat doe!

PHP:

<?php
$file = "titel2.txt";
        $file2 = fopen("$file", "r");
                while (!feof($file2)) {
                $line = fgets($file2, 512);
                $isbn = "$line[1]$line[2]$line[3]$line[4]$line[5]$line[6]$line[7]$line[8]$line[9]$line[10]";
                $titel = "$line[11]$line[12]$line[13]$line[14]$line[15]$line[16]$line[17]$line[18]$line[19]$line[20]$line[21]$line[22]$line[23]$line[24]$line[25]$line[26]$line[27]$line[28]$line[29]$line[30]$line[31]$line[32]$line[33]$line[34]$line[35]$line[36]$line[37]$line[38]$line[39]$line[40]$line[41]$line[42]$line[43]$line[44]$line[45]$line[46]$line[47]$line[48]$line[49]$line[50]";
                        $titel = rtrim($titel);
                $auteur = "$line[51]$line[52]$line[53]$line[54]$line[55]$line[56]$line[57]$line[58]$line[59]$line[60]$line[61]$line[62]$line[63]$line[64]$line[65]$line[66]$line[67]$line[68]$line[69]$line[70]$line[71]$line[72]$line[73]$line[74]$line[75]$line[76]";
                        $auteur = rtrim($auteur);
                $prijs = "$line[77]$line[78]$line[79]$line[80]$line[81]$line[82]$line[83]$line[84]";
                        $prijs = wordwrap($prijs, 6, ",", 1);
                        $prijs = ltrim($prijs, "\0");
                $boekcode = "$line[87]";
                $uitgnr = "$line[89]$line[90]$line[91]$line[92]$line[93]$line[94]";
                        $uitgnr = rtrim($uitgnr);
                $versweek = "$line[110]$line[111]$line[112]$line[113]$line[114]$line[115]";
                $boeksoort = "$line[130]";
                $bindwijze = "$line[131]$line[132]$line[133]";

                
                $titel = $isbn . ";" . $titel . ";" . $auteur . ";" . $prijs . ";" . $boekcode . ";" . $uitgnr . ";" . $versweek . ";" . $boeksoort . ";" . $bindwijze;
                
                
                $file3 = "titel3.txt";
                $file4 = fopen("$file3", "a");
                fputs($file4, $titel . "\n");
                fclose($file4);
                }
        fclose($file2);
        echo "Voltooid";
?>

Alvast bedankt.

Groet,
Hans

[Verwijderd]

Als het om iets eenmaligs gaat, zou ik gewoon het bestand handmatig opdelen... (dat lijkt me de snelste oplossing)

[beetle71]

al die $line[1]$line[2] enz kun je vervangen door:

PHP:

$isbn=substr($line,0,10);

dit selecteer 10 tekens vanaf het 0de (nul-de) teken.

enz. enz.

www.php.net/substr

[Verwijderd]

Zou ik graag doen, maar er staan 200.000 records ongeveer in dat bestand. Ik heb echt geen flauw idee hoe ik dat krijg opgedeeld. Openen met kladblok gaat natuurlijk niet, dus wordt Wordpad gebruikt. Als ik dan een X aantal regels selecteer en een copy (ctrl c) actie op loslaat wil dat niet helemaal, dat vraagt verschrikkelijk veel geheugen. Ik krijg bij het plakken ook een melding dat ik onvoldoende geheugen tot mijn beschikking heb. Heb 256 MB DDR erin zitten. Athlon XP 1700 processor.

In Excel importeren gaat ook niet, omdat die maar 65xxx records accepteerd, en ik heb er dus meer...

Het proces mag wat mij betreft wel een half uur in beslag nemen, maar daarvoor krijg ik mijn server niet goed geconfigureerd.

Heb je dan misschien een ander idee hoe ik het bestand opdeel?

Groet

[Verwijderd]

beetle71 schreef op 12 March 2003 @ 20:21:
al die $line[1]$line[2] enz kun je vervangen door:

PHP:

$isbn=substr($line,0,10);

dit selecteer 10 tekens vanaf het 0de (nul-de) teken.

enz. enz.

www.php.net/substr

Ah fijn!!! Ben blij dit te horen. Ik had een vermoeden dat zoiets bestond, maar wist niet dat die functie substr heette. Thnx. In VB is dit iets met LEFT ofzo, daarom was ik al op zoek gegaan naar een vergelijkbare functie, maar niet gevonden. Great!
Ga ik meteen aanpassen.

[MBV]

Ik zie een andere oplossing. Kan je niet een kort .c progje schrijven? Lijkt vrij veel op PHP qua structuur, en kan dit zeker aan. Dan kan je hem gewoon de hele nacht laten rekenen, zonder problems. En kan je in PHP niet iets knutselen dat het bestand opdeelt? gewoon 1000 regels inlezen, in een *.001 bestand zetten, volgende inlezen, in een *.002 bestand zetten, enz.? Ben niet echt into PHP wat dat betreft, maar moet er toch inzitten lijkt me?
Anders kan je kijken hoever je nu komt, en een variabele gebruiken voor welk blok. Je zal dan naar regel 10000*$blok moeten gaan voor de while lus, en 10000*($blok+1) erbij moeten zetten in de while. Kan je stukjes doen, en verder gaan waar je was.

Hoop dat je er iets aan hebt. Suc6 in ieder geval!

[ Voor 12% gewijzigd door MBV op 12-03-2003 20:44 ]

[ACM]

Als je het al in je php hebt, waarom insert je het dan niet gelijk in mysql?

wat je kan doen is dan nog in mysql (of ergens anders) waar je gebleven was in de file (ftell) en na een "crash" daarnaartoe terugkeren (fseek).
Wellicht is de reden dat je webserver het afkapt, ondanks de lange timeout, dat je browser het na 5 minuten opgeeft ofzo.
Probeer na elke 100 regels eens een letterlijke . te echoen ofzo en dan ook gelijk een flush() erachteraan doen, daardoor zal je browser iig het wachten niet opgeven, hoelang het ook duurt omdat ie al data krijgt.

[beetle71]

Het probleem dat je webserver het script afbreekt komt omdat je te lang geen output hebt. In princpipe moet het script blijven lopen zolang het output genereert.
dus achter regel 28 nog even iets van: echo ".";
(heb je meteen ook een soort van progress

[MBV]

Progress? Ik denk dat je dan een gecrashte browser krijgt, met 100.000 regels oid. Is ook niet bevorderlijk voor de snelheid lijkt me. Misschien een counter laten meelopen, en elke 10e of 100e regel een punt. Maar ik zou toch eerst eens proberen met progress.

edit:
sorry, beetje offtopic. Laat even weten wat je met bovenstaande toevoeging krijgt, anders zal je een telmechanisme moeten maken dat hij in blokken werkt, zoals ik eerder voorstelde.
En als reactie op het bericht hieronder: ik maakte me niet zo'n zorgen over de kb's, maar over het aantal keer dat er communicatie wordt gestart. Maar nu ik erover nadenk, denk ik dat het je browser niet echt veel uitmaakt

* MBV houdt het de volgende keer korter

[ Voor 48% gewijzigd door MBV op 12-03-2003 21:50 ]

[beetle71]

In princpipe moet het script blijven lopen zolang het output genereert.
dus achter regel 28 nog even iets van: echo ".";

200.000 record, elke regel EEN "." = 200Kb
Mag niet hopen dat je browser bij 200K al crashed.....

[Verwijderd]

andere oplossing dan: laat het script bijvoorbeeld 1000 records verwerken, en daarna alle verwerkte records verwijderen uit het bestand. Vervolgens aan het einde van het script weer zichzelf aanroepen.
Wat betreft het hele browserverhaal: dat is niet aan de orde. Zelfs al stopt je browser er na 30 seconden mee, php blijft gewoon doordraaien op de server. (is maar lastig zat met een foutje zoals infinite loop)

[Verwijderd]

Grappig,
Nu ik al die $line[24] enz heb vervangen door die substr werkt het wel, én binnen de 5 minuten. Om precies te zijn: 4 min. 20 sec.

Ik heb ook nog even getest met die counter, nou ja, weet ik hoe dat moet. Gewoon een var $count aangemaakt en in de while een +1 gezet en een \n, maar geen flush ofzo, dus alles kwam onder elkaar te staan. Werkt wel!!! Het aantal records is: 171036
Ik heb dus eerder overdreven Het zijn er minder dan ik dacht.
Browser crasht niet met die counter. Is er wel weer uit om de snelheid optimaal e benutten.

Ik wil wel graag iets meer info over wat ACM schreef:

(ftell) en na een "crash" daarnaartoe terugkeren (fseek)

Klein voorbeeldje??? Danke...

Probeer na elke 100 regels eens een letterlijke . te echoen ofzo en dan ook gelijk een flush() erachteraan doen

Hoe krijg ik het voor elkaar dat ie alleen maar na een 100ste regel iets echoed? FOR gebruiken? Hoe dan, heb daar nooit iets van begrepen. En met die flush verkom ik waarschijnlijk dat er 176000 regels onder elkaar in de browser staan? Hoe werkt die dan?

Heel erg bedankt voor jullie hulp, ben blij dat het werkt nu. Bovenstaande vragen zijn niet heel erg belangrijk, maar klinkt wel interessant en wil het dus wel graag weten.

Nogmaals bedankt.
Groet

[TheRebell]

zeg voor zover ik nu ff 1..2..3.. uit het hoofd kan halen kun je (mits je phpMyAdmin gebruikt) chars opgeven waarop ie de input gaat scheiden. Zou stuk makkelijker gaan dan...

offtopic:
mocht iemand het toch al gezegd hebben en ik weer eens te snel lezen dan bij deze mijn excusses

[Verwijderd]

Ik heb het met phpmyadmin niet voor elkaar kunnen krijgen helaas. Dat leek me ook wel erg makkelijk.

En ik vergat nog iets:
De bedragen in het bestand bestaan uit 8 tekens, maar kan bijv dus zo staan: 00002195
Voorloopnullen en zonder komma ertussen. Met wordwrap heb ik de komma geplaatst, maar ik moet ook van die voorloopnullen af. Iemand een idee? Ik heb ltrim eruit gecomment, op die manier werkte het niet.

edit:

Dit laatste is inmiddels gelukt met:
$prijs = ltrim($prijs, "\0x00..\0x1F");

Ik word steeds blijer!

[ Voor 13% gewijzigd door Verwijderd op 12-03-2003 23:25 ]

[ACM]

Mja, een voorbeeld?

zoiets:

PHP:

while(de loop)
{
  // haal alle waarden uit de huidige regel
  $huidige_file_positie = ftell($fp);
   mysql_query("update file_pointer set value=$huidige_file_positie where file = '$filenaam');
  //
}

En voor je "recover code"

PHP:

$res = mysql_query("select value from file_pointer where file = '$filenaam'");
if(mysql_num_rows($res) > 0)
{
   // haal waarde op, doe een fseek na de fopen
}
else
{
   // sla nieuwe entry op in de db, met als waarde 0/-1 en open doe geen fseek
}

[beetle71]

PHP:

$titel = $isbn . ";" . $titel . ";" . $auteur . ";" . $prijs . ";"
           . $boekcode . ";" . $uitgnr . ";" . $versweek . ";"

.

Als je het net even anders doet, en er een csv van maakt zou je het zo met phpMyAdmin in moeten kunnen lezen.

(aangezien het in jou geval geen gekke leestekens in de teksten voorkomen hoef je daar geen rekening mee te houden )

(ik gebruik $output ipv $titel, leek me iets logischer)
begin met $output='';
en voeg er per regel dit aan toe

PHP:

$output.='"'.$isbn.'";"'.$titel.'";"'.   ...enz....   .$versweek.'"'."\r\n";

(Let vooral op de positie/volgorde van de " en ', vooral ook aan het einde! )
en schrijf het bestand pas naar disk als het helemaal klaar is.
Het bestand dat je zo gecreerd hebt zou je in phpmyadmin (wel eerst de tabeldefinitie maken ) zo in moeten kunnen lezen.

[ Voor 14% gewijzigd door beetle71 op 13-03-2003 10:11 ]

[Verwijderd]

Hey,

Het werkt!!!

Ik breng output naar het scherm en het script blijft lopen. Het duurt langer dan 5 minuten, maar dat maakt me niet uit.
De output zie ik pas op het scherm wanneer het script helemaal is uitgelopen. Als ik thuis ben post ik het script wel even.

Bedankt allemaal.

Groet,
Hans

P.s. Dat over csv. Die structuur heeft mijn txt ook ongeveer en is te importeren met phpMyAdmin.