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

TAN WhoAMI

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.

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted 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}\r\n{1}\r\n{2}\r\n{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 < 1)
{
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 < 1)
{
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("\n\n");
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 WhoAMI13y ago
cheers, thanks for your verification.
Vulpes13y ago
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 WhoAMI13y ago
yes, what you say is true...I still could not figure out how they produce more accurate values than the zip .csv file...
Vulpes13y ago
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 WhoAMI13y ago
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 WhoAMI13y ago
THANK YOU VERY MUCH, Vulpes
TAN WhoAMI13y ago
Hi Vulpes,

I try with the attached code, but could not produce what is desired from the picture file. Could you help? thanks.
TAN WhoAMI13y ago
EXACTLY what I need, thanks a lot Vulpes, again :)
Vulpes13y ago
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}\r\n{1}\r\n{2}\r\n{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 < 1000)
items[0] = String.Format("{0, 6} Hz", freq);
else if (freq < 1000000)
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 WhoAMI13y ago
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.
arjun13y ago
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(10,Convert.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 < data.Count; 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 Wallace13y ago
Maybe filehelpers could assist you here

http://filehelpers.sourceforge.net/

Jay
Vulpes13y ago
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 < 1000)
items[0] = String.Format("{0} Hz", freq);
else if (freq < 1000000)
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 Hz,5,6.068,97.92pF,0.523676,PASS
300 Hz,5,2.69,98.05pF,0.658289,PASS
1kHz,5,2.56,97.97pF,0.573141,PASS
1kHz,5,1.96,97.60pF,0.191519,PASS
3kHz,5,2.21,97.46pF,0.0488924,PASS
10kHz,5,3.588,97.41pF,0.00120819,PASS
30kHz,5,1.744,97.12pF,-0.296046,PASS
100kHz,5,1.728,97.49pF,0.0812877,PASS
300kHz,5,3.523,96.60pF,-0.835146,PASS
1MHz,5,10.478,97.71pF,0.303205,PASS
300kHz,5,3.523,96.67pF,-0.758152,PASS
1MHz,5,10.478,97.64pF,0.232791,PASS
300 Hz,1,5.81,97.97pF,0.57296,PASS
1kHz,1,5.846,98.18pF,0.78955,PASS
10kHz,1,2.11,97.60pF,0.194791,PASS
30kHz,1,5.84,97.31pF,-0.101046,PASS
50kHz,1,2.951,97.37pF,-0.0454047,PASS
1MHz,1,10.581,98.12pF,0.723754,PASS
300kHz,1,3.931,96.74pF,-0.692772,PASS
1MHz,1,10.581,98.12pF,0.72665,PASS
3MHz,4,12.457,101.03pF,3.71295,PASS
3MHz,1,11.629,101.92pF,4.63152,PASS