Big issue in split operation

Question

Hi
//This is normal input line which is read from csv file and i want to get value only comma separator (i,e Redhighlighted comma only)
Note:- I cant modify that line example:- remove double quote or add double quote in line

str="12,Goods,"LCD,HDD",V21,HIGH",xyz,pqr,vbc"; //Input line

//I tried this code..I have separated in string array (named as arr) using comma(,) separator.
string[] arr= str.Split(',');

//After split value:- Actual array
arr[12]
arr[Goods]
arr["LCD]
arr[HDD"]
arr[V21]
arr[HIGH"]
arr[xyz]
arr[pqr]
arr[vbc]

//After split value:- Expected array
arr[12]
arr[Goods]
arr["LCD,HDD",V21,HIGH"]
arr[xyz]
arr[pqr]
arr[vbc]

//--------
----------

//Output
// 12 Goods "LCD,HDD",V21,HIGH" xyz pqr vbc

//If it will open in Excel file then it is displaying properly as like above output, but i want that output either in string variable or string array using c# code , so give me appropriate solution for doing that output.
Thanks in advance.

Vulpes · Answer

As long as there is only one field in each row which is surrounded by quotes then you should be able to do it as follows. Otherwise it will be difficult to do in a general way because of possible ambiguity problems as to where one field ends and the next one begins: using System; using System.IO; class Program { static void Main() { string[] rows = File.ReadAllLines(datta.csv); // assume all rows have same number of fields and that only one field // is surrounded by quotes // first find position of initial and final quotes (using first row) int index1 = rows[0].IndexOf(\); int index2 = rows[0].LastIndexOf(\); // separate out fields before the 'quoted' one string sub1 = rows[0].Substring(0 index1 - 1); // split this on the comma separator string[] fields1 = sub1.Split(''); // separate out fields after the 'quoted' one string sub2 = rows[0].Substring(index2 + 2); // split this on the comma separator string[] fields2 = sub2.Split(''); // so total number of fields must be int numFields = fields1.Length + fields2.Length + 1; // and the index of the quoted field must be int quotedIndex = fields1.Length; // we can now process the rows string[] fields = new string[numFields]; // array to hold all fields StreamWriter sw = new StreamWriter(datta2.csv); foreach(string row in rows) { // first find position of initial and final quotes for this row index1 = row.IndexOf(\); index2 = row.LastIndexOf(\); // separate out fields before the 'quoted' one for this row sub1 = row.Substring(0 index1 - 1); // split this on the comma separator fields1 = sub1.Split(''); // separate out fields after the 'quoted' one for this row sub2 = row.Substring(index2 + 2); // split this on the comma separator fields2 = sub2.Split(''); // populate fields array for(int i = 0; i for(int i = quotedIndex + 1; i fields[quotedIndex] = row.Substring(index1 index2 - index1 + 1); string combined = String.Join(  fields); // write to console Console.WriteLine(combined); // write to file sw.WriteLine(combined); } sw.Close(); Console.ReadKey(); } }

Datta Kharad · Answer

Hi I got alternate solution for above problem. private ArrayList SplitOperation(ref string[] sarrSingleLine) { ArrayList alRes = new ArrayList(); string strTemp = string.Empty; bool blnQuote = false; int iQuoteCount = 0; int iCurQuoteCount = 0; string[] arrCurWord; for (int iWord = 0; iWord { if (sarrSingleLine[iWord].StartsWith(\) || blnQuote) { if (string.IsNullOrEmpty(strTemp)) { strTemp = sarrSingleLine[iWord].ToString(); } else { strTemp = strTemp +  + sarrSingleLine[iWord].ToString(); } arrCurWord = sarrSingleLine[iWord].Split('\'); iCurQuoteCount = arrCurWord.Length - 1; iQuoteCount = iQuoteCount + iCurQuoteCount; if (iQuoteCount % 2 == 0) { alRes.Add(strTemp); blnQuote = false; strTemp = string.Empty; } else { blnQuote = true; } } else { alRes.Add(sarrSingleLine[iWord]); } } if (!string.IsNullOrEmpty(strTemp)) { arrCurWord = strTemp.Split(''); foreach (string str in arrCurWord) { alRes.Add( + str); } MessageBox.Show(Line is not Proper Formaat Check it... + strTemp SplitOperation Method MessageBoxButtons.OK MessageBoxIcon.Warning); } return alRes; }

Datta Kharad · Answer

@Vulpes Thanku Sir for giving desired solution. I have all rows have same number of fields and that only one field but that field may be present some rows and may not be present some rows.....doesn't matter in that cases i will get as it is line and lastly it is working properly. Again Thanks Sir.

Datta Kharad · Answer

Hello Sir format can be change...suppose csv file has 7 rows and each row has different format. Example:- USD5GoodsCP188895-XXR LCD 14.1SXGA+ - REPAIRED Each15.00 USD6GoodsCP191238-XXR LCD13.3XGA(Glare)-REPAIRED Each26.00 USD7GoodsCP235669-XXR LCD 17 SAMSUNG - REPAIRED Each20.00 USD8GoodsCP238801-XXR LCD 12IN XGA H - REPAIRED Each60.00 USD1GoodsCP051250-XXR PCBMOTHERBOARD PC-200 - REPAIRED Each1.00 USD3GoodsCP112545-XXR LCDW/TOUCHPANEL10.4TFT/XGA - REPAIRED Each4.00 USD4GoodsCP115323-XXR LCD13.3SANYO XGA - REPAIRED Each1.00 //Output USD 5 Goods CP188895-XXR LCD 14.1SXGA+ - REPAIRED Each 15.00 USD 6 Goods CP191238-XXR LCD13.3XGA(Glare)-REPAIRED Each 26.00 USD 7 Goods CP235669-XXR LCD 17 SAMSUNG - REPAIRED Each 20.00 USD 8 Goods CP238801-XXR LCD 12IN XGA H - REPAIRED Each 60.00 USD 1 Goods CP051250-XXR PCBMOTHERBOARD PC-200 - REPAIRED Each 1.00 USD 3 Goods CP112545-XXR LCDW/TOUCHPANEL10.4TFT/XGA - REPAIRED Each 4.00 USD 4 Goods CP115323-XXR LCD13.3SANYO XGA - REPAIRED Each 1.00 After separated and collect it string or stringbuilder these above output i want to save into database. So how can separate these data properly b'coz Open Office software (Excel) is displaying properly..... Please give me solution its urgent.

Vulpes · Answer

Here's another version of the above program but much less messy (and no Regex): using System; class Test { static void Main() { string str=12Goods\LCDHDD\V21HIGH\xyzpqrvbc; // assume embedded commas will always be in the third of six fields string[] arr = new string[6]; string[] temp = str.Split(''); int count = temp.Length; arr[0] = temp[0]; arr[1] = temp[1]; arr[2] = String.Join( temp 2 count - 5); arr[3] = temp[count - 3]; arr[4] = temp[count - 2]; arr[5] = temp[count - 1]; // check it worked foreach(string s in arr) Console.Write({0}	 s); Console.ReadKey(); }

Vulpes · Answer

It's a bit messy but if we can assume that the embedded quotes and commas will only occur in the third of the six fields then this should work: using System; using System.Text.RegularExpressions; class Test { static void Main() { string str=12Goods\LCDHDD\V21HIGH\xyzpqrvbc; // assume embedded commas will always be in the third of six fields string[] arr = new string[6]; MatchCollection mc = Regex.Matches(str ); // find all commas in the string int count = mc.Count; arr[0] = str.Substring(0 mc[0].Index); arr[1] = str.Substring(mc[0].Index + 1 mc[1].Index - mc[0].Index - 1); arr[2] = str.Substring(mc[1].Index + 1 mc[count - 3].Index - mc[1].Index - 1); arr[3] = str.Substring(mc[count - 3].Index + 1 mc[count - 2].Index - mc[count - 3].Index - 1); arr[4] = str.Substring(mc[mc.Count - 2].Index + 1 mc[count - 1].Index - mc[count - 2].Index - 1); arr[5] = str.Substring(mc[mc.Count - 1].Index + 1); // check it worked foreach(string s in arr) Console.Write({0}	 s); Console.ReadKey(); } }

Pravin Ghadge · Answer

Hi Datta May i know is this same format will remain same for all data.