MS Access 2003 Parsing Memo Field VBA


I need to parse a text (MS Access memo field) that is comma separated with double quotation marks as text delimiters AND commas as part of some text entries themselves. Overall data volume is about 300k records with 3800 chars in 500 columns. I need to extract about 50 columns from all records.

Looping through the records themselves and overall data handling is ok. I am now searching for the best performing way to parse through a single text record, figure out if I have the correct column, extract the data and then get the next column from the text. The index and order of the columns is known, of course.

Sample data:
1, "2", 3, "4," ,"5", ",6,a"

Commas after 4 and before and after 6 are NOT delimiters.

For example, I need to extract the second ("2") and sixth (",6,a") column.

Did somebody already solve this or something comparable in some smooth way?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
A bit of a cheat, (and definitely not "Sooth") but this returns what you specified...
Can you post the desired output from your sample?
Are we dealing with elements in which the commas and double quotes are important for the field values?
finarisAuthor Commented:
Hi boag2000,

thanks for your reply, but this is not what I want, since commas being part of the field values (and not meant as field delimiters) are recognized as field delimiters. However, being able to "split" the string into an array at once as suggested would be very good since I then do not need to parse every single line for searching the correct columns but can simply select the correct columns (by id) from the array. Question is now whether it is possible to handle with the additional commas as field values instead of being delimiters. Maybe there is an option to use some weird REGEX with the split function?

Hi jerryb30,

the result for columns 2 and 6 of my sample string should be
"2" for column 2
",6,a" for column 6

Double quotes here are meant to be part of the field value.

The commas are relevant for the field values whereas the double quotes are not relevant in the end. However I can not remove them beforehand because they are the only way for me to find the commas being part of the field values. Not all columns have double quotes (the ones with commas being part of the field values as wel as some others too).

All the best
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
finaris ,

I am confused, you say you want:

    "2" for column 2

    ",6,a" for column 6

This is exactly what my sample shows:
Can you clearly explain *exactly"  what is lacking in my sample?

In other words, instead of explaining what you want in detail, ,...simply post a clear "Graphical" example of the *exact* output you need.
"weird regex" is not going to be faster than parsing the whole line, it may take less code, but won't be faster since the regex is going to be parsing the whole line under the covers anyway. Sometimes with VBA, built in functions make stuff run a lot faster, but parsing a string should be pretty quick (I should hope).

I'm here for the Algorithms zone, so I can't promise perfect syntax, but the parse line sub routine could look something like

Sub Parse(line As String)
  Dim in_quote As Boolean
  Dim i, column As Integer
  column = 1
  For i From 1 to Len(line)
    If line[i] = """" Then
      in_quote = Not in_quote ' flip the value of the boolean
    Else If line[i] = "," And Not in_quote
      column = column +1
    End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Does Access not have a way to handle this? What could be faster would be to open the file in Excel. Tell Excel it is comma separated with " as the text qualifier, then save it in tab separated format and it should open straight into Access with no issues.
finarisAuthor Commented:
Hi boag2000,

you mixed up the delimiter chars, the comma is the separator, not the double quote. And the ",6,a" is the sixth column, not the eights (or should be).

Hi Tommy,

Working with Excel is not an option since I only have 2003 (and I am not allowed to use a different version) and we have more than 65k rows. Furthermore, Excel has the the same problem with the number of columns.

Your first suggestion is what I have implemented today - which is the "long way". However, there are still some issues that have not been understood yet. I will get back to you tomorrow.

Thanks to you guys!
Jeffrey CoachmanMIS LiasonCommented:
Still confused...

You asked for:           ",6,a"
My query displays:  ",6,a"

    ...How is anything "Mixed up"?

Le's for get columns for now, as I can make the values appear in any column you like...

I am not trying to be argumentative here..its just that my query displays the values you asked for...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.