Cleaning an Excel Sheet

Excel 2010:
Choose Data > Get External Data > From Text and choose your source text file, click Import.
Choose 'Delimited', start import at row 153, click Next.
Under 'Delimiters', tick Space, and untick all others. Click Finish.
Choose where to put the data, and click OK.
You'll have an additional cell in the 'header' row, containing "# " - simply delete this cell (shift left).


You could also use Power Query - which will make it easier to refresh the data, and join up data from multiple similar sources. Something like:

Please Login or Register  to view this content.

Here's a couple of power queries which will combine all four text sources, and filter out irrelevant values. It returns around 1.3 million rows - so I'd suggest loading to Power Pivot data model, rather than materialising the data on worksheet(s).

fnGetTxtData:

Please Login or Register  to view this content.

CombinedData:

Please Login or Register  to view this content.

Attached file has query as connection only, to keep file size small.

.
I copied 76,000 rows, pasted in A2.

Went back and copied the HEADERS from the website : site_code year month day hour minute second value value_unc nvalue latitude longitude altitude elevation intake_height instrument qcflag
Pasted that in A1.

Highlight all of Col A.

Then went to the DATA tab / TEXT TO COLUMNS and followed these selections :

Delimited
Tab & Space
Next
Finish

I suspect you will need to copy several groups of the data ... perhaps another 70,000 rows at a time ? and repeat the procedure.

ALSO: There are a total of 385,705 rows of data total.

Originally Posted by TheRevenant

Unfortunately the Excel sheet you provided is blank because it's not establishing a connection.

It's blank, as I have neither materialised the query in a table, not loaded to the data model.

I'd suggest you choose to load to the data model, then manipulate your data in Power Pivot.

I offered step by step instructions for Excel 2010 as that's what your profile states you are using...

Edit:

Repeating myself, this query works (in 2016) for a single file:

Please Login or Register  to view this content.

Here's the workbook with the data (for all four source files) added to the data model, and summarised in a pivot table. It's a 3.5Mb zip file.


https://excel.solutions/wp-content/u...eRevenant2.zip

You can update by simply clicking "Data > Refresh All" - no need to undertake any manual import steps.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.