Formatting an imported CSV file.

Ramballah

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
334
Office Version
  1. 365
Platform
  1. Windows
Hey all,
I have imported an CSV file and now i get this:

Hwoqim.jpg

Now this has all the information i want but theres too much information i wanna keep...
So in the end i want it to look like this in a different sheet (master sheet)
Kj3qTZ.jpg

Now maybe you notice that i left out the SLB Individueel class. thats because its just a break. u only have to go there if u get personally invited by a teacher. Uhm im not sure if its even possible for it to look like this through macro's or formulas? But basically the concept is. I'll be uploading a new CSV file every week and it will make 5 new columns like this so i can just hide these 5 columns. So i can keep the current week here but still save the old weeks. Now if u got questions please ask me. I also give you the workbook here :)
https://www.dropbox.com/s/swqdqoa8ltm01w1/Book1.xlsx?dl=0
 
Sort by date Sort by votes
It will be good if you learn PowerQuery :) It will not hurt or even help now and in the future.

with an alternative view it is simpler

PowerQuery and PivotTable (automated):

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
28/01/2019​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
29/01/2019​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
30/01/2019​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
31/01/2019​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
01/02/2019​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
28/04/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BE[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]BE[/td][td=bgcolor:#DDEBF7]DU[/td][td=bgcolor:#DDEBF7]Excel[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7]LL[/td][td=bgcolor:#DDEBF7]SLB individueel[/td][td=bgcolor:#DDEBF7]BA[/td][td=bgcolor:#DDEBF7]DU[/td][td=bgcolor:#DDEBF7]EN[/td][td=bgcolor:#DDEBF7]Excel[/td][td=bgcolor:#DDEBF7]KD Ondernemerschap[/td][td=bgcolor:#DDEBF7]NE[/td][td=bgcolor:#DDEBF7]BA[/td][td=bgcolor:#DDEBF7]BE[/td][td=bgcolor:#DDEBF7]EN[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7]NE[/td][td=bgcolor:#DDEBF7]BA[/td][td=bgcolor:#DDEBF7]BU[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7]KD Ondernemerschap[/td][td=bgcolor:#DDEBF7]RV[/td][td=bgcolor:#DDEBF7]SLB individueel[/td][td=bgcolor:#DDEBF7]TEST[/td][td=bgcolor:#DDEBF7]TEST2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW164[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW122[/td][td=bgcolor:#DDEBF7]FW162[/td][td=bgcolor:#DDEBF7]FW162[/td][td=bgcolor:#DDEBF7]FW166[/td][td=bgcolor:#DDEBF7]FW156[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW117[/td][td=bgcolor:#DDEBF7]FW116[/td][td=bgcolor:#DDEBF7]FW157[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW116[/td][td=bgcolor:#DDEBF7]FW164[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW167[/td][td=bgcolor:#DDEBF7]FW157[/td][td=bgcolor:#DDEBF7]FW166[/td][td=bgcolor:#DDEBF7]FW157[/td][td=bgcolor:#DDEBF7]FW156[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW165[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]pp04[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]bs19[/td][td=bgcolor:#DDEBF7]sb41[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]sl08[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ho3b[/td][td=bgcolor:#DDEBF7]bs19[/td][td=bgcolor:#DDEBF7]jo2l[/td][td=bgcolor:#DDEBF7]sb41[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]iw99[/td][td=bgcolor:#DDEBF7]ho3b[/td][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]jo2l[/td][td=bgcolor:#DDEBF7]pp04[/td][td=bgcolor:#DDEBF7]iw99[/td][td=bgcolor:#DDEBF7]ho3b[/td][td=bgcolor:#DDEBF7]ve4a[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]wi1a[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
14:30:00​
[/td][td=bgcolor:#DDEBF7]
11:30:00​
[/td][td=bgcolor:#DDEBF7]
13:00:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][td=bgcolor:#DDEBF7]
09:30:00​
[/td][td=bgcolor:#DDEBF7]
15:30:00​
[/td][td=bgcolor:#DDEBF7]
13:30:00​
[/td][td=bgcolor:#DDEBF7]
11:00:00​
[/td][td=bgcolor:#DDEBF7]
15:00:00​
[/td][td=bgcolor:#DDEBF7]
13:30:00​
[/td][td=bgcolor:#DDEBF7]
16:30:00​
[/td][td=bgcolor:#DDEBF7]
15:00:00​
[/td][td=bgcolor:#DDEBF7]
10:00:00​
[/td][td=bgcolor:#DDEBF7]
09:00:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][td=bgcolor:#DDEBF7]
11:30:00​
[/td][td=bgcolor:#DDEBF7]
10:00:00​
[/td][td=bgcolor:#DDEBF7]
13:00:00​
[/td][td=bgcolor:#DDEBF7]
14:00:00​
[/td][td=bgcolor:#DDEBF7]
08:30:00​
[/td][td=bgcolor:#DDEBF7]
14:00:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][td=bgcolor:#DDEBF7]
09:00:00​
[/td][td=bgcolor:#DDEBF7]
15:00:00​
[/td][td=bgcolor:#DDEBF7]
11:00:00​
[/td][td=bgcolor:#DDEBF7]
08:30:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
15:30:00​
[/td][td=bgcolor:#DDEBF7]
12:30:00​
[/td][td=bgcolor:#DDEBF7]
14:30:00​
[/td][td=bgcolor:#DDEBF7]
13:00:00​
[/td][td=bgcolor:#DDEBF7]
10:30:00​
[/td][td=bgcolor:#DDEBF7]
17:00:00​
[/td][td=bgcolor:#DDEBF7]
15:00:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][td=bgcolor:#DDEBF7]
15:30:00​
[/td][td=bgcolor:#DDEBF7]
14:30:00​
[/td][td=bgcolor:#DDEBF7]
17:30:00​
[/td][td=bgcolor:#DDEBF7]
16:00:00​
[/td][td=bgcolor:#DDEBF7]
11:30:00​
[/td][td=bgcolor:#DDEBF7]
10:00:00​
[/td][td=bgcolor:#DDEBF7]
13:30:00​
[/td][td=bgcolor:#DDEBF7]
12:30:00​
[/td][td=bgcolor:#DDEBF7]
11:00:00​
[/td][td=bgcolor:#DDEBF7]
14:00:00​
[/td][td=bgcolor:#DDEBF7]
15:00:00​
[/td][td=bgcolor:#DDEBF7]
10:00:00​
[/td][td=bgcolor:#DDEBF7]
15:00:00​
[/td][td=bgcolor:#DDEBF7]
13:30:00​
[/td][td=bgcolor:#DDEBF7]
10:30:00​
[/td][td=bgcolor:#DDEBF7]
16:00:00​
[/td][td=bgcolor:#DDEBF7]
12:00:00​
[/td][td=bgcolor:#DDEBF7]
09:00:00​
[/td][td=bgcolor:#DDEBF7]
13:00:00​
[/td][td=bgcolor:#DDEBF7]
13:00:00​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
or like this

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]
28/01/2019​
[/td][td]BE[/td][td]FW165[/td][td]wi1a[/td][td]
14:30:00​
[/td][td]
15:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW164[/td][td]pp04[/td][td]
11:30:00​
[/td][td]
12:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]ve3p[/td][td]
13:00:00​
[/td][td]
14:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
29/01/2019​
[/td][td]BE[/td][td]FW165[/td][td]wi1a[/td][td]
12:00:00​
[/td][td]
13:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DU[/td][td]FW122[/td][td]bs19[/td][td]
09:30:00​
[/td][td]
10:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Excel[/td][td]FW162[/td][td]sb41[/td][td]
15:30:00​
[/td][td]
17:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW162[/td][td]ve3p[/td][td]
13:30:00​
[/td][td]
15:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]LL[/td][td]FW166[/td][td]sl08[/td][td]
11:00:00​
[/td][td]
12:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]SLB individueel[/td][td]FW156[/td][td]ve3p[/td][td]
15:00:00​
[/td][td]
15:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
30/01/2019​
[/td][td]BA[/td][td]FW161[/td][td]ho3b[/td][td]
13:30:00​
[/td][td]
14:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DU[/td][td]FW117[/td][td]bs19[/td][td]
16:30:00​
[/td][td]
17:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]EN[/td][td]FW116[/td][td]jo2l[/td][td]
15:00:00​
[/td][td]
16:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Excel[/td][td]FW157[/td][td]sb41[/td][td]
10:00:00​
[/td][td]
11:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]KD Ondernemerschap[/td][td]FW161[/td][td]ve3p[/td][td]
09:00:00​
[/td][td]
10:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]NE[/td][td]FW165[/td][td]iw99[/td][td]
12:00:00​
[/td][td]
13:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
31/01/2019​
[/td][td]BA[/td][td]FW161[/td][td]ho3b[/td][td]
11:30:00​
[/td][td]
12:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]BE[/td][td]FW165[/td][td]wi1a[/td][td]
10:00:00​
[/td][td]
11:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]EN[/td][td]FW116[/td][td]jo2l[/td][td]
13:00:00​
[/td][td]
14:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW164[/td][td]pp04[/td][td]
14:00:00​
[/td][td]
15:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]NE[/td][td]FW165[/td][td]iw99[/td][td]
08:30:00​
[/td][td]
10:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
01/02/2019​
[/td][td]BA[/td][td]FW161[/td][td]ho3b[/td][td]
14:00:00​
[/td][td]
15:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]BU[/td][td]FW167[/td][td]ve4a[/td][td]
12:00:00​
[/td][td]
13:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW157[/td][td]ve3p[/td][td]
09:00:00​
[/td][td]
10:30:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]KD Ondernemerschap[/td][td]FW166[/td][td]ve3p[/td][td]
15:00:00​
[/td][td]
16:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]RV[/td][td]FW157[/td][td]ve3p[/td][td]
11:00:00​
[/td][td]
12:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]SLB individueel[/td][td]FW156[/td][td]ve3p[/td][td]
08:30:00​
[/td][td]
09:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]TEST[/td][td]FW165[/td][td]wi1a[/td][td]
12:00:00​
[/td][td]
13:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
28/04/2019​
[/td][td]TEST2[/td][td]FW165[/td][td]wi1a[/td][td]
12:00:00​
[/td][td]
13:00:00​
[/td][/tr]
[/table]
 
Upvote 0
I like the last version better, that looks like its easier to read off.. Can you teach me how you did that?
 
Upvote 0
here is an example for the last two: example alternative

to see steps in PowerQuery use Show Queries from Data tab then double click on table on the right side. It will open PowerQuery Editor and on the right side you'll see steps
You need prepare table first if you want it to use in PivotTable
After all you can simply use this table as source for PT and play with PT styles and options to get view like you want
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
915
MichaelMorris
M
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
446
Akuini
Akuini
M
  • Question Question
Power Query with Dynamic Data Source
Replies
4
Views
938
Alex Blakenburg
A
M
  • Question Question
VBA to Clean Up CSV with Large Blank Spaces and Extra Text Rows
Replies
5
Views
641
kevin9999
K
H
  • Question Question
Macro to download zip, unpack and copy data
Replies
0
Views
711
HeinrichPaul
H

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top