DateFormat probleem bij importeren van extern bestand naar een tabel

[AMBERTJE]

Hallo


Kan er iemand mij helpen aub? Ben ondertussen behoorlijk wanhopig geworden.
Ik werk met ACCESS 2003 en probeer een extern bestand in een tabel te importeren.
Voor ik de Import doe moet ik dit bestand omzetten naar de extensie .txt omdat dit oorspronkelijk uit SAP komt en anders niet herkend wordt.

Ik doe dit met een import specificatie en de 2 laatste velden zijn datum en tijd (die ik als Date/Time heb gedefiniëerd), ze staan in het rood aangeduid in onderstaand voorbeeld.
Wat ik ook doe het wil niet lukken en na de import zijn de velden datum en tijd leeg en heb ik een Import Error Conversion Type voor datum en tijd.
Ook met InputMask lukt dit niet.

Heb ook geprobeerd via een omweg, het bestand in exel gezet en daar geprobeerd de date format juist te krijgen maar ook dit lukt niet.


4200146101;B910;0000019723;5010480310;0000000000000000;000000000104859696;x-dock palet cheque;+;1,000;ST;BATCHSHP;20100301;000729
4200150302;B910;0000001545;5010477364;0000000000000000;000000000100006750;PLASTIC EPS;+;3,000;ST;BATCHSHP;20100301;004639
4200150303;B910;0000088577;5010477365;0000000000000000;000000000100006681;LARGE CRATE;+;1.626,000;ST;BATCHSHP;20100301;005324
4200150304;B910;0000088577;5010477366;0000000000000000;000000000100006681;LARGE CRATE;+;70,000;ST;BATCHSHP;20100301;005329

Ben ten einde raad en zie geen uitweg meer met mijn beperkte kennis.

Groetjes,
Amberke

 

[Tardis]

Ms Access zal deze gegevens nooit als datum/tijd kunnen herkennen, vandaar je probleem.
Maak een extra tabel aan.
Importeer daarin je gegevens, datumgegeven als tekst, tijdgegeven als tekst.
Maak daarna een query.
Gebruik daarin functies zoals Format, CDate en/of DateSerial om datum- en tijdgegevens te converteren naar een format dat MS Acces herkent als een datum/tijd.
Zorg er daarbij voor dat wat je nu apart als datum en tijd krijgt aangeleverd wordt geconverteerd naar 1 samengestelde waarde datum/tijd.

Bouw tenslotte je query om naar een toevoegquery om de gegevens uit de extra tabel toe te voegen aan de tabel waar je ze in wilt zetten.

Verwijder tenslotte de gegevens uit de extra tabel.

Tardis

 

[AMBERTJE]

Ms Access zal deze gegevens nooit als datum/tijd kunnen herkennen, vandaar je probleem.
Maak een extra tabel aan.
Importeer daarin je gegevens, datumgegeven als tekst, tijdgegeven als tekst.
Maak daarna een query.
Gebruik daarin functies zoals Format, CDate en/of DateSerial om datum- en tijdgegevens te converteren naar een format dat MS Acces herkent als een datum/tijd.
Zorg er daarbij voor dat wat je nu apart als datum en tijd krijgt aangeleverd wordt geconverteerd naar 1 samengestelde waarde datum/tijd.

Bouw tenslotte je query om naar een toevoegquery om de gegevens uit de extra tabel toe te voegen aan de tabel waar je ze in wilt zetten.

Verwijder tenslotte de gegevens uit de extra tabel.

Tardis

Dank u wel voor de reactie Tardis,

Wat bedoel je met een extra tabel? Ik heb nu een tabel waar alle gegevens inkomen(uitgezonderd de datum en tijd natuurlijk), moet ik die extra tabel dan terug manueel aanmaken?
Kun je mij ook een voorbeeldje van zo'n Query tonen aub?
Ik ben wel wat vertrouwd met Access maar eigenlijk is mijn kennis van Query's niet zo groot (ik doe dit meestal via de wizard).

Groetjes,
Amberke

 

[OctaFish]

Tardis heeft niet helemaal gelijk, want Access kan wel degelijk onderscheid maken tussen de verschillende gegevens die je aanlevert. De datum kan dan ook prima in één keer als datum in een tabel worden gezet. De tijden vertalen is echter een beetje lastiger.

Je hebt al aangegeven dat je een Import specificatie gebruikt, en dat is inderdaad de juiste manier van werken! :thumb: Alleen heb je de specificatie vermoedelijk niet goed ingesteld, met als resultaat dat je datums verkeerd worden vertaald. In bijgaand plaatje zie je de juiste instellingen om de datum goed te importeren.

