Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 5 votes, 5.00 average. Display Modes
Old 09-03-2013, 01:56 PM   #1
TeeJay
Newly Registered User
 
Join Date: Sep 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
TeeJay is on a distinguished road
Using VBA to split data within a record into multiple fields?

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~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"

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

TeeJay is offline   Reply With Quote
Old 09-03-2013, 02:03 PM   #2
GohDiamond
"Access- Imagineer that!"
 
GohDiamond's Avatar
 
Join Date: Nov 2006
Location: Hartford, CT
Posts: 548
Thanks: 17
Thanked 48 Times in 46 Posts
GohDiamond is on a distinguished road
Re: Using VBA to split data within a record into multiple fields?

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 "
GohDiamond is offline   Reply With Quote
Old 09-03-2013, 02:13 PM   #3
TeeJay
Newly Registered User
 
Join Date: Sep 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
TeeJay is on a distinguished road
Re: Using VBA to split data within a record into multiple fields?

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).

TeeJay is offline   Reply With Quote
Old 09-04-2013, 05:18 AM   #4
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: Using VBA to split data within a record into multiple fields?

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
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
Old 09-04-2013, 07:14 AM   #5
TeeJay
Newly Registered User
 
Join Date: Sep 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
TeeJay is on a distinguished road
Re: Using VBA to split data within a record into multiple fields?

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.
TeeJay is offline   Reply With Quote
Old 09-04-2013, 07:18 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Using VBA to split data within a record into multiple fields?

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 ,.
jdraw is offline   Reply With Quote
Old 09-04-2013, 07:26 AM   #7
TeeJay
Newly Registered User
 
Join Date: Sep 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
TeeJay is on a distinguished road
Re: Using VBA to split data within a record into multiple fields?

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.

TeeJay is offline   Reply With Quote
Old 09-05-2013, 08:33 AM   #8
tranchemontaigne
Newly Registered User
 
Join Date: Aug 2008
Location: New Hampshire, USA
Posts: 203
Thanks: 0
Thanked 2 Times in 2 Posts
tranchemontaigne is on a distinguished road
Re: Using VBA to split data within a record into multiple fields?

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.

__________________
If you find a response helpful, please click on the scale at the top of the post to add to the contributor's reputation.

Last edited by tranchemontaigne; 09-05-2013 at 08:50 AM.
tranchemontaigne is offline   Reply With Quote
Reply

Tags
access , fields , split , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data from multiple fields in same record in combo box dropdown markzaal Forms 1 03-12-2013 07:52 AM
Split multiple fields into individual records skwilliams Queries 1 07-12-2011 07:05 PM
Help Split data of 1 field into 4 different fields jdawg_1989 Queries 12 04-09-2011 02:04 PM
split record into multiple records by query Gkynard Queries 1 08-17-2009 06:55 AM
Split data into many fields echorley Modules & VBA 3 12-09-2008 01:38 PM




All times are GMT -8. The time now is 06:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World