Using VBA to split data within a record into multiple fields? (1 Viewer)

TeeJay

New member
Local time
Today, 11:22
Joined
Sep 3, 2013
Messages
4
Hi,

I am completely new to VBA (never used it before) and I've come up against an issue that I just can't figure out.

I have an access table that looks like the below:

"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~field7"
"field1~field2~field3~field4~field5~field6~field7"
"field1~field2~field3~field4~field5~field6~field7"
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~field7"

I've got a module which loads the data into a table, but it can't handle the records that are ~ delimited, so spits them out as a single field.

I know there is some VBA code that can be used to split comma delimited records, and I've seen bits of it floating around online and tried to alter it to work for the ~, but I get the feeling that what I've seen is only a part of the required code and have no idea how to put it all together to make something that actually works.

So, what I need to do is;
- Split some records in a table out into multiple fields where there is a ~ present
- Place these newly split fields into a table (I don't mind if it ends up in a new table or not)

Can anybody give me the layman's view on how to get this working?

Thanks in advance
 

GohDiamond

"Access- Imagineer that!"
Local time
Today, 07:22
Joined
Nov 1, 2006
Messages
550
What kind of file is it? txt, csv? maybe you need to adjust your import specification to account for the exceptions. I had incoming data that had extra commas within the text in the field being imported. It split the data to the left of the comma and to the right of the comma into different fields. I finally resolved it by using a text delimiter of "
 

TeeJay

New member
Local time
Today, 11:22
Joined
Sep 3, 2013
Messages
4
they are .csv's

I discussed that with someone who knows a bit more about VBA than I do, but he reckons that would just cause issues with the comma delimited fields (they're coming in via the same files as the ~ delimited fields, which is why I'm having such a problem).
 

DavidAtWork

Registered User.
Local time
Today, 11:22
Joined
Oct 25, 2011
Messages
699
are you saying the csv file uses a ~ as a delimiter, if so start by doing a manual import and once the wizard opens choose the 'delimited' as the type and 'Other' as the delimiter and type a ~ in the 'other box', if there is a text qualifier, select from the list, then click on the advanced button and set any other specifications and save the specifications with a meaningful name. Import the file.
Future files can be imported using the

Code:
DoCmd.TransferText acImportDelim, "yourSpecs", "yourImportTable", "C:\yourFolder\yourFileName.csv", True 'if it has headers, False if not

David
 

TeeJay

New member
Local time
Today, 11:22
Joined
Sep 3, 2013
Messages
4
No. The csvs are comma delimited but for some reason some of the records within each file are output with ~ instead (the example in my first post is from one file, note the comma delimited rows and rows with ~ in) also, because the individual fields are not encapsulated within their own set of text qualifiers (again, see the example in my first post) I can't split the fields by setting a text qualifier within the access import. What I need is a piece of vba code which can split the records with ~ in them post-import.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:22
Joined
Jan 23, 2006
Messages
15,364
What is the source of these .csv files? Perhaps a small adjustment to the program/routine that supplies the data can make all records with " and ,.
 

TeeJay

New member
Local time
Today, 11:22
Joined
Sep 3, 2013
Messages
4
We are working on that but the files come from an outside source on a daily basis so, until the fix is in place at their end they will still be in this dodgy format.
 

tranchemontaigne

Registered User.
Local time
Today, 04:22
Joined
Aug 12, 2008
Messages
203
TeeJay,

This kind of data structure is not that uncommon. Look at any HL7 or X.12 data file.

If you think about it this kind of structure makes sense. For example you might want to store a complete address in a single field. The address is defined as a collection of other elements such as street number, street name, town, city, postal code that you might later want to use separately to either search, filter or group collections of addresses.

NOTE: If address information is missing, then the primary address field would be empty and you would not have to insert fill characters such as commas for space holders. If you get this concept, then you should be better able to think about and solve this kind of problem.


Your real task here is to write a data parser.


RECOMMENDATION:
First use the SPLIT function to identify primary fields (in your xcase they would be separated by commas) and store those in variables.

You could then loop through each of those variables that you know are composed of a collection of other values using another call to the SPLIT function call using the next tier of field delimiters (e.g. "~") and store those sub field values in additional variables.

Once you have all your variables set you would dynamically write a INSERT INTO...VALUES statement and execute the resulting SQL to write teh parsed data values into a table.

As always, you can save yourself a lot of headache by making sure you initialize all variables before using them. This will prevent you from assuming that a variable won't already contain a value just because you are calling a variable for the first time.
 
Last edited:

Users who are viewing this thread

Top Bottom