Substraction from 2 Fields, out of two .csv Files

Question

Hello all,
i got help by Vulpes in this Thread->

http://www.c-sharpcorner.com/Forums/Thread/205494/string-split-how-to-calculate-slot.aspx

I wanna rewrite the Code from Vulpes, but dont now how to do that with two files.

I have a FIRST File that looks like that: (crap Export i made)

"";"";0
" 09000";"Catalog 1";10148
" 09001";"Catalog 2";20
" 09002";"Catalog 3";166

I have a SECOND File that looks like that:
" 09000";476
" 09001";10
" 09005";5

What to do? the Output file must look like that:
09000~Catalog 1~9672
09001~Catalog 2~10
09002~Catalog 3~161

I need to substract Field 3 from the FIRST File, with Field 2 from the SECOND File and get a Output like above.

Vulpes · Answer

Sorry, I'd forgotten that you were specifying the default encoding in the StreamReader constructor to deal properly with non-ASCII characters. Anyway, I've highlighted the changes that will be needed to get the output file as you want it: string spaces = new string(' ', 15); using(StreamReader reader = new StreamReader(filePath2)) { string line; while((line = reader.ReadLine()) != null) { string[] items = line.Replace("\"", "").Trim().Split(';'); if (items[0] != "" && !dict.ContainsKey(items[0])) dict.Add(items[0], items[1]); } } // now read first file, try and match up with dictionary and put results in third file using(StreamWriter writer = new StreamWriter(@"C:/WEBUPLOAD/UPLOAD/artikelbestand.xls")) { writer.WriteLine(";0"); using(StreamReader reader = new StreamReader(@"C:/WEBUPLOAD/EXPORT_VON_CL/.art", System.Text.Encoding.Default)) { string line; while((line = reader.ReadLine()) != null) { string[] items = line.Replace("\"", "").Trim().Split(';'); if (items[0] == "") continue; if(dict.ContainsKey(items[0])) { int i1, i2; bool b1 = int.TryParse(items[2], out i1); bool b2 = int.TryParse(dict[items[0]], out i2); if(!b1 || !b2) continue; items[2] = (i1 - i2).ToString(); } line = spaces + String.Format("{0};{1}", items[0], items[2]); writer.WriteLine(line); } } }

Vulpes · Answer

OK, see if this will work: var dict = new Dictionary (); // read second file first (2 fields) and put data in a dictionary using(StreamReader reader = new StreamReader(filePath2)) { string line; while((line = reader.ReadLine()) != null) { string[] items = line.Replace("\"", "").Trim().Split(';'); if (items[0] != "" && !dict.ContainsKey(items[0])) dict.Add(items[0], items[1]); } } // now read first file, try and match up with dictionary and put results in third file using(StreamWriter writer = new StreamWriter(filePath3)) { using(StreamReader reader = new StreamReader(filePath1)) { string line; while((line = reader.ReadLine()) != null) { string[] items = line.Replace("\"", "").Trim().Split(';'); if (items[0] == "") continue; if(dict.ContainsKey(items[0])) { int i1, i2; bool b1 = int.TryParse(items[2], out i1); bool b2 = int.TryParse(dict[items[0]], out i2); if(!b1 || !b2) continue; items[2] = (i1 -i2).ToString(); } line = String.Join("~", items); writer.WriteLine(line); } } }

s h · Answer

Very good work, fast and excaktly what i needed. For your Code (just to let u know, i think u know that already, maybe forgoten, for the rest, Encoding @ Reader must be set like here: using (StreamReader reader = new StreamReader(@"file", System.Text.Encoding.Default)) For special character s. Last Question (How to do that)? I need to output a Third file, that must look like that: ;0 09000;10008 09001;0 09002;166 09003;383 But without the middle "Catalog" and with the spaces at start (15). Same Input Files. i tried with that using (StreamWriter writer = new StreamWriter(@"C:/WEBUPLOAD/UPLOAD/artikelbestand.xls")) { using (StreamReader reader = new StreamReader(@"C:/WEBUPLOAD/EXPORT_VON_CL/.art", System.Text.Encoding.Default)) { string line; while ((line = reader.ReadLine()) != null) { string[] items = line.Replace("\"", "").Split(';'); if (items[0] == "") continue; if (dict.ContainsKey(items[0])) { int i1, i2; bool b1 = int.TryParse(items[2], out i1); bool b2 = int.TryParse(dict[items[0]], out i2); if (!b1 || !b2) continue; items[2] = (i1 - i2).ToString(); } line = String.Join(";", items); writer.WriteLine(line); } This comes out: 09000;Catalog 1;10019 09001;Catalog 2;0 Now i only need to remove the middle

s h · Answer

It`s safe that the first file always starts with "";"";0 , also it should be always like that. or should the program allow for the possibility that this may not necessarily be the case? -> u can do that if u think that is needed..

Vulpes · Answer

Another quick question: Is it safe to assume that there will always be a line in the second file whose stock number corresponds with that in the first file, or should the program allow for the possibility that this may not necessarily be the case? For example, if file 1 contains a line with 09012, can we be certain that the second file will as well?

s h · Answer

1. There r everytime 3 Fields in the first File and the Second File always 2 Fields. 2.If it get ignored in the First file, it must be ignored in the Second too, what u see r Stocks columns. Important is that the real Line get substracted, Indiz is the Number at start. 09000 09001 3. Sry that was a write mistake. should b the same number like in the First File. (corrected it above)

Vulpes · Answer

Some questions: 1. Can it be assumed that each line in both files will have exactly 3 fields? 2. Should the first line in the second file be ignored or does this only apply to the first file? 3. Does it matter that the 'serial numbers' of the corresponding lines in each file don't match? For example, 10 is being subtracted from 20 even though the serial numbers are 09001 in the first file and 09003 in the second file.

Substraction from 2 Fields, out of two .csv Files

Answers (7)