The data in excel spreadsheet is getting deleted after I save it as CSV file
I am trying to save an excel spreadsheet to CSV file, but when I do the DATA in the spreadsheet is getting deleted but in the CSV file I can see the data though. I tried to look on the internet for the solution or any similar issue but I could not found any. Can you please tell me what can be the possible error or solution for this? Thank you in advance !!!!
See also questions close to this topic
Finding Next Occurrence of Range
I am building a code that will allow me to find the next occurrence of a range and build a specific concatenate formula off of attributes from each range. Each range will start with "FINANCIAL STATEMENT" and end with "TOTAL BALANCE," which has been defined in my code below. I have build this out, but am only able to run for the first occurrence. Once the first occurrence of the range is found, in column A I have built out a concatenate code that concatenates specific items in the range with the concatenate results in rows 3 and 5 of each range. I am looking for help in finding the next range that lays beneath the original range but has the same toprow and bottomrow criteria.
I'm 100% ok with the concatenate portion, I just need help with being able to find the next applicable range and running this same concatenate formula.
This is the code thus far:
Sub Concatenate() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim TopRow, BottomRow, DataSelect, FundSelect, FSRange, FSRangeCritiera, MiddleRow As Range Dim cell As Range Dim Master As Workbook Dim dateformat As String Dim last row As Integer Dim fundname, funddate As Range With ActiveSheet Set TopRow = Range("A:B").Find(What:"FINANCIAL STATEMENT", LookIn:=x1Values) Set MiddleRow = Range("A:B").Find(What:="Entity*", LookIn:=x1Values) Set BottomRow = Range("A:B").Find(What:="TOTAL BALANCE", LookIn:=x1Values) With ActiveSheet Set FSRange = Range(TopRow, BottomRow).Columns(0) Set FSRangeCriteria = Range(TopRow, MiddleRow).Columns(1) Set DateSelect = FSRangeCriteria.Rows(3) Set FundSelect = FSRangeCriteria.Rows(5) Set funddate = FSRange.Rows(3) funddate = Format(funddate, " MM DD YYYY") Set fundname = FSRange.Rows(5) FSRange.Activate For each cell in FSRange.Cells If cell.Offset(I,1).Text Like "As of*" Then cell.Formula = Mid(DateSelect, 7) dateformat = Format(funddate, " MM DD YYYY") ElseIf cell.offset(I,1).Text Like "Entity*" Then cell.Formula = Range(FundSelect, 4) ElseIf IsEmpty(cell.offset(I, 1)) And IsEmpty(cell.offset(I,2)) Then cell.offset(I,0) = "" Else: cell.Value = fund name & " - " & cell.offset(I,1) & cell.offset(I, 2) & " - " & dateformat End If Next cell End With End Sub
Filter & Unfilter Entire Data Based on Active Cell Selection & Unselection In Excel
I have an Excel Sheet Contains 3 Columns Name,Mobile,Amount and Multiple rows with relevant data.
I want to Create a Excel VBA Code to do as under:
a) Filter Entire Data Based on Active Cell Selection from Mobile Column
b) Unfilter Entire Data once Cell from Mobile Column is UnSelected or any other cell (other than the cells in Mobile Column) is selected.
Note: By Selection i mean simple clicking on the cell and not double click
I tried to Find such code on the net and found below pasted code.But it does not serve the purpose.Can some one help please.
Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim rgTable As Range Dim rgData As Range Dim xColumn As Integer On Error Resume Next Application.ScreenUpdating = False Set rgTable = Range("mydata") With rgTable Set rgData = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) If Not Application.Intersect(ActiveCell, rgData.Cells) Is Nothing Then xColumn = ActiveCell.Column - .Column + 1 If ActiveSheet.AutoFilterMode = False Then .AutoFilter End If If ActiveSheet.AutoFilter.Filters(xColumn).On = True Then .AutoFilter Field:=xColumn Else .AutoFilter Field:=xColumn, Criteria1:=ActiveCell.Value End If End If End With Set rgData = Nothing Set rgTable = Nothing Application.ScreenUpdating = True End Sub
Please note that above code does not solve the purpose but i have pasted the same for reference purpose only.Thus New Simple Code will be preferred & requested.
passing variables as arguments from excel VBA
I use the following code for passing arguments to my script from VBA.
Successful case: (argument value without space)
FilePath = "c:\Users\dimension_export.exe" AppName = "Area_Sales" Call Shell(FilePath & " " & AppName, 1)
Error case: (argument value with space (''))
FilePath = "c:\Users\dimension_export.exe" AppName = "Total Sales" Call Shell(FilePath & " " & AppName, 1)
when I do this, only the Total part in the AppName variable is passed as a argument to my exe file.
Is there any specific keyword or symbols I should pad up?
Output multiple pandas DataFrames to CSV with custom name
I am trying to output multiple csvs from a function - but also name the csv per the name of the
dfs = ( df1, df2 , df3 , df4) for df in dfs: def to_csvs(df, 'df name as csv name'): df.to_csv('example.csv') return df
I want to use a loop to feed in my multiple
dfsand have a parameter in the function input that would name the
csvoutput as well - is that possible?
Unstructured Text to CSV
I tried to get the solution of powershell script in order to convert to csv format from unstrutured text and I'm stuck at how to repeat update the servername if the 'TechnologyType' contain a different data but attach to the same servername. I've only found a straight forward update but this unstructured a bit different way to read by line.
Servername=svr343 TechnologyType=Storage ID=100 DiskSize=3gb Drive=PhysicalDrive Type=SCSI TechnologyType=Storage ID=110 DiskSize=9gb Drive=PhysicalDrive Type=SCSI TechnologyType=Interface ID=200 localhostname=svr343 ipaddress=184.108.40.206 TechnologyType=Interface ID=220 localhostname=svr343 ipaddress=220.127.116.11 Servername=svr400 TechnologyType=Storage ID=180 DiskSize=5gb Drive=PhysicalDrive Type=SCSI TechnologyType=Storage ID=190 DiskSize=15gb Drive=PhysicalDrive Type=SCSI TechnologyType=Interface ID=250 localhostname=svr400 ipaddress=18.104.22.168 TechnologyType=Interface ID=260 localhostname=svr400 ipaddress=22.214.171.124
Output for CSV:
"ServerName","TechnologyType","ID","DIskSize","Drive","Type","localhostname","IPAddress" "svr343","Storage","100","3gb","PhysicalDrive","SCSI","","" "svr343","Storage","110","9gb","PhysicalDrive","SCSI","","" "svr343","Interface","200","","","","svr343","126.96.36.199" "svr343","Interface","220","","","","svr343","188.8.131.52" "svr400","Storage","180","5gb","PhysicalDrive","SCSI","","" "svr400","Storage","190","15gb","PhysicalDrive","SCSI","","" "svr400","Interface","250","","","","svr343","184.108.40.206" "svr400","Interface","260","","","","svr343","220.127.116.11"
Extract data from netCDF file based on station
I have read other solutions for NetCDF data but my data are a little different and I do not know how to extract data from NetCDF and save them in CSV files based on stations. Data include the maximum temperature values for stations. I just need stations located in latitudes:25.74 to 49.05 and longitude: -93.44 to -116.0. The format of time is different and I just need time[7518:43947190] which include data from 1948. I want to create multiple csv files. every file have to be data for one single station that include time, tmax, and quality flag of data. I really appreciate if anybody can help.
from netCDF4 import Dataset dataset=Dataset("D:/ushcn_tmax.nc") #### Print dimentions ##### print dataset.file_format print dataset.dimensions.keys() print dataset.dimensions['name_strlen'] print dataset.dimensions['obs'] print dataset.dimensions['station'] #### Print variables #### print dataset.variables.keys() print dataset.variables['LON'] print dataset.variables['LAT'] print dataset.variables['ELEVATION'] print dataset.variables['STATION_NAME'] print dataset.variables['STATION_INDEX'] print dataset.variables['TIME'] print dataset.variables['TMAX'] print dataset.variables['TMAX_MFLAG'] print dataset.variables['TMAX_QFLAG'] print dataset.variables['TMAX_SFLAG']
Dimension and variables of my data can bee seen here:
NETCDF3_CLASSIC [u'name_strlen', u'obs', u'station'] <type 'netCDF4._netCDF4.Dimension'>: name = 'name_strlen', size = 50 <type 'netCDF4._netCDF4.Dimension'>: name = 'obs', size = 43947189 <type 'netCDF4._netCDF4.Dimension'>: name = 'station', size = 1218 [u'LON', u'LAT', u'ELEVATION', u'STATION_NAME', u'STATION_INDEX', u'TIME', u'TMAX', u'TMAX_MFLAG', u'TMAX_QFLAG', u'TMAX_SFLAG'] <type 'netCDF4._netCDF4.Variable'> float32 LON(station) standard_name: longitude long_name: station longitude units: degrees_east unlimited dimensions: current shape = (1218,) filling off <type 'netCDF4._netCDF4.Variable'> float32 LAT(station) standard_name: latitude long_name: station latitude units: degrees_north unlimited dimensions: current shape = (1218,) filling off <type 'netCDF4._netCDF4.Variable'> float64 ELEVATION(station) long_name: elevation above the sea level standard_name: elevation units: m positive: up axis: Z unlimited dimensions: current shape = (1218,) filling off <type 'netCDF4._netCDF4.Variable'> |S1 STATION_NAME(station, name_strlen) long_name: USHCN station name cf_role: timeseries_id unlimited dimensions: current shape = (1218, 50) filling off <type 'netCDF4._netCDF4.Variable'> int32 STATION_INDEX(obs) long_name: which station this obs is for instance_dimension: station unlimited dimensions: current shape = (43947189,) filling off <type 'netCDF4._netCDF4.Variable'> float64 TIME(obs) standard_name: time long_name: Time units: decimal day _FillValue: -9999.0 comment: time calculeted as: year + day_of_year/days_in_year unlimited dimensions: current shape = (43947189,) filling off <type 'netCDF4._netCDF4.Variable'> int32 TMAX(obs) standard_name: TMAX long_name: maximum temperature units: degrees F coordinates: time lat lon elevation _FillValue: -9999 unlimited dimensions: current shape = (43947189,) filling off <type 'netCDF4._netCDF4.Variable'> |S1 TMAX_MFLAG(obs) standard_name: TMAX_MFLAG long_mane: measurement flag for TMAX flag_values: BDLT flag_meanings: Blank = no measurement information applicable; B = precipitation total formed from two 12-hour totals; D = precipitation total formed from four six-hour totals; L = temperature appears to be lagged with respect to reported hour of OBServation; T = trace of precipitation, snowfall, or snow depth unlimited dimensions: current shape = (43947189,) filling off <type 'netCDF4._netCDF4.Variable'> |S1 TMAX_QFLAG(obs) standard_name: TMAX_QFLAG long_mane: quality flag for TMAX flag_values: ADGIKMNORSTWX flag_meanings: Blank = did not fail any quality assurance check; A = failed accumulation total check; D = failed duplicate check; G = failed gap check; I = failed internal consistency check; K = failed streak/frequent-value check; M = failed megaconsistency check; N = failed naught check; O = failed climatological outlier check; R = failed lagged range check; S = failed spatial consistency check; T = failed temporal consistency check; W = temperature too warm for snow; X = failed bounds check; unlimited dimensions: current shape = (43947189,) filling off <type 'netCDF4._netCDF4.Variable'> |S1 TMAX_SFLAG(obs) standard_name: TMAX_SFLAG long_mane: source flag for TMAX flag_values: 0126ABFGHIMQRSX flag_meanings: Blank = No source (i.e., data value missing); 0 = U.S. Cooperative Summary of the Day (NCDC DSI-3200); 1 = U.S. Preliminary Cooperative Summary of the Day -- Transmitted; 2 = U.S. Preliminary Cooperative Summary of the Day -- Keyed from paper forms; 6 = CDMP Cooperative Summary of the Day (NCDC DSI-3206); A = U.S. Automated Surface Observing System (ASOS) real-time data (since January 1, 2006); B = U.S. ASOS data for October 2000-December 2005 (NCDC DSI-3211); F = U.S. Fort data; G = Official Global Climate Observing System (GCOS) or other government-supplied data; H = High Plains Regional Climate Center real-time data; I = International collection (non U.S. data received through personal contacts); M = Monthly METAR Extract (additional ASOS data); Q = Data from several African countries that had been 'quarantined', that is, withheld from public release until permission was granted from the respective meteorological services; R = NCDC Reference Network Database (Climate Reference Network and Historical Climatology Network-Modernized); S = Global Summary of the Day (NCDC DSI-9618), NOTE: 'S' values are derived from hourly synoptic reports exchanged on the Global Telecommunications System (GTS).Daily values derived in this fashion may differ significantly from 'true' daily data, particularly for precipitation (i.e., use with caution); X = U.S. First-Order Summary of the Day (NCDC DSI-3210) unlimited dimensions: current shape = (43947189,) filling off
I have tried to read data with :
while the mentione lat and lon belong to one station and I received error "KeyError: 'lat'". Is there any way I can convert variables (lat, lon, and time) to dimentions to make it easier to work with? Or any way I can extract data based on station as dimension? Any help is really appreciated.