[Løst] Power Pivot i Excel - skilletegn og datatype

[SuperSofie]

Hei,

 

Jeg driver med et datasett som var for stort for Excel, dvs. det hadde for mange kolonner og rader.

Jeg fant en løsning på Google ved å opprette en datamodell (https://blogs.technet.microsoft.com/josebda/2017/02/12/loading-csvtext-files-with-more-than-a-million-rows-into-excel/#comment-86845) som viser total mengde, og antall, av alle variablene jeg har valgt å ha med. 

 

Problemet mitt er at jeg ser i "bruksanvisningen" jeg har valgt at de har "count of value" og "sum of value". Det vil si jeg har antall priser, i stedet for sum av priser, som er min variabel.

For å få sum av, i stedet for antall av, har jeg funnet ut at jeg må endre alle radene mine til datatype desimal tall, og ikke datatype tekst.

Problemet er at når jeg markerer alle radene (flere millioner) og endrer til desimal tall kommer det opp error i alle radene, fordi skilletegnet i verdiene nedover er for eksempel "1.256" og ikke "1,256". Jeg må derfor endre alle verdiene i radene nedover til komma og ikke punktum, men dette får jeg ikke til? Jeg får til å endre hver rad sin verdi nedover manuelt og skrive 1,256 og trykke erstatt, men jeg kan jo ikke gjøre dette 17 millioner ganger på ulike tall. (Da blir det ikke error når jeg endrer til desimaler)

Prøvde i excel og gå på avanserte innstillinger og velge "." som skilletegn og ikke ",", men dette gjorde ingen forskjell  når jeg operer i Power Pivot? 

I linken nevnt over ser jeg at personen som har lagt ut eksempel også har punktum som skilletegn nedover i sine rader, men får likevel opp "sum of" i tillegg til "count of", har personen fått til å endre datatypen til desimal tall og ikke tekst selv om skilletegnet er punktum? 

Noen som vet hva jeg kan gjøre?

 

Ida

 

 

[Horge]

Åpne csv-filen i notepad eller tilsvarende tekst-editor, og endre desimaltegnet via "søk og erstatt".

[trn100]

Du kan også forsøke å endre oppsettet under Alternativer for Excel. Fjern haken foran "Bruk systemskilletegn" og velg desimal/tusenskilletegn.

 

 

[SuperSofie]

Åpne csv-filen i notepad eller tilsvarende tekst-editor, og endre desimaltegnet via "søk og erstatt".

 

Åpne csv-filen i notepad eller tilsvarende tekst-editor, og endre desimaltegnet via "søk og erstatt".

 

Grunnen til at jeg åpner det i Power Pivott er at filen er for stor til å åpnes vanlig i Excel/Word osv. Hver fil inneholder over 2 millioner observasjoner/rader, så det er grunnen til at jeg må finne summen i Pivott, og ikke kan bruke filen vanlig i Excel og andre dataprogrammer. Så da får jeg ikke lastet det opp i notepad f.eks

[SuperSofie]

Du kan også forsøke å endre oppsettet under Alternativer for Excel. Fjern haken foran "Bruk systemskilletegn" og velg desimal/tusenskilletegn.

Ja, jeg prøvde å endre komma til punktum, men det skjedde ingenting i Pivott. Kanskje jeg må laste opp datasettet på nytt i Pivott for at innstillingen kan fungere? Eller om det finnes egne instilllinger for Pivott?

[trn100]

Snodig at det ikke virker hos deg? Her har jeg ingen problemer når jeg setter opp en datamodell mot en .csv fil?

[trn100]

Kan det være at en eller flere av postene i dine kildedata er korrupte eller ikke inneholder et gyldig tall? Det er en en greie jeg ofte har opplevd med pivot tabeller at om man f.eks. ønsker å gruppere på dato og en post ikke inneholder en gyldig dato.

Prøv å importere det hele som tekst og sjekk verdiene i de kolonnene du ikke får endret?

[SuperSofie]

Kan det være at en eller flere av postene i dine kildedata er korrupte eller ikke inneholder et gyldig tall? Det er en en greie jeg ofte har opplevd med pivot tabeller at om man f.eks. ønsker å gruppere på dato og en post ikke inneholder en gyldig dato.

 

Prøv å importere det hele som tekst og sjekk verdiene i de kolonnene du ikke får endret?

De jeg ikke får endret er de som inneholder punktum. "1.256"  For eksempel 10, beholdes og blir ikke error på. Endrer jeg manuelt 1.256 til komma 1,256 og deretter endrer til datatype desimaler, så kommer det ikke error. Så problemet er punktum skulle vært endret til komma, men jeg kan ikke endre dette manuelt på 17 millioner tall.... Så tydeligvis er "." ikke et gyldig tall, kun med komma.

[trn100]

Hvis du endrer oppsettet i Windows, bare for å teste?

 

Edit: Har du eventuelt en fil som vi kan teste med?

Endret 6. mars 2018 av trn100

[SuperSofie]

Hvis du endrer oppsettet i Windows, bare for å teste?

 

Edit: Har du eventuelt en fil som vi kan teste med?

Driver med en skole oppgave; http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml datasettene er her.

Så skal gjøre dette med alle de 60 stk fra 2009 og oppover. Jeg holder på med januar 2009 nå. 

Bilde under viser at det er riktig med sum på antall passasjerer, men feil på pris, der står det antall priser (stykker) i stedet for sum av prisene.

Googlet og finner at grunnen er at "total pris" tabellen er i tekst, og ikke datatype tall.

Prøver å endre, men får error. Antagelig på grunn av at tallene er med punktum og ikke komma, for hvis jeg endrer en til komma så blir det ikke error på denne.

[SuperSofie]

 

Hvis du endrer oppsettet i Windows, bare for å teste?

 

Edit: Har du eventuelt en fil som vi kan teste med?

Driver med en skole oppgave; http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml datasettene er her.

Så skal gjøre dette med alle de 60 stk fra 2009 og oppover. Jeg holder på med januar 2009 nå. 

Bilde under viser at det er riktig med sum på antall passasjerer, men feil på pris, der står det antall priser (stykker) i stedet for sum av prisene.

Googlet og finner at grunnen er at "total pris" tabellen er i tekst, og ikke datatype tall.

Prøver å endre, men får error. Antagelig på grunn av at tallene er med punktum og ikke komma, for hvis jeg endrer en til komma så blir det ikke error på denne.

 

Og slik ser det ut om jeg endrer til desimaler (tall), da står det error i alle. Et eksempel; endret 9,14 til komma og ikke punktum manuelt før jeg markerte og valgte desimaler. Da blir det ikke error, og ikke error på hele tall. Så det må være punktummet som er problemet. Jeg kan ikke endre 17 millioner celler manuelt...

[trn100]

Det var noen voksne filer ja! ;-)

