Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I seperate Imported text with delimeter as part of the par
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I seperate Imported text with delimeter as part of the par
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"",""mo re 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I seperate Imported text with delimeter as part of the
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"",""mo re 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I seperate Imported text with delimeter as part of the
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"",""mo re 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I seperate Imported text with delimeter as part of the
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"",""mo re 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I seperate Imported text with delimeter as part of the
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"",""mo re 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data, Text to Columns, has commas in the text, plus as a delimeter | Excel Discussion (Misc queries) | |||
In Excel, how do I seperate data that is imported into one column | Excel Discussion (Misc queries) | |||
how to seperate the the content of a cell by "," delimeter | Excel Discussion (Misc queries) | |||
text wrap delimeter | Excel Discussion (Misc queries) | |||
imported doc from pdf file, want to seperate text & date in cells | Excel Discussion (Misc queries) |