Read from txt file

[Archibald_SM]

Hello All,

I wish to read weather station data from a text file (comma deliminated) into two separate spreadsheets, one for wind speed, wind direction, soil temp. soil conductivity and air temp and finally pyranometer data into a second separate spreadsheet. All data is to be recorded in columns.

One datum is recorded every minute as per the attached excel spread sheet. The data logger just continuously loops through this cycle. Ultimately there will be thousands of data points recorded. The final cell in each column is to be the average of that column.

Finally, the file name which contains the data will have a format such as 120000240411 (time-date) is it possible to extract this and place in a cell as a reference?


Thanks in advance!!

[mojo249]

The easiest way is to record a macro while opening one of your text files. You'll end up with something like:

Please Login or Register  to view this content.

If you want to be able to select the file to import and get the file name, you can change it to:

Please Login or Register  to view this content.

You can then use varImportFileName and insert it into your sheet.

[Archibald_SM]

Hi Mojo - thanks for the reply but I'm not sure whether your code will achieve what I'm looking for. Please find attached a set of sample data. As mentioned this text file will ultimately contain thousands of data entries which represents 1 data input per min cycled as per the info on the excel sheet above. What I need to do is sort all wind speed measurements in to once column, wind direction measurements in to a second column etc. Give me a shout if this is not clear.


Once again thanks for your response!!!

[mojo249]

The text file is just a series of numbers with no indication of whether a number relates to wind speed/direction etc......

Also, there are no line breaks in the text file. When you import the file it'll place all of the data onto row 1 and since there are a finite number of columns (A to XFD), not all of the data will be imported.

[Archibald_SM]

Hi Mojo,

The text file will be just a continuous series of numbers, comma deliminated, from the data logger. As per the excel sheet provided (for guidance only),the first number in the text file will be wind direction, the second number will be wind speed, the third soil temp, the fourth soil conductivity, the fifth will be solar, the sixth will be air temp, the seventh will be wind speed, the eighth will be solar and so on. There will be a total of 60 readings per hour in the order of which is outlined in the excel sheet provided. The controller program will then loop and start again with wind direction followed by wind speed followed by soil temp........

In total they will be 3 wind direction readings per hour, 25 wind speed, 2 soil temp, 2 soil conductivity, 26 solar and 2 air temp. The controller will be running for a few days thus the text file size. What i'm looking for is a macro that will move through the text file, extract the next number in the sequence and append to the next free cell in the correct column. If this can be done??

Again thanks for your input!

[barryleajo]

Hi Archibald_SM
I have attached a starter for ten.
Have a look and see if this is presenting your data correctly.

Note you will need to change some data in the macro for your filepath etc
Then just delete the data and run the macro 'ReadWdata' from the Developer Tab, Macros dropdown
barry

[Archibald_SM]

Thanks Barry!

I just have to nip out for an hour or so but will check this once I return. Once again much appreciated!


John.

[Archibald_SM]

Works Great!!!

One final question - how would I have the last value in each column represent the average value for that column and transfer this row to sheet 2?

[barryleajo]

Hi John
Attached is a modified version.
Same comments about supplying your own filepath/name.
Will need a bit of tweaking when you need to add output from multiple files.
But .. should give you the idea.
barry

[Archibald_SM]

Thanks Barry!!

Works Perfect !! Should be able to finish it out from here. Much Thanks,


John.