Lastet ned Green August 2013 som var en del mindre enn de andre.

Når du setter opp koblingen, haker du av for at første rad er overskrifter? Hvis ikke får du en rad som ikke inneholder tall og kan skape problemer.

Edit: Ser forresten nå at du antagelig har gjort det...

Edit2: 9.4 i rad 2 funker jo, så hvorfor feil i rad 3 med 14.6??

Endret 6. mars 2018 av trn100

[SuperSofie]

Det var noen voksne filer ja! ;-)

 

Lastet ned Green August 2013 som var en del mindre enn de andre.

 

Når du setter opp koblingen, haker du av for at første rad er overskrifter? Hvis ikke får du en rad som ikke inneholder tall og kan skape problemer.

 

Edit: Ser forresten nå at du antagelig har gjort det...

 

Edit2: 9.4 i rad 2 funker jo, så hvorfor feil i rad 3 med 14.6??

Jeg kan prøve å hake av for det, men er ganske sikker på at det er gjort.

 

Ja, 9,4 i rad 2 funker fordi jeg endret fra punktum til komma manuelt "endre verdi" før jeg valgte å endre til datatype tall.

Så derfor funker det ikke i rad 3 fordi det er 14 punktum 6 og ikke 14 komma 6. 

Det illustrerer poenget mitt med at det vil fungere å endre datatype til tall hvis jeg får endret alle cellene fra punktum til komma.

 

Edit; Det kan være en annen mulighet for å få sum og ikke antall også, for linken til instruksjonen i første innlegg viser at personen sitt datasett der inneholder punktum og ikke komma, så det kan være en annen mulighet å få sum også, jeg vet bare om at det fungerer med sum når datatypen er tall og ikke tekst.

[trn100]

Hmmm... Testet igjen på 2009-01 filen og fikk importert hele filen uten feil i "Total_Amt"? Over 14 millioner poster som desimaltall?

 

 

[SuperSofie]

Hmmm... Testet igjen på 2009-01 filen og fikk importert hele filen uten feil i "Total_Amt"? Over 14 millioner poster som desimaltall?

Ja, fra det bilde der. Trykk på Pivot-tabell, og huk av for "total_amount" til høyre. Og plasser denne under values, blir det da "count of" eller "sum of"?

 

Edit; Merkelig at dine er med komma da, og med datatype desimal?? Mine er tekst.

 

Edit2; Jeg har lest noe om at Excel ikke kan ha flere enn 15 desimaler bak komma. Kan det være problemet for at jeg får error? I såfall merkelig da du får desimaler, og ikke jeg. Kan være du har en innstilling på for kun to tall bak komma?

 

Endret 6. mars 2018 av SuperSofie

[trn100]

Mine tall ble importert som desimaltall ja.

 

Power Pivot - Administrer - Fra Andre Kilder - Tekstfil og deretter oppsette som i bildet. Da ser du også om verdiene blir importert som tekst eller tall.

 

 

 

[SuperSofie]

Mine tall ble importert som desimaltall ja.

 

Power Pivot - Administrer - Fra Andre Kilder - Tekstfil og deretter oppsette som i bildet. Da ser du også om verdiene blir importert som tekst eller tall.

 

 

 

 

Ok, takk. Da prøver jeg den metoden, om det fungerer!

 

Edit; Jeg fikk det til å bli desimaler ved å markere tabellen, velge skilletegnet punktum, og dele den inn i to tabeller slik at det ble heletall i en, og desimalene i en ny tabell som jeg slettet. Da fungerte det, men ble jo ikke "nøyaktig" sum i forhold til alle kommaene tilsvarer en del av summen og ble slettet. Skal prøve din metode!

Endret 6. mars 2018 av SuperSofie

[trn100]

Lykke til! 

[SuperSofie]

Mine tall ble importert som desimaltall ja.

 

Power Pivot - Administrer - Fra Andre Kilder - Tekstfil og deretter oppsette som i bildet. Da ser du også om verdiene blir importert som tekst eller tall.

 

 

 

 

Tusen tusen takk! Dette fungerte utmerket! :D Veldig glad!

[trn100]

Så bra!    Håper dog du har raskere Internett enn meg om du skal ha ned alle de filene!