[EXCEL 2007] Decimale punt wordt niet herkend bij import CSV

[Pindakaas]

Beste mede tweakers,

Ik ben bezig om een excel sheet in elkaar te flansen waarmee ik thuis mijn administratie kan doen. Deze excel sheet importeert d.m.v. een macro een CSV bestandje die ik kan downloaden van de site van mijn bank.

Wanneer deze CSV file er in staat worden alle bedragen die gecrediteerd of gedibiteerd worden weergeven als bijv: "45.25" (zonder aanhalingstekens)

Nu worden deze getallen in excel niet gezien als getallen maar als tekst wat het bijzonder lastig maakt. Ik wil namelijk van deze getallen een draaitabel maken maar dit werkt niet zonder getallen natuurlijk.

-zodra ik de getallen opmaak als bijv. een valuta gebeurt er helemaal niks.

-zodra ik de hele kolom selecteer. Alle punten vervang door komma's en als valuta opmaak gaat het goed.

-zodra ik hetzelfde doe, opneem als macro en de macro vervolgens uitvoer maakt hij er wel komma's van maar nog steeds wordt het niet als een bedrag gezien maar als tekst. (zodra ik dan in een cel ga bewerken en zonder iets aan te passen op enter druk wordt het opeens wel als getal gezien.)

Ik probeer natuurlijk dit process ook geautomatiseerd te laten gaan d.m.v. een macro maar wil niet echt lukken.

Wat doe ik verkeerd?

