Comma with in CSV file
-
-
Dear Developers, I'm importing CSV file to sql DB using ASP.net. It works fine. But ,If a Cell contain a value like john,inc. CSV take it as two values as john and inc But i want in a single value as john,inc .. How can i do thatThanks in Regards, Prajin
For this kind of value you have to use ' or " to enclose the Value Ex// 1,'john,store',aroda
Best Regards, Chetan Patel
-
For this kind of value you have to use ' or " to enclose the Value Ex// 1,'john,store',aroda
Best Regards, Chetan Patel
-
Hi Chetan, This did not work when I read "1,'john,store',aroda" from file and split it on comma to convert to a datatable, "john" and "store" are coming in different columns. I want this in same column. Regards, Prajin
Can u please post the code??
-
Can u please post the code??
try { memStream = new MemoryStream(fileImport.FileBytes); reader = new StreamReader((Stream)memStream); int nCurrPosition = 0; int nNoOfCharactersToBeRead = Convert.ToInt32(reader.BaseStream.Length) - nCurrPosition; szRawString = ""; if (nNoOfCharactersToBeRead >= 0) { reader.BaseStream.Seek(0, SeekOrigin.Begin); char[] vBuffer = new char[nNoOfCharactersToBeRead]; reader.Read(vBuffer, 0, nNoOfCharactersToBeRead); if (vBuffer.Length != 0) { szRawString = ReArrangeStringEx(vBuffer); } } if (szRawString != "") { szRawString = szRawString.Replace("\r", ""); string[] rows = szRawString.Split("\n".ToCharArray()); for (int j = 0; j < rows.Length; j++) { string[] columns = rows[j].Split(','); if (dtCustomer.Columns.Count == columns.Length) { dtCustomer.Rows.Add(columns); } } } reader.Close(); memStream.Close();
-
try { memStream = new MemoryStream(fileImport.FileBytes); reader = new StreamReader((Stream)memStream); int nCurrPosition = 0; int nNoOfCharactersToBeRead = Convert.ToInt32(reader.BaseStream.Length) - nCurrPosition; szRawString = ""; if (nNoOfCharactersToBeRead >= 0) { reader.BaseStream.Seek(0, SeekOrigin.Begin); char[] vBuffer = new char[nNoOfCharactersToBeRead]; reader.Read(vBuffer, 0, nNoOfCharactersToBeRead); if (vBuffer.Length != 0) { szRawString = ReArrangeStringEx(vBuffer); } } if (szRawString != "") { szRawString = szRawString.Replace("\r", ""); string[] rows = szRawString.Split("\n".ToCharArray()); for (int j = 0; j < rows.Length; j++) { string[] columns = rows[j].Split(','); if (dtCustomer.Columns.Count == columns.Length) { dtCustomer.Rows.Add(columns); } } } reader.Close(); memStream.Close();
Well you're not operating on this as a CSV (which would honour the quotes as a text identifier) - you're simply splitting the string using comma:
string[] columns = rows[j].Split(',');If you want to roll your own code to read from a CSV then you'll need to build something into this line above that honours quotes as a text identifier. -
try { memStream = new MemoryStream(fileImport.FileBytes); reader = new StreamReader((Stream)memStream); int nCurrPosition = 0; int nNoOfCharactersToBeRead = Convert.ToInt32(reader.BaseStream.Length) - nCurrPosition; szRawString = ""; if (nNoOfCharactersToBeRead >= 0) { reader.BaseStream.Seek(0, SeekOrigin.Begin); char[] vBuffer = new char[nNoOfCharactersToBeRead]; reader.Read(vBuffer, 0, nNoOfCharactersToBeRead); if (vBuffer.Length != 0) { szRawString = ReArrangeStringEx(vBuffer); } } if (szRawString != "") { szRawString = szRawString.Replace("\r", ""); string[] rows = szRawString.Split("\n".ToCharArray()); for (int j = 0; j < rows.Length; j++) { string[] columns = rows[j].Split(','); if (dtCustomer.Columns.Count == columns.Length) { dtCustomer.Rows.Add(columns); } } } reader.Close(); memStream.Close();
I suppose u first split your string by ' and then split it by comma. Hope it works.
-
Dear Developers, I'm importing CSV file to sql DB using ASP.net. It works fine. But ,If a Cell contain a value like john,inc. CSV take it as two values as john and inc But i want in a single value as john,inc .. How can i do thatThanks in Regards, Prajin
for (int j = 0; j < rows.Length; j++) { string[] columns = rows[j].Split(','); if (dtCustomer.Columns.Count == columns.Length) { dtCustomer.Rows.Add(columns); } } change your code like this arrTemp[]=rows[j].Split("'") you get three parts 1,'abc,def',asd as 1, abc,def ,asd now add this like this for (int j = 0; j < rows.Length; j++) { arrTemp[]=rows[j].Split("'") dtCustomer.Rows.Add(arrTemp[0].subString(1,arrTemp[0].Length-1); dtCustomer.Rows.Add(arrTemp[1]); dtCustomer.Rows.Add(arrTemp[2].subString(2); }
Best Regards, Chetan Patel