How do I split this? RSS

18 replies

Last post Oct 18, 2006 01:41 PM by patrick24601

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
patrick24601
Member

83 Points

244 Posts

How do I split this?

Oct 07, 2006 10:23 PM|patrick24601|LINK

I'd like some help splitting a string please. My data is CSV, has quotes around each field, and there are actual commas in the data. So a plain split on a commas will not work.

Here is an example line of data

"SKU","Description","Price"

"3030495","This is a great product, at a great price","100.00"

Thanks for any help. I've been wrestling with this for awhile.

Patrick

asp.net C#

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
StrongTypes
Contributor

6294 Points

5754 Posts

ASPInsiders

Re: How do I split this?

Oct 07, 2006 10:58 PM|StrongTypes|LINK
You could split it on "," and then remove the " at the beginning and end.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 07, 2006 11:06 PM|patrick24601|LINK
Thanks... but that is where I need help. I know how to split on a single character. How do I split on three - especially with a quote in there?

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
StrongTypes
Contributor

6294 Points

5754 Posts

ASPInsiders

Re: How do I split this?

Oct 07, 2006 11:12 PM|StrongTypes|LINK

Sorry about that. I was thinking of the String.Replace method. It looks like your best bet is to use a regular expression. See http://www.regexlib.com for more help with regular expressions.
- Reply
StrongTypes
Contributor

6294 Points

5754 Posts

ASPInsiders

Re: How do I split this?

Oct 07, 2006 11:20 PM|StrongTypes|LINK

Actually, now that I think about it, there is an overload of the String.Split method you could use.

string csv = "\"1\",\"2\",\"3\"";
string[] matches = new string[] {"\",\""};
string[] csvArray = csv.Split(matches, StringSplitOptions.None);
csvArray[0] = csvArray[0].Replace("\"", string.Empty);
csvArray[2] = csvArray[2].Replace("\"", string.Empty);
string string1 = csvArray[0];
string string2 = csvArray[1];
string string3 = csvArray[2];
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 07, 2006 11:39 PM|patrick24601|LINK

Thanks. I was also thinking I could just do a .Replace on "," with | or ~ and split on that single character.

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 12:48 AM|patrick24601|LINK

Now I need some additional help if anybody could spare it :)

I found another datafeed I have to import where all of textual/string items have quotes, but the numbers do not:

"Product Name","Product HTML Description",8.99,4.99

How would I handle that one?

Thanks,

Patrick

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
vcsjones
All-Star

24872 Points

4336 Posts

ASPInsiders
Moderator
MVP
Re: How do I split this?

Oct 17, 2006 01:07 AM|vcsjones|LINK
What you can do is split it with just the comma (,), then loop through the array of strings and trim the quotes off, like this:
```
string input = "\"Product Name\",\"Product HTML Description\",8.99,4.99";
string[] inputs = Array.ConvertAll<string, string>(input.Split(','), delegate(string _in) { return _in.Trim('"'); });
```
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 08:34 AM|patrick24601|LINK

I am not sure if you saw some of my earlier posts, but my textual data can have commas in it also. So a better example of my data is:

"Product Name","Product Description, which can have commas",8.99,34.33,"Excellent Rating"

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
vcsjones
All-Star

24872 Points

4336 Posts

ASPInsiders
Moderator
MVP

Re: How do I split this?

Oct 17, 2006 12:17 PM|vcsjones|LINK
yes I saw that, and the code I gave you will split it and remove the quotes if it does have quotes at the end. It will work.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 12:45 PM|patrick24601|LINK
Thanks. I'll get this in and let you know how it works ok. I've never used the array.convertall().

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.

vladi

Member

50 Points

51 Posts

Re: How do I split this?

Oct 17, 2006 04:19 PM|vladi|LINK

or:

string s = @"""3030495"",""This is a great product, at a great price"",""100.00""";
Regex reg = new Regex(@""",""", RegexOptions.Compiled | RegexOptions.IgnoreCase);
string[] arr = reg.Split(s.Trim('"'));


foreach(string current in arr){
    Console.WriteLine("&gt;>" + current);
}

- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 05:20 PM|patrick24601|LINK

Kevin,

It did not work. WHen it encountered a comma in the middle of my data it split it there.

"text field 1","Text field 2, plus some more" <--- it splits right after the 2.

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
vcsjones
All-Star

24872 Points

4336 Posts

ASPInsiders
Moderator
MVP

Re: How do I split this?

Oct 17, 2006 05:24 PM|vcsjones|LINK

In that case you would have to use a regular expression to split it.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 06:41 PM|patrick24601|LINK
I tried the regular expression one above also and that one did not work. I did not realize that this was this hard!

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 06:53 PM|patrick24601|LINK

I am going to look into using this:

http://www.codeproject.com/cs/database/CsvReader.asp

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 17, 2006 07:14 PM|patrick24601|LINK
Yep... that csv reader handled it very well. Had it running in my project in about 15 minutes. Highly recommended.

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.
- Reply
JeffreyABeck...
Contributor

3938 Points

3276 Posts

Re: How do I split this?

Oct 18, 2006 12:56 PM|JeffreyABecker|LINK
You know you can open delimited text as a Database through the Jet driver right?

If the answer I provided is useful or informative please check the "answer" button.

Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
- Reply
patrick24601
Member

83 Points

244 Posts

Re: How do I split this?

Oct 18, 2006 01:41 PM|patrick24601|LINK

Would it handle the delimiter being in the data as well as delimiting it? In the examples we've given above there are commas in my data, and the comma is the delimiter also. Quotes surround the data that has the commas in it.

Example: "Product Name", "Product Descriptions, sometimes with commas", 8.99, 23.44, "And possibly even some more text there"

PatrickAllmond.com.
Information Technology Solutions
Helping You Focus. Helping You Succeed.

‹ Previous Thread|Next Thread ›