zal z.s.m. even een voorbeeld bestandje uploaden
[EDIT] hier een voorbeeldje: [doe maar niet

[ Voor 5% gewijzigd door Lustucru op 29-09-2014 20:33 . Reden: link naar bestand met macro's weer weggehaald ]

[Driek]

Mooie hergebruikte sheet, ik heb ooit als start dezelfde gebruikt, maar nooit problemen gehad.

Gebruik je ING als invoer? Ik gebruik overigens de 2010 versie van Excel

[Pindakaas]

Klopt, heb ik hier van het forum afgehaald een tijdje geleden en de nodige aanpassingen gedaan omdat ik geen ING rekening gebruik maar een RABO rekening. (andere indeling van CSV bestandje enzo)

overigens gebruik ik nu om de sheet aan te passen versie 2007 maar thuis heb ik 2013.

[Driek]

misschien in je opgenomen makro ook opnemen dat je rechtermuisknop op de gehele kolom doet en vervolgens getalnotatie selecteert?

[Pindakaas]

Heb ik ook al geprobeerd maar veranderd jammer genoeg niks

[Lustucru]

Ik heb de link naar je bestand weggehaald, om drie redenen:
1: Ik vind het niet zo'n geweldig idee om uitvoerbare bestanden te linken via tweakers. Een ongelukje zit in een klein hoekje en een excelbestand uit een willekeurige bron openen is i.h.a. een slecht plan.
2: Het is in dit geval ook absoluut niet nodig. Je kunt gewoon een deel van de relevante code posten (tussen [code][/code] tags. Dan is het topic over een tijdje ook nog interessant en vindbaar
3: We zijn geen helpdesk. Het uploaden van een bestand lokt ook weer uit dat iemand wat fixt en jij wel een bruikbaar bestand hebt, maar nog steeds geen notie waar je probleem nu zat.

Tot slot je topcititel aangepast. Alleen [Excel 2007] is zo weinig informatief

[Pindakaas]

Excuses voor het plaatsen van het bestandje dan. Het was zeker niet bedoeld om opgelost te worden voor mij of om slecht te doen. (+ ik heb wel meer topics hier voorbij zien komen over een excel vraag waar elke keer gewoon werd verwezen naar een excel bestandje)

Ik wil juist zelf weten waar het precies zit omdat ik dit bestandje natuurlijk veel ga gebruiken en ik zelf moet weten hoe ik dit in de toekomst moet aanpassen.

Hierbij dus de code waar het om gaat. In principe werkt deze code dus prima want alle punten worden vervangen door komma's maar excel ziet de inhoud van de cel vervolgens nog steeds niet als een getal maar als een tekst:

code:

Private Sub puntkomma()

    Sheets("Data").Select
    Columns("H:H").Select
    Selection.NumberFormat = "$ #,##0.00"
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets("Zoekwaardes").Select
    
End Sub

[ Voor 6% gewijzigd door Pindakaas op 30-09-2014 09:32 ]

[Coffeemonster]

Als mij dit een keer overkomt, dan zet ik het getal 1 in een lege cel, kopieer die cel, selecteer de cellen met de verkeerd geïnterpreteerde waarden en doe dan Plakken speciaal - vermenigvuldigen. Hierdoor worden de waarden vermenigvuldigd met 1 (blijven dus hetzelfde), en worden de waarden erna als getal gezien. Meestal doe ik dit even met de hand, maar dit kan je natuurlijk ook doen met een macro.

[Pindakaas]

Klinkt als een goede oplossing maar helaas heeft die hetzelfde probleem als mijn eigen "oplossing".

als ik het gewoon handmatig doe dan gaat het allemaal prima. Zodra ik dezelfde handeling in een macro zet gebeurt er helemaal niks.

[Angeloonie]

Ik heb hetzelfde probleem gehad met een CSV en na enorm veel puzzelen tot de conclusie gekomen dat het niet automatisch mogelijk is. Via macro's komen er bepaalde formatting-opties niet over op getallen.

Mijn oplossing was de ontwikkelaar lief aan kijken en te zorgen dat die "." een "," werd.

[Pindakaas]

Ik heb ook gekeken of die . niet vervangen kan worden door een , in het CSV bestand zelf. Maar ik dacht dat dit niet ging werken doordat het CSV bestand is gescheiden door middel van Komma's.

alle cijfers achter de komma zal dan dus als aparte kolom tevoorschijn komen lijkt mij

[ksmedts]

Eerst alle komma's vervangen door puntkomma's en dan je decimaal teken punt vervangen voor een komma...

[Pindakaas]

Dan ga ik even proberen.

Even zoeken hoe je het makkelijkste een CSV kan aanpassen. (En dan ook het liefste automatisch natuurlijk want vrouwlief moet ook met de Excelsheet om kunnen gaan.)

[Coffeemonster]

Helpt het niet om de landinstellingen van Windows aan te passen, zodat de punt als decimaalscheidingsteken wordt gebruikt (in plaats van de komma) en de komma als lijstscheidingsteken (in plaats van de puntkomma)?

[Lustucru]

JeroenF schreef op dinsdag 30 september 2014 @ 09:30:

Hierbij dus de code waar het om gaat. In principe werkt deze code dus prima want alle punten worden vervangen door komma's maar excel ziet de inhoud van de cel vervolgens nog steeds niet als een getal maar als een tekst:

De code waarmee je het csv bestand importeert is veel interessanter. In tegenstelling wat hier beweerd wordt verwerkt excel de parameter TextFileDecimalSeparator = "." correct. Als alternatief kun je een gegevensverbinding leggen met de csv-file; het volstaat dan om simpelweg de gegevens te verversen.

offtopic:
"ik heb wel meer hier voorbij zien komen over een excel vraag waar elke keer gewoon werd verwezen naar een excel bestandje". Dat is helaas helemaal waar en het neemt de laatste tijd sterk toe. Een ontwikkeling die ik niet wil aanmoedigen. Bestand bijvoegen prima, als het een meerwaarde heeft. Uitvoerbare bestanden met ingebouwde code zijn echter ook nog een beveiligingsrisico

[ Voor 23% gewijzigd door Lustucru op 30-09-2014 22:52 ]

[Pindakaas]

Bedoel je met gegevensverbinding dat ik gewoon de data ophaal uit de CSV file zonder het te importeren?
Ik zal is kijken of dat gaat lukken maar ik denk dat ik daar niet excel-expert genoeg voor ben haha.

EDIT: even geprobeerd maar lijkt me toch niet handig aangezien de CSV file die geimporteerd moet worden elke keer anders heet aangezien hij van de bank-website gedownload worden. Moet ik hem eerst weer gaan hernoemen enz. enz.

Hier dan in ieder geval de code voor het importeren van de CSV:

code:

'import text file to sheet
Public Sub ImportCSV()
    Dim vPath As Variant
    
    'Show the file open dialog to allow user to select a CSV file
    vPath = Application.GetOpenFilename("CSV (Comma Delimited) (*.csv),*.csv" _
    , 1, "Select a file", , False)
    
    'Exit macro if no file selected
    If vPath = False Then Exit Sub
    
    'Clear data sheet
    ThisWorkbook.Sheets("Data").Cells.Clear
    
    'Import file
    ThisWorkbook.Sheets("Data").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & vPath, Destination:=Range("A1"))
        .Name = "Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 5, 1, 1, 1, 1, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    'Kolommen sorteren op de goede volgorde
    Columns("C").Cut
    Columns("A").Insert Shift:=xlToRight
    Columns("K").Cut
    Columns("B").Insert Shift:=xlToRight
    Columns("G").Cut
    Columns("D").Insert Shift:=xlToRight
    Columns("J").Cut
    Columns("E").Insert Shift:=xlToRight
    Columns("G").Cut
    Columns("F").Insert Shift:=xlToRight
    Columns("H").Cut
    Columns("G").Insert Shift:=xlToRight
    Columns("I").Cut
    Columns("H").Insert Shift:=xlToRight
    Columns("L").Cut
    Columns("I").Insert Shift:=xlToRight
    
    'close connection
    ThisWorkbook.Connections(1).Delete
    
    'Enable autofilter
    ThisWorkbook.Sheets("Data").EnableAutoFilter = True
    ThisWorkbook.Sheets("Data").Cells.AutoFilter
    
    'Selecteer originele sheet
    ThisWorkbook.Sheets("Zoekwaardes").Select
End Sub

Een kennis van me zei dat ik misschien beter het vervangen van de punt door de komma gelijk na het importeren kan doen i.p.v. na het categoriseren andere veranderingen in de sheet. Ga ik vanmiddag ook even proberen
EDIT: Blijkt niet te werken

[ Voor 4% gewijzigd door Pindakaas op 01-10-2014 12:10 ]

[Pindakaas]

Coffeemonster schreef op dinsdag 30 september 2014 @ 17:03:
Helpt het niet om de landinstellingen van Windows aan te passen, zodat de punt als decimaalscheidingsteken wordt gebruikt (in plaats van de komma) en de komma als lijstscheidingsteken (in plaats van de puntkomma)?

Dit blijkt wel te werken! Helaas is het dan wel problematisch dat mijn hele computer dan gelijk ingesteld staat.

[ksmedts]

In windows 7 kun je meerdere profielen aanmaken en dan makkelijk switchen via een quickmenu langs de klok

[Belindo]

Probeer eens een TextToColumns op je kolom met bedragen, dan kun je deze omzetten naar Numbers.

En een steunkolom met =INT(A1) waar A1 de cel met het bedrag voorstelt?

[Pindakaas]

Net even een andere formule geprobeerd en die werkt tot nu toe prima.

code:

ActiveCell.FormulaR1C1 = "=VALUE(RC[-4])"

Moet ik alleen even een hulpkolom maken.

[Lustucru]

Wat doe je nu moeilijk? Voeg in je code de textfiledecimalsrparator toe en je bent klaar.
Lustucru in "[EXCEL 2007] Decimale punt wordt niet herkend bij import CSV"

[Pindakaas]

Excuses, dat werd me niet helemaal duidelijk uit je vorige verhaal. Ik zal het morgen even proberen!

[Pindakaas]

Werkt inderdaad goed! bedankt!

[hans boehlee]

Nog makkelijker vond ik dit. Met zoek en vervang.

selecteer de kolom
klick op
bewerken > zoeken en vervangen
vul onder "zoeken naar" in .
vul onder "vervangen door" in ,
klick op
alles vervangen

Hoe makkelijk kan het zijn.