Python Forum - .dat file to xlsxl

Pages: 1 2 3 4 5

Hi,

I want to have the data in the .dat file copied in a xlsxl file.
The data is comma seperated, a fragment from the data incl. Headers is shown below.
Basicallly i want to have the headers above the corresponding data.

Thanks.

"TOACI1","CR1000","Table1"
"TMSTAMP","RECNBR","WS_kph_S_WVT","WindDir_D1_WVT","WindDir_SD1_WVT","WS_kph_Max","AirTC_Avg","AirTC_Max","AirTC_Min","RH_Avg","RH_Max","RH_Min","BP_mbar_Avg","BP_mbar_Max","BP_mbar_Min","Rain_mm_Tot","Rain_mm_Intensity_1_min","Rain_mm_Max_Intensity_1_min","Rain_mm_Min_Intensity_1_min","Tot_Rain_mm_12_sec_Max","Tot_Rain_mm_12_sec_Avg","Lufft_R2S_Mode","Mode(1)","Mode(2)","Mode(3)","Mode(4)","Mode(5)","Mode(6)","SlrW_Avg","SlrW_Max","TdC_Avg","TdC_Max","TdC_Min","TwC_Avg","TwC_Max","TwC_Min","HI_C_Avg","HI_C_Max","SVPWPa_Avg","SVPWPa_Max","SVPWPa_Min","SunHrs_Tot","PrecipitationHrs_Tot","PotSlrW_Avg","PotSlrW_Max","PotSlrW_Min","WC_C_Avg","WC_C_Min"
"2020-03-25 21:09:00",0,9.69,87.8,21.5,13.38,7.397,7.475,7.28,44.17419,44.24,44.1,1024.373,1024.412,1024.319,0,0,0,0,-7999,-7999,0,0,0,0,0,0,0,0,0,-4.005,-3.932,-4.101,2.934,3.002,2.84,7.397,7.475,1029,1034,1021,0,0,0,0,0,5.619,4.987
"2020-03-25 21:10:00",1,9.62,88.5,17.91,12.6,7.375,7.458,7.28,44.26099,44.33,44.21,1024.377,1024.443,1024.288,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-3.999,-3.925,-4.099,2.923,2.995,2.841,7.375,7.458,1027,1033,1021,0,0,0,0,0,5.602,4.966
"2020-03-25 21:11:00",2,9.82,86.6,13.84,13.14,7.349,7.424,7.264,44.31913,44.38,44.27,1024.379,1024.443,1024.319,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-4.005,-3.935,-4.081,2.907,2.97,2.837,7.349,7.424,1025,1031,1019,0,0,0,0,0,5.542,4.891
"2020-03-25 21:12:00",3,9.63,85.9,18.83,14.64,7.335,7.399,7.221,44.49706,44.61,44.36,1024.376,1024.412,1024.319,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-3.964,-3.915,-4.094,2.911,2.96,2.807,7.335,7.399,1024,1029,1017,0,0,0,0,0,5.576,4.657
"2020-03-25 21:13:00",4,8.9,90.9,16.59,11.44,7.331,7.399,7.247,44.53759,44.62,44.48,1024.374,1024.381,1024.288,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-3.956,-3.887,-4.031,2.911,2.971,2.842,7.331,7.399,1024,1029,1018,0,0,0,0,0,5.697,5.222
"2020-03-25 21:14:00",5,9.13,88.5,14.06,11.88,7.31,7.382,7.221,44.49424,44.53,44.47,1024.374,1024.412,1024.288,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-3.988,-3.913,-4.072,2.891,2.953,2.815,7.31,7.382,1023,1028,1017,0,0,0,0,0,5.629,5.096

...and convert the source to numeric in the xlsxl output file.

I'm not sure I understand. It looks like you could just rename the file as "data.csv" and open it directly in Excel. What are you trying to do beyond that?

Hi,

Im struggling with the data.
Ive tried all options to have the data working in the status bar where you can see, when select mutiple cells, the average sum max etc is shown.
The file is updated every minute.
When i double click a cell, its working, but as soon as the file is updated, its not working again....

I have made a script to try.
Its taking ages to have the output and in all cells theres a little black triangle shown.
When i click that triangle, i can convert to numeric.
But i want it to automatic have all the data in numeric.

import pandas as pd
import openpyxl

df = pd.read_table("C:\\Campbellsci\\LoggerNet\\CR1000_Table1.dat", delimiter=',', low_memory=False)
a=df.to_excel("C:\\Users\\Makada\\Desktop\\Map1.xlsx")
df1=pd.to_numeric(a,errors='coerce')

Hi,

Here you can see a part of the output...

Hi,

Maybe someone can point me in the right direction please Smile

Hi,

I am still struggling to get the data to numeric in Excel.
I have the trace error (green triangles in left corner of all cells).

With kind regards.

Hi,

Anyone please Smile

import pandas as pd
 
df = pd.read_table("C:\\Campbellsci\\LoggerNet\\CR1000_Table1.dat", delimiter=',', parse_dates=[1,], low_memory=False, skiprows=1)
cols = df.columns.drop("TMSTAMP")
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce')
df.to_excel("C:\\Users\\Makada\\Desktop\\Map1.xlsx")

Hi buran,

Thanks for your reply, that seems to have worked .
One minor thing is:

When i graph particular data in the output excel file, i get numbers on the bottom of the graph (collumn A i think), but i want to have the date from collumn B there.
Is it possible to have the collumns A and C removed from the output file?

With kind regards.

Hi,

I managed to remove the RECNBR collumn by code below:

df.drop("RECNBR", axis=1, inplace=True)

But how do i remove the nameless A collumn?

With kind regards.

Pages: 1 2 3 4 5