I have large text files that I need to import. I only needed some of the data
from them so I select those lines from the text file and import these. I've
got that part working, but I ran into an issue with some of the columns
having the comma, which I'm using as the delimeter, inside them. Here is an
example:

"Lastname","FirstName","Sales,Marketing","more data...

I had been using the substitute function to remove the quotes before I split
the line with the Split function. However, when I came across this I had to
rethink my solution. Well my thinking hasn't worked too well.

So how can I split these lines, remove the quotes, and keep the comma as
part of the result?

Thanks
Mike

There is an old Proverb : If you can't bring Mohamed to the mountain, bring
the mountain to Mohamed. This is a perfect case of the Proverb. Ths simple
solution is to replace the CSV commas with some other character like a
semicolon (any character not in you data). then seperate the string using
Split but seperate the semicolons.

Sub test()

'Input data
a = "Lastname"",""FirstName"",""Sales,Marketing"",""more data"
'replace comma delimiters with semicolon
'leave other commas in place
'for examble "a","b,c","d" with "a";"b,c";"d"
b = Replace(a, """,""", """;""")
c = Split(b, ";")
'Remove the double quotes
For i = LBound(c) To UBound(c)
c(i) = Replace(c(i), Chr(34), "")
Next i

End Sub


"mikebres" wrote:

> I have large text files that I need to import. I only needed some of the data
> from them so I select those lines from the text file and import these. I've
> got that part working, but I ran into an issue with some of the columns
> having the comma, which I'm using as the delimeter, inside them. Here is an
> example:
>
> "Lastname","FirstName","Sales,Marketing","more data...
>
> I had been using the substitute function to remove the quotes before I split
> the line with the Split function. However, when I came across this I had to
> rethink my solution. Well my thinking hasn't worked too well.
>
> So how can I split these lines, remove the quotes, and keep the comma as
> part of the result?
>
> Thanks
> Mike
>

This isn't wrong, but isn't it premature? Before I advise a workaround like
that, I want to make sure our guy isn't missing a more obvious solution.
This is obviously a file in CSV format, yet he didn't SAY "csv" and he
apparently isn't reading it into Excel as a CSV either, because if he were
the quotes would no longer be in the data. So mikebres, have you the option
of just opening this file in Excel as a csv? because if you do, you'll find
the data already broken down into cells with the quote marks removed for you
and the internal commas preserved.

--- "Joel" wrote:
> There is an old Proverb : If you can't bring Mohamed to the mountain, bring
> the mountain to Mohamed. This is a perfect case of the Proverb. Ths simple
> solution is to replace the CSV commas with some other character like a
> semicolon (any character not in you data). then seperate the string using
> Split but seperate the semicolons.
>
> Sub test()
>
> 'Input data
> a = "Lastname"",""FirstName"",""Sales,Marketing"",""more data"
> 'replace comma delimiters with semicolon
> 'leave other commas in place
> 'for examble "a","b,c","d" with "a";"b,c";"d"
> b = Replace(a, """,""", """;""")
> c = Split(b, ";")
> 'Remove the double quotes
> For i = LBound(c) To UBound(c)
> c(i) = Replace(c(i), Chr(34), "")
> Next i
>
> End Sub
>
> --- "mikebres" wrote:
> > I have large text files that I need to import. I only needed some of the data
> > from them so I select those lines from the text file and import these. I've
> > got that part working, but I ran into an issue with some of the columns
> > having the comma, which I'm using as the delimeter, inside them. Here is an
> > example:
> >
> > "Lastname","FirstName","Sales,Marketing","more data...
> >
> > I had been using the substitute function to remove the quotes before I split
> > the line with the Split function. However, when I came across this I had to
> > rethink my solution. Well my thinking hasn't worked too well.
> >
> > So how can I split these lines, remove the quotes, and keep the comma as
> > part of the result?

In the posting he said he had a very large file. There as a previous posting
from the author. The previous postingsaid he had a very large file and
wanted to filter the data before it was read into excel. I gave him a
solution to open a text file whre the code read one line at a time and
performed the split on each line. the split method doesn't work like Text to
Column method which ignores the commas inbetween th edouble quote. then use
the split dat to perform his filtering.

I could of given him a solution where he put each line into a worksheet and
did a Text to Column to sperate the data. I thought my method was simplier.

That is why I refered to the Mohamed Proverb. It basically says when an
easy method starts to become very difficult look at a different approach.

"Bob Bridges" wrote:

> This isn't wrong, but isn't it premature? Before I advise a workaround like
> that, I want to make sure our guy isn't missing a more obvious solution.
> This is obviously a file in CSV format, yet he didn't SAY "csv" and he
> apparently isn't reading it into Excel as a CSV either, because if he were
> the quotes would no longer be in the data. So mikebres, have you the option
> of just opening this file in Excel as a csv? because if you do, you'll find
> the data already broken down into cells with the quote marks removed for you
> and the internal commas preserved.
>
> --- "Joel" wrote:
> > There is an old Proverb : If you can't bring Mohamed to the mountain, bring
> > the mountain to Mohamed. This is a perfect case of the Proverb. Ths simple
> > solution is to replace the CSV commas with some other character like a
> > semicolon (any character not in you data). then seperate the string using
> > Split but seperate the semicolons.
> >
> > Sub test()
> >
> > 'Input data
> > a = "Lastname"",""FirstName"",""Sales,Marketing"",""more data"
> > 'replace comma delimiters with semicolon
> > 'leave other commas in place
> > 'for examble "a","b,c","d" with "a";"b,c";"d"
> > b = Replace(a, """,""", """;""")
> > c = Split(b, ";")
> > 'Remove the double quotes
> > For i = LBound(c) To UBound(c)
> > c(i) = Replace(c(i), Chr(34), "")
> > Next i
> >
> > End Sub
> >
> > --- "mikebres" wrote:
> > > I have large text files that I need to import. I only needed some of the data
> > > from them so I select those lines from the text file and import these. I've
> > > got that part working, but I ran into an issue with some of the columns
> > > having the comma, which I'm using as the delimeter, inside them. Here is an
> > > example:
> > >
> > > "Lastname","FirstName","Sales,Marketing","more data...
> > >
> > > I had been using the substitute function to remove the quotes before I split
> > > the line with the Split function. However, when I came across this I had to
> > > rethink my solution. Well my thinking hasn't worked too well.
> > >
> > > So how can I split these lines, remove the quotes, and keep the comma as
> > > part of the result?

Thanks all,

Bob, Yes, it is a csv. No I can't open the entire file in Exce as it is too
large, 507k lines of text.

Joel, I think I understand yuour approach, but wouldn't that also replace
the comma I don't want replaced?

Mike

"Bob Bridges" wrote:

> This isn't wrong, but isn't it premature? Before I advise a workaround like
> that, I want to make sure our guy isn't missing a more obvious solution.
> This is obviously a file in CSV format, yet he didn't SAY "csv" and he
> apparently isn't reading it into Excel as a CSV either, because if he were
> the quotes would no longer be in the data. So mikebres, have you the option
> of just opening this file in Excel as a csv? because if you do, you'll find
> the data already broken down into cells with the quote marks removed for you
> and the internal commas preserved.
>
> --- "Joel" wrote:
> > There is an old Proverb : If you can't bring Mohamed to the mountain, bring
> > the mountain to Mohamed. This is a perfect case of the Proverb. Ths simple
> > solution is to replace the CSV commas with some other character like a
> > semicolon (any character not in you data). then seperate the string using
> > Split but seperate the semicolons.
> >
> > Sub test()
> >
> > 'Input data
> > a = "Lastname"",""FirstName"",""Sales,Marketing"",""more data"
> > 'replace comma delimiters with semicolon
> > 'leave other commas in place
> > 'for examble "a","b,c","d" with "a";"b,c";"d"
> > b = Replace(a, """,""", """;""")
> > c = Split(b, ";")
> > 'Remove the double quotes
> > For i = LBound(c) To UBound(c)
> > c(i) = Replace(c(i), Chr(34), "")
> > Next i
> >
> > End Sub
> >
> > --- "mikebres" wrote:
> > > I have large text files that I need to import. I only needed some of the data
> > > from them so I select those lines from the text file and import these. I've
> > > got that part working, but I ran into an issue with some of the columns
> > > having the comma, which I'm using as the delimeter, inside them. Here is an
> > > example:
> > >
> > > "Lastname","FirstName","Sales,Marketing","more data...
> > >
> > > I had been using the substitute function to remove the quotes before I split
> > > the line with the Split function. However, when I came across this I had to
> > > rethink my solution. Well my thinking hasn't worked too well.
> > >
> > > So how can I split these lines, remove the quotes, and keep the comma as
> > > part of the result?

Joel

I got a chance to try your solution. It did the job! Thanks.

Mike

"Joel" wrote:

> There is an old Proverb : If you can't bring Mohamed to the mountain, bring
> the mountain to Mohamed. This is a perfect case of the Proverb. Ths simple
> solution is to replace the CSV commas with some other character like a
> semicolon (any character not in you data). then seperate the string using
> Split but seperate the semicolons.
>
> Sub test()
>
> 'Input data
> a = "Lastname"",""FirstName"",""Sales,Marketing"",""more data"
> 'replace comma delimiters with semicolon
> 'leave other commas in place
> 'for examble "a","b,c","d" with "a";"b,c";"d"
> b = Replace(a, """,""", """;""")
> c = Split(b, ";")
> 'Remove the double quotes
> For i = LBound(c) To UBound(c)
> c(i) = Replace(c(i), Chr(34), "")
> Next i
>
> End Sub
>
>
> "mikebres" wrote:
>
> > I have large text files that I need to import. I only needed some of the data
> > from them so I select those lines from the text file and import these. I've
> > got that part working, but I ran into an issue with some of the columns
> > having the comma, which I'm using as the delimeter, inside them. Here is an
> > example:
> >
> > "Lastname","FirstName","Sales,Marketing","more data...
> >
> > I had been using the substitute function to remove the quotes before I split
> > the line with the Split function. However, when I came across this I had to
> > rethink my solution. Well my thinking hasn't worked too well.
> >
> > So how can I split these lines, remove the quotes, and keep the comma as
> > part of the result?
> >
> > Thanks
> > Mike
> >