Wat betreft de tijden, zul je met een Bijwerkquery moeten gaan werken. Persoonlijk zou ik het importbestand dus zodanig instellen, dat je de tijden als Tekst inleest, waarna je met een Bijwerkquery dan de Tijd omzet van tekst naar Tijd. Ik zou daarvoor dan een extra veld toevoegen aan de importtabel, van het type Datum/Tijd. Op die manier houd je alles in één tabel, en dat werkt een stuk eenvoudiger.
Je kunt alles ook nog wel automatiseren, door een macro te maken die de import regelt met de eerder gemaakte importspecificatie, vervolgens de tabel Import aanpast met het extra tijdveld, en als laatste de bijwerkquery die de tijd omzet.

 

[AMBERTJE]

Tardis heeft niet helemaal gelijk, want Access kan wel degelijk onderscheid maken tussen de verschillende gegevens die je aanlevert. De datum kan dan ook prima in één keer als datum in een tabel worden gezet. De tijden vertalen is echter een beetje lastiger.

Je hebt al aangegeven dat je een Import specificatie gebruikt, en dat is inderdaad de juiste manier van werken! :thumb: Alleen heb je de specificatie vermoedelijk niet goed ingesteld, met als resultaat dat je datums verkeerd worden vertaald. In bijgaand plaatje zie je de juiste instellingen om de datum goed te importeren.

Wat betreft de tijden, zul je met een Bijwerkquery moeten gaan werken. Persoonlijk zou ik het importbestand dus zodanig instellen, dat je de tijden als Tekst inleest, waarna je met een Bijwerkquery dan de Tijd omzet van tekst naar Tijd. Ik zou daarvoor dan een extra veld toevoegen aan de importtabel, van het type Datum/Tijd. Op die manier houd je alles in één tabel, en dat werkt een stuk eenvoudiger.
Je kunt alles ook nog wel automatiseren, door een macro te maken die de import regelt met de eerder gemaakte importspecificatie, vervolgens de tabel Import aanpast met het extra tijdveld, en als laatste de bijwerkquery die de tijd omzet.

Hallo Octafish,

Je hebt gelijk, ik kan mijn datums nu in de kolom zien staan en dan ook nog als datum.
Zoals je al aangaf zijn er nog problemen met de tijden, als ik uw voorbeeld neem leest access ze zelfs nog niet als text in.
Ik heb dan maar voor het Time veld in de import specificatie double gebruikt.
Het probleem is dan nog hoe ik dit omzet naar tijd (ik heb geen ervaring met macro's in Access buiten de interne macro's om query's te openen enz.).
Er is nog een ander detail waar ik rekening mee zal moeten houden, als de tijd 00:12:23 is dan komt dit in de kolom als 1223 te staan en de nullen zijn weg.

Kun je me hiervoor een beetje gedetailleerder uitleg geven aub?
Ben al supergelukkig dat de datum gelukt is, duizendmaal bedankt.

Groetjes,
Amberke

 

[OctaFish]

Het plaatje dat ik had bijgevoegd was op de een of andere manier van een oudere versie. Je laatste veld mag namelijk geen datum/tijd veld zijn, omdat het toch niet geconverteerd wordt. In de specificatie moet je het dus als Tekst definieren. Dat is belangrijk, omdat je anders straks de bijwerking niet kan maken.
Na de import moet de tijd dus als tekst in de import tabel staan, met zes cijfers.
Vervolgens maak je een macro met de volgende SQL:

ALTER TABLE Import ADD COLUMN NieuweTijd Date

Deze query voegt een extra tijdveld toe.

Met deze bijwerkquery wordt vervolgens het tekstvak als tijd toevoegd aan het nieuwe veld:

UPDATE Import SET Import.NieuweTijd = TimeSerial(Left([Veld13],2),Mid([veld13],3,2),Right([veld13],2));

En dan ben je er volgens mij wel.

 

[AMBERTJE]

Het plaatje dat ik had bijgevoegd was op de een of andere manier van een oudere versie. Je laatste veld mag namelijk geen datum/tijd veld zijn, omdat het toch niet geconverteerd wordt. In de specificatie moet je het dus als Tekst definieren. Dat is belangrijk, omdat je anders straks de bijwerking niet kan maken.
Na de import moet de tijd dus als tekst in de import tabel staan, met zes cijfers.
Vervolgens maak je een macro met de volgende SQL:

ALTER TABLE Import ADD COLUMN NieuweTijd Date

Deze query voegt een extra tijdveld toe.

Met deze bijwerkquery wordt vervolgens het tekstvak als tijd toevoegd aan het nieuwe veld:

UPDATE Import SET Import.NieuweTijd = TimeSerial(Left([Veld13],2),Mid([veld13],3,2),Right([veld13],2));

En dan ben je er volgens mij wel.

Geweldig, nu staat de tijd er inderdaad met 6 cijfers.
Alleen weet ik niet hoe ik die macro moet klaarmaken. Doe ik dit in een module of doe ik dit via macro's? Als het via de ingebouwde macro's moet welke actie moet ik dan gebruiken?

Sorry voor mijn domme vragen maar op macro gebied ben ik een beginneling.
Alvast bedankt voor de hulp en uw snelle reactie:thumb:
Ik apprecieer dit ten zeerste.

Groetjes,
Amber

 

[OctaFish]

Hoi Amber,

Vragen zijn zelden dom (al kan ik er wel een paar bedenken ), antwoorden toch wat vaker... Niet deze overigens
Ik ga er vanuit, dat je de queries gemaakt hebt gekregen? Ik had het in de vorige post inderdaad over macro's die je moest maken. Ik bedoelde eigenlijk Queries.

Als de queries klaar zijn kun je een nieuwe macro maken, die de verschillende handelingen gaat uitvoeren. Als eerste zoek je dan de actie die de importfile binnen gaat halen. Zo te zien werk je met 2007, en daar blijf ik voorlopig ver bij uit de buurt, dus de juiste commando's kan ik je zo niet geven.
Maar in de plaatjes vind je de 2003 equivalenten, dus hopelijk kom je er dan wel uit.
Deze macro draai je maar één keer, omdat bij een tweede keer draaien hij vastloopt op de tweede query...

 

[AMBERTJE]

Hoi Amber,

Vragen zijn zelden dom (al kan ik er wel een paar bedenken ), antwoorden toch wat vaker... Niet deze overigens
Ik ga er vanuit, dat je de queries gemaakt hebt gekregen? Ik had het in de vorige post inderdaad over macro's die je moest maken. Ik bedoelde eigenlijk Queries.

Als de queries klaar zijn kun je een nieuwe macro maken, die de verschillende handelingen gaat uitvoeren. Als eerste zoek je dan de actie die de importfile binnen gaat halen. Zo te zien werk je met 2007, en daar blijf ik voorlopig ver bij uit de buurt, dus de juiste commando's kan ik je zo niet geven.
Maar in de plaatjes vind je de 2003 equivalenten, dus hopelijk kom je er dan wel uit.
Deze macro draai je maar één keer, omdat bij een tweede keer draaien hij vastloopt op de tweede query...

Hi Octafish
Alles is geïmplementeerd en het werkt bijna.
Enkel als ik de macro uitvoer dan moet ik een parameter invullen voor de nieuweTijd als de UpdateQuery wordt aangesproken. Helaas weet ik niet wat Access daar van mij verwacht.

Ik werk met de 2003 versie
Groetjes,
Amberke

 

[OctaFish]

Hai Amber,
Ik heb 'm ook gemaakt in 2003, dus da's alleen maar mooi! Als er een parameter wordt gevraagd, dan komt dat waarschijnlijk doordat de veldnaam in de tabel niet overeenkomt met de naam in de query. Dat kun je makkelijk zelf checken in de query. Ik denk dat de Update query het probleem geeft? De Alter Table query maakt het veld namelijk slechts aan, dus dan kan-ie ook nog niet bestaan...

Post anders de SQL die in de query gebruikt wordt: <Beeld>,<SQL>, en dan zie je een SELECT ..... tekst geselecteerd staan. Die mag je kopieëren en hier plakken...

 

[AMBERTJE]

Hai Amber,
Ik heb 'm ook gemaakt in 2003, dus da's alleen maar mooi! Als er een parameter wordt gevraagd, dan komt dat waarschijnlijk doordat de veldnaam in de tabel niet overeenkomt met de naam in de query. Dat kun je makkelijk zelf checken in de query. Ik denk dat de Update query het probleem geeft? De Alter Table query maakt het veld namelijk slechts aan, dus dan kan-ie ook nog niet bestaan...

Post anders de SQL die in de query gebruikt wordt: <Beeld>,<SQL>, en dan zie je een SELECT ..... tekst geselecteerd staan. Die mag je kopieëren en hier plakken...

OctaFish,
Hier de informatie van de SQL statements en een voorbeeld van de velden uit mijn tabel:
ALTER TABLE Tbl_ISLCA_Import ADD NieuweTijd Date


UPDATE Tbl_ISLCA_Import SET NieuweTijd = TimeSerial(Left([Time],2),Mid([Time],3,2),Right([Time],2));


Voorbeeld van de tabelvelden:
PO | DC | Vendor| Sap_Doc_nr | IDOC_nr | Referentie | Omschrijving |
4800625545 | B910 | 7280 | 5010491407 | 0 | 100902991 | POMTERRE500GR |

Adjust_Teken | Quantity | Eenheid | User | Datum | Time
+ | 144 | ST | BATCHSHP | 2/03/2010 | 004013

Moest ik misschien nog een extra tabel maken want dat heb ik niet gedaan?
Kan ik misschien nog een extra vraagje stellen aub? Bij referentie staat een getal dat 9 karakters groot is en moet ik definiëren als number, kan ik op een simpele manier de 2 éérste getallen (10) laten vallen?

Groetjes en hopelijk neem ik niet te veel van je tijd in beslag.
Ambertje

 

[Tardis]

Wijzig dit

UPDATE Tbl_ISLCA_Import SET NieuweTijd = TimeSerial(Left([Time],2),Mid([Time],3,2),Right([Time],2));

in

UPDATE Tbl_ISLCA_Import SET Time = TimeSerial(Left([Time],2),Mid([Time],3,2),Right([Time],2));

Daarnaast raad ik je aan om in ieder geval je kolommen Time en User een andere naam te geven.
Time en user zijn namelijk gereserveerde woorden in Access, gebruik van gereserveerde namen kan tot onverwachte situaties leiden.
Daarmaast zou ik ook de kolomnaam Datum wijzigen, Date is ook een gereserveerd woord en ik dacht (kan me vergissen) dat dat voor Datum ook geldt.

Tenslotte zou ik datum en tijd in 1 kolom samenvoegen.
Er is namelijk geen enkele reden in jouw geval om tijd apart op te slaan.
Samenvoegen maakt het maken van eventuele overzichten een stuk gemakkelijker.

Tardis

 

[OctaFish]

Datum is geen gereserveerd woord, maar Time wel, dus dat is waarschijnlijk het probleem met de Update query. Als je die veldnaam wijzigt, moet het verder werken. Het voorstel van Tardis zou het probleem dus alleen maar groter maken. User is volgens mij prima te gebruiken. Doe ik zelf namelijk ook, en ik heb er nooit problemen mee gehad.

Maak bijvoorbeeld van de veldnamen:
Adjust_Teken | Quantity | Eenheid | User | Datum | Tijd

 

[Tardis]

Het voorstel van Tardis zou het probleem dus alleen maar groter maken.

Want?
Heb je mijn reactie wel gelezen/begrepen?
Lijkt mij niet.

Tardis

 

[OctaFish]

@Tardis:
Als we weer gaan mekkeren, dan ben ik gauw klaar met jou, want daar heb ik helemaal geen trek in:

Wijzig dit
Code:
UPDATE Tbl_ISLCA_Import SET NieuweTijd = TimeSerial(Left([Time],2),Mid([Time],3,2),Right([Time],2));in

Code:
UPDATE Tbl_ISLCA_Import SET Time = TimeSerial(Left([Time],2),Mid([Time],3,2),Right([Time],2))

;

is nou niet bepaald het beste voorstel....

 

[AMBERTJE]

@Tardis:
Als we weer gaan mekkeren, dan ben ik gauw klaar met jou, want daar heb ik helemaal geen trek in:

;

is nou niet bepaald het beste voorstel....

Goedemiddag,

Ik heb mijn Veld in de tabel nu Tijdstip genoemd en de Query aangepast naar dit:

UPDATE Tbl_ISLCA_Import SET NieuweTijd = TimeSerial(Left([Tijdstip],2),Mid([Tijdstip],3,2),Right([Tijdstip],2))

Helaas krijg ik nog steeds een foutmelding:
Cannot update 'NieuweTijd', field not updateable.

Wat doe ik toch verkeerd? In de ImportSpecificaties heb ik het veld Tijdstip als Text staan en ik zie mijn tijden nu na het laten lopen van de Macro wel staan maar dat is het dan ook.

Verlies aub je geduld niet met mij, ik ben zo dankbaar voor alle hulp.
Groetjes,
Ambertje

 

[OctaFish]

Hé Amber,
Mijn geduld met jou is nog lang niet op Ik krijg alleen een punthoofd van mr. T. :evil:
Heb je al in de import tabel gekeken of het veld [NieuweTijd] goed is aangemaakt? Ik kan er anders wel een voorbeeldje bij maken, dat werkt misschien wat makkelijker.

 

[AMBERTJE]

Hé Amber,
Mijn geduld met jou is nog lang niet op Ik krijg alleen een punthoofd van mr. T. :evil:
Heb je al in de import tabel gekeken of het veld [NieuweTijd] goed is aangemaakt? Ik kan er anders wel een voorbeeldje bij maken, dat werkt misschien wat makkelijker.

Dank u wel Octa,

heb de fout ontdekt, in de macro had ik de query's in de verkeerde volgorde gezet en nu werkt het wel. Schitterend resultaat. Kun je in die macro ook zetten dat het oude tijdveld deleted moet worden?

Bedankt voor uw hulp, ik ben zo blij als een klein kind. :thumb::thumb:
Groetjes,
Ambertje ('k ga nu naar school)

 

[OctaFish]

Er komt dan nog een query bij met de volgende code:

ALTER TABLE Import DROP COLUMN Tijdstip

Deze query roep je dan aan in een nieuwe opdrachtregel in de macro.
Je hebt verder nog niet uitgelegd wat je met die import tabel gaat doen. Als ik zelf regelmatig een (zelfde) bestand moet importeren, dan automatiseer ik dat proces doorgaans van begin tot eind. Bij zo'n productieproces wil je meestal dat de te importeren records worden toegevoegd aan een tabel in je db. De Import tabel is dan dus een tijdelijke tabel, die steeds opnieuw wordt aangemaakt/bewerkt/toegevoegd aan de hoofdtabel. In zo'n werkstroom hoef je een veld niet te verwijderen, maar verwijder je de hele tabel aan het begin of eind van het proces. Ook heb je het Tijdstip veld natuurlijk weer nodig bij de volgende import.

 

[AMBERTJE]

Er komt dan nog een query bij met de volgende code:

ALTER TABLE Import DROP COLUMN Tijdstip

Deze query roep je dan aan in een nieuwe opdrachtregel in de macro.
Je hebt verder nog niet uitgelegd wat je met die import tabel gaat doen. Als ik zelf regelmatig een (zelfde) bestand moet importeren, dan automatiseer ik dat proces doorgaans van begin tot eind. Bij zo'n productieproces wil je meestal dat de te importeren records worden toegevoegd aan een tabel in je db. De Import tabel is dan dus een tijdelijke tabel, die steeds opnieuw wordt aangemaakt/bewerkt/toegevoegd aan de hoofdtabel. In zo'n werkstroom hoef je een veld niet te verwijderen, maar verwijder je de hele tabel aan het begin of eind van het proces. Ook heb je het Tijdstip veld natuurlijk weer nodig bij de volgende import.

Wat ik met die importtabel wil doen is niet zo simpel.
De bedoeling is dat ik die kan koppelen aan een shakelbord in een bestaande database die men ooit heeft ontwikkeld (een zeer complexe database).
Op dat schakelbord staat een knop die al functioneel is en bij het aanklikken ervan gegevens toont uit een andere importtabel.
Het linken van mijn gegevens aan de bestaande is al een uitdaging op zich omdat in die andere tabel ook het veld referentie aanwezig is.
Moet ik voor vragen hieromtrent misschien een andere thread aanmaken?

Nu ben ik al eens gaan snuffelen in die database om een beetje te begrijpen hoe men dat in elkaar heeft gestoken. De persoon die die database heeft ontwikkeld werkt nu niet meer in de firma dus ben ik op jullie hulp aangewezen omdat er niemand in ons bedrijf een goede kennis van access heeft.

Mijn kennis is ook beperkt maar mijn doorzettingsvermogen en leergierigheid is groot.

Wat je in uw antwoord zegt over het volledig automatiseren van zo’n process heb ik al in een kleinschalige database verwezenlijkt dus dat is in een klein project niet zo moeilijk.

Hartelijk bedankt voor al jullie hulp. Ik wil nog even vermelden dat ik het ongelooflijk vind dat jullie ons onbaatzuchtig helpen, wat zouden wij leken doen zonder de hulp van een forum en de mensen erachter.