Big issue in split operation

Datta Kharad

14y3.1k.NET

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.

7 Replies

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

Vulpes14y ago
Accepted 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 < quotedIndex; i++) fields[i] = fields1[i];
for(int i = quotedIndex + 1; i < numFields; i++) fields[i] = fields2[i - quotedIndex - 1];
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();
}
}
0
Datta Kharad14y ago
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 < sarrSingleLine.Length; 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;
}
0
Datta Kharad14y ago
@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.
0
Datta Kharad14y ago
Hello Sir
format can be change...suppose csv file has 7 rows and each row has different format.
Example:-
USD,5,Goods,CP188895-XXR,"LCD, 14.1"",SXGA+ - REPAIRED",Each,15.00
USD,6,Goods,CP191238-XXR,"LCD,13.3,XGA""(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,"PCB,MOTHERBOARD PC-200 - REPAIRED",Each,1.00
USD,3,Goods,CP112545-XXR,"LCD,W/TOUCHPANEL,10.4"",TFT/XGA - REPAIRED",Each,4.00
USD,4,Goods,CP115323-XXR,"LCD,13.3"",SANYO XGA - REPAIRED",Each,1.00

//Output
USD 5 Goods CP188895-XXR "LCD, 14.1"",SXGA+ - REPAIRED" Each 15.00
USD 6 Goods CP191238-XXR "LCD,13.3,XGA""(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 "PCB,MOTHERBOARD PC-200 - REPAIRED" Each 1.00
USD 3 Goods CP112545-XXR "LCD,W/TOUCHPANEL,10.4"",TFT/XGA - REPAIRED" Each 4.00
USD 4 Goods CP115323-XXR "LCD,13.3"",SANYO 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.
0
Vulpes14y ago
Here's another version of the above program but much less messy (and no Regex):

using System;

class Test
{
static void Main()
{
string str="12,Goods,\"LCD,HDD\",V21,HIGH\",xyz,pqr,vbc";

// 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}\t", s);
Console.ReadKey();
}
0
Vulpes14y ago
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="12,Goods,\"LCD,HDD\",V21,HIGH\",xyz,pqr,vbc";

// 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}\t", s);
Console.ReadKey();
}
}
0
Pravin Ghadge14y ago
Hi Datta,

May i know, is this same format will remain same for all data.
0