Extract data and text from .csv file and save to a text file

Question

I am trying to use this CsvHelper from https://github.com/JoshClose/CsvHelper/blob/master/src/CsvHelper.Example/Program.cs

to extract relevant information and save it to a text file.

if the Measured Value (F) is already in the exponential form

9.79E-11

in the Excel file, how are we able to extract out the value such that it is 97.92pF in the text file, without manipulating the Excel file contents?

how to manipulate to show the Frequency in Hz, kHz and MHz?

appreciate any useful comment and help.

see attached files.

Vulpes · Answer

Sure try this: using System; using System.IO; class Test { static void Main() { double multiplier = Math.Pow(10 9); string temp1 = new string('-' 48); string temp2 = | Measured | Measured | Allowed | Result |; string temp3 = | Value | Dev(%) | Dev(%) | |; string temp4 = new string('-' 48); string header = String.Format({0}
{1}
{2}
{3} temp1 temp2 temp3 temp4); using(StreamReader sr = new StreamReader(test3.csv)) { using(StreamWriter sw = new StreamWriter(test3a.csv)) { string line; int i = 1; while((line = sr.ReadLine()) != null) { if (i > 2) { line = line.Replace(\ ); string[] items = line.Split(''); double resistor; double.TryParse(items[0] out resistor); if (resistor { items[0] = String.Format(Resistor R={0 7:F2}mR resistor * 1000); } else { items[0] = String.Format(Resistor R={0 7:F3} R resistor); } double mValue; double.TryParse(items[1] out mValue); if (mValue { items[1] = String.Format({0 9:F2}mR mValue * 1000); } else { items[1] = String.Format({0 9:F3} R mValue); } double mDev; double.TryParse(items[2] out mDev); string mDevs = String.Format({0}{1:F3} (mDev >= 0) ? + :  mDev); items[2] = String.Format({0 13:F3} mDevs); double aDev; double.TryParse(items[3] out aDev); items[3] = String.Format({0 12:F3} aDev); items[4] = String.Format({0 10} items[4]); if (i > 3) sw.WriteLine(

); sw.WriteLine(items[0]); // resistor line sw.WriteLine(header); // header lines sw.WriteLine(String.Format({0}{1}{2}{3} items[1] items[2] items[3] items[4])); } i++; } } } } }

TAN WhoAMI · Answer

cheers thanks for your verification.

Vulpes · Answer

Yes it does look like the image has been produced from a more accurate source as there are a couple more decimal places. If all you have is the .csv file then I suggest you edit that to include the extra decimal places before reproducing the figures in the image.

TAN WhoAMI · Answer

yes what you say is true...I still could not figure out how they produce more accurate values than the zip .csv file...

Vulpes · Answer

You didn't include the Excel file in the zip file but clearly you can't create figures which are more accurate than the raw data. Perhaps the raw data (i.e. the .csv file) is itself a snapshot of an original .xls file which contained more accurate data?

TAN WhoAMI · Answer

Hi Vuples For the Measured Value I am still wondering why the generated .csv file produce 5 decimal places which are not the same as the picture file attached. I understand the original file already does not contain enough decimal places... so it puts a zero on the 5th decimal places. But why the picture file able to get those precise decimal places? it is also produce from that original file... puzzled.....

TAN WhoAMI · Answer

THANK YOU VERY MUCH Vulpes

TAN WhoAMI · Answer

Hi Vulpes I try with the attached code but could not produce what is desired from the picture file. Could you help? thanks.

TAN WhoAMI · Answer

EXACTLY what I need thanks a lot Vulpes again :)

Vulpes · Answer

OK try this: using System; using System.IO; class Test { static void Main() { double multiplier = Math.Pow(10 12); using(StreamReader sr = new StreamReader(test1.csv)) { using(StreamWriter sw = new StreamWriter(test1a.csv)) { string line; int i = 1; while((line = sr.ReadLine()) != null) { line = line.Replace(\ ); if (i == 1) { string[] items = line.Split(''); double capacitor; double.TryParse(items[1] out capacitor); capacitor *= multiplier; items[0] = Capacitor; items[1] = String.Format( C= {0:F2}pF capacitor); sw.WriteLine(String.Join( items)); } else if (i == 2) { string temp1 = new string('-' 71); string temp2 = | Frequency | Voltage | Measured | Measured | Allowed | Result |; string temp3 = | | | Value | Dev(%) | Dev(%) | |; string temp4 = new string('-' 71); line = String.Format({0}
{1}
{2}
{3} temp1 temp2 temp3 temp4); sw.WriteLine(line); // header lines } else { string[] items = line.Split(''); double freq; double.TryParse(items[0] out freq); if (freq items[0] = String.Format({0 6} Hz freq); else if (freq items[0] = String.Format({0 6}kHz freq/1000); else items[0] = String.Format({0 6}MHz freq/1000000); double voltage; double.TryParse(items[1] out voltage); items[1] = String.Format({0 10:F1} V voltage); double aDev; double.TryParse(items[2] out aDev); items[2] = String.Format({0 15:F3} aDev); double mValue; double.TryParse(items[3] out mValue); mValue *= multiplier; items[3] = String.Format({0 11:F2}pF mValue ); double mDev; double.TryParse(items[4] out mDev); string mDevs = String.Format({0}{1:F3} (mDev >= 0) ? +: mDev); items[4] = String.Format({0 12:F3} mDevs); items[5] = String.Format({0 8} items[5]); sw.WriteLine(String.Format({0}{1}{2}{3}{4}{5} items[0] items[1] items[3] items[4] items[2] items[5])); } i++; } } } } }

TAN WhoAMI · Answer

Hi Vulpes and Arjun thanks for your effort and hard work...it is more or less close but I need to produce the text file to be identical with the JPEG file is it possible? I need the correct alignments as well. Also postive value of Measured Dev (%) needs to have a '+' appended. Voltage needs to be 5.0V 1.0V or 4.0V. I re-attached the JPEG file again with its test1.csv. Take note that there is an additional first row Capacitor Value (F):9.741e-011. also take note that Allowed Deviation (%) on the .csv file is on the 3rd column while the text file it is on the 5th column.

arjun · Answer

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Shapes; using System.IO; namespace WpfTestApp { /// /// Interaction logic for Window2.xaml /// public partial class Window2 : Window { StreamReader sr; StreamWriter tw; public Window2() { InitializeComponent(); tw = new StreamWriter(C:\Users\testFilename.txt); sr = new StreamReader(C:\Users\arjun\Downloads\code\test1.csv); } private void Window_Loaded_1(object sender RoutedEventArgs e) { List data = new List (); while (!sr.EndOfStream) { CsData cs = new CsData(); string ss = sr.ReadLine(); string[] sa = ss.Split(''); cs.frequency = sa[0]; cs.voltage = sa[1]; cs.allowedDev = sa[2]; cs.measuredValue = sa[3]; cs.measuredDev = sa[4]; cs.result = sa[5]; if (cs.measuredValue.Contains('E')) { string[] temp = cs.measuredValue.Split('E'); double d = Convert.ToDouble(temp[0]); string t=temp[1].Substring(1); double dd = d / Math.Pow(10Convert.ToDouble(t)); float fl =(float ) dd * 1000000000000; cs.measuredValue = fl.ToString(); if (cs.frequency.Length > 3 && !cs.frequency.Contains('E')) { int hz = Convert.ToInt32(cs.frequency); double khz = hz * Math.Pow(10 -2); cs.frequency = khz.ToString() +  khz; } else { cs.frequency = cs.frequency +  hz; } } data.Add(cs); } for (int i = 0; i { string rec = data[i].frequency +   + data[i].voltage +   + data[i].measuredValue +   + data[i].measuredDev +   + data[i].allowedDev +   + data[i].result; tw.WriteLine(rec); //need to write data in proper manner. } tw.Close(); } } public class CsData { public string frequency { get; set; } public string voltage { get; set; } public string measuredDev { get; set; } public string measuredValue { get; set; } public string allowedDev { get; set; } public string result { get; set; } } }

Poppa Wallace · Answer

Maybe filehelpers could assist you here http://filehelpers.sourceforge.net/ Jay

Vulpes · Answer

I'm not familar with CsvHelper and so don't know what if any manipulations it can perform. However it's not difficult to do what you've described using 'ordinary' code and then save it to a separate text file so that it doesn't overwrite the original: using System; using System.IO; class Test { static void Main() { double multiplier = Math.Pow(10 12); using(StreamReader sr = new StreamReader(test1.csv)) { using(StreamWriter sw = new StreamWriter(test1a.csv)) { string line; int i = 1; while((line = sr.ReadLine()) != null) { line = line.Replace(\ ); if (i == 1) { sw.WriteLine(line); // header line } else { string[] items = line.Split(''); double freq; double.TryParse(items[0] out freq); if (freq items[0] = String.Format({0} Hz freq); else if (freq items[0] = String.Format({0}kHz freq/1000); else items[0] = String.Format({0}MHz freq/1000000); double mValue; double.TryParse(items[3] out mValue); mValue *= multiplier; items[3] = String.Format({0:F2}pF mValue ); sw.WriteLine(String.Join( items)); } i++; } } } } } The resulting text file test1a.csv looks like this: Frequency (Hz)Voltage (Volts)Allowed Deviation (%)Measured Value (F)Measured Deviation (%)Result 100 Hz56.06897.92pF0.523676PASS 300 Hz52.6998.05pF0.658289PASS 1kHz52.5697.97pF0.573141PASS 1kHz51.9697.60pF0.191519PASS 3kHz52.2197.46pF0.0488924PASS 10kHz53.58897.41pF0.00120819PASS 30kHz51.74497.12pF-0.296046PASS 100kHz51.72897.49pF0.0812877PASS 300kHz53.52396.60pF-0.835146PASS 1MHz510.47897.71pF0.303205PASS 300kHz53.52396.67pF-0.758152PASS 1MHz510.47897.64pF0.232791PASS 300 Hz15.8197.97pF0.57296PASS 1kHz15.84698.18pF0.78955PASS 10kHz12.1197.60pF0.194791PASS 30kHz15.8497.31pF-0.101046PASS 50kHz12.95197.37pF-0.0454047PASS 1MHz110.58198.12pF0.723754PASS 300kHz13.93196.74pF-0.692772PASS 1MHz110.58198.12pF0.72665PASS 3MHz412.457101.03pF3.71295PASS 3MHz111.629101.92pF4.63152PASS