OK - here's what you need to do:
1. Go to the Data ribbon and select From Text/CSV.
2. Find and select your CSV file and click Import.
3. Click Transform Data at the bottom - this will load the data to the PQ editor.
4. With Column1 highlighted green, use the filter drop-down at the top of it to untick blank and empty rows (two boxes).
5. Click on the table icon to the left of the Column1 header and select Remove Top Rows - type 1 into the dialog box and OK.
6. On the Transform ribbon, select Use Top Row as Headers.
7. On the Home ribbon, select the Close & Load drop-down and choose Close & Load To - choose Existing Worksheet and then the cell where you want the table to appear - OK.
Your cleaned data table will appear in Excel:
Excel 2016 (Windows) 32 bit
|
A |
B |
C |
D |
E |
1 |
Name |
Birth |
Marriage |
Death |
Spouse Name |
2 |
Mouse, Minnie |
07 Aug 1877 |
26 Nov 1893 |
7-Jun-47 |
Mouse, Mickey |
3 |
Duck, Daisy |
|
|
|
Duck, Donald |
4 |
Fudd, Elmer |
28-Dec-14 |
24-Nov-40 |
6-Dec-12 |
Fudd, Elsa |
5 |
Tarzan |
|
|
|
Jane |
6 |
Anna |
17-Jan-04 |
|
13-Nov-06 |
Kristoff |
7 |
Kermit |
01 May 1862 |
26 Nov 1893 |
21-Oct-19 |
Miss Piggy |
8 |
Tink |
08 Jun 1896 |
|
|
Terrence |
9 |
Ariel |
28-Apr-09 |
11-May-45 |
11-Oct-91 |
Eric |
10 |
Simba |
29 Oct 1894 |
|
1925 |
Nala |
The M Code generated is here:
You can duplicate this query at any time to import fresh data - just change the source location.
Bookmarks