.dat file to xlsxl - Printable Version

.dat file to xlsxl - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: .dat file to xlsxl (/thread-25678.html)

Pages: 1 2 3 4 5

.dat file to xlsxl - Makada - Apr-07-2020

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

RE: .dat file to xlsxl - Makada - Apr-07-2020

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

RE: .dat file to xlsxl - bowlofred - Apr-07-2020

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?

RE: .dat file to xlsxl - Makada - Apr-08-2020

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')

RE: .dat file to xlsxl - Makada - Apr-08-2020

Hi,

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

RE: .dat file to xlsxl - Makada - Apr-08-2020

Hi,

Maybe someone can point me in the right direction please Smile

RE: .dat file to xlsxl - Makada - Apr-09-2020

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.

RE: .dat file to xlsxl - Makada - Apr-17-2020

Hi,

Anyone please Smile

RE: .dat file to xlsxl - buran - Apr-17-2020

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")

RE: .dat file to xlsxl - Makada - Apr-23-2020

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.