MS Access 2003 Parsing Memo Field VBA

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-17

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?

Question by:finaris
  • 3
  • 2
  • 2
  • +1
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39485014
A bit of a cheat, (and definitely not "Sooth") but this returns what you specified...
LVL 26

Expert Comment

ID: 39485787
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?

Author Comment

ID: 39486240
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39486845
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.
LVL 37

Accepted Solution

TommySzalapski earned 1500 total points
ID: 39486903
"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

LVL 37

Expert Comment

ID: 39486913
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.

Author Comment

ID: 39487658
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!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39488222
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...


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question