By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Parse / split number into multiptle records

P: 3
Hello,

I'm a novice to VBA and need to import raw data into an access database. I created the table, it contains 87 fields, all numeric.

The data I have looks like this:

01390681068168120110301002936140400000000000000000 3000000100000000000000000000
0239068106816812011030100293603600541022867+000100 0+000000650+0000000000000067721292120000+0000000+0 000000+0000000+0000000+0000000+0000000+0000000+000 0000+0000000+0000000+0000000-0002000000000000000000
10390681068168120110301002936000067720000002000000 006500000000000000000000
03390681068168120110301002936001000000000000000000 000000000000000000000000004500100151 3000000000202000000045000000
03390681068168120110301002936001000000000000000000 060045202000000000000000002001413989 3000000000201000000020000000
85390681068168120110301002936140400+00000450

This is one record for one transaction. Problems:
- One transaction consists of 1 Header, X Transaction Details and Y Payment Details + 1 End Record.
Each section always contain the same number of fields.

To highlight the structure:
(A) Header -> (01 is the ID for the Header, 390 would be a store, etc.)
(01)(390)(681)(0681)(681)(20110301)(002936)(1404)( 00000000)(0)(00000000)(3)(000000)(1)(0)(0000000000 000000000)

(B) Transaction Details -> (02 id the ID for trans details)
(02)(390)(681)(0681)(681)(20110301)(002936)(036005 41022867)(+0001000)(+000000650)(+000000000)(0)(000 06772)(1292)(12)(0)(0)(00)(+0000000)(+0000000)(+00 00000)(+0000000)(+0000000)(+0000000)(+0000000)(+00 00000)(+0000000)(+0000000)(+0000000)(-0002000)(0)(00000000)(000000)

(C) Discount Voucher-> (10 is the discount voucher)
(10)(390)(681)(0681)(681)(20110301)(002936)(000067 72)(000000200)(000000650)(0000000000000000000)

(D) Payment Details Main -> (03 is payment)
(03)(390)(681)(0681)(681)(20110301)(002936)(001)(0 0000000)(0000000000000000000)(0000000000)(00000004 50)(01)(0)(01)(51) (3)(0000)(00)(0)(002)(02)(0000000450)(0)(0000)

(F) Payment Details Main II -> (again 03 as there is a second method on this record)
(03)(390)(681)(0681)(681)(20110301)(002936)(001)(0 0000000)(0000000000060045202)(0000000000)(00000002 00)(14)(1)(39)(89) (3)(0000)(00)(0)(002)(01)(0000000200)(0)(0000)

(G) End Details -> (always 85)
(85)(390)(681)(0681)(681)(20110301)(002936)(1404)( 00)(+00000450)

Good news is that the all sections contain a key (transaction number), so f I manage to parse these into a database, I can then build my transaction details.

Now how to I parse/split this structure so that inserts the right information in the right field? I was thinking of using the section ID's (01, 02, 03, 10 , 85), but these could easily exist somewhere else in the data. Now I have this data store by store, so one record would always start with section ID AND Store number.....

Thanks for all your help in advance...

A
Mar 8 '11 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
The spaces you have shown in your example, are they the only delimiters present?
Do the lines have a endpoint? A carriage return, and/or line feed?

The normal string manipulation tools are:
Split,Left,Right,InStr,InStrRev,Mid
I suggest you look in the VBA help file on how to use them.
Mar 8 '11 #2

P: 3
The spaces are no delimiters, in fact there are no delimters at all. The raw file only contains these numbers. They sometimes have strings and + sign in it, but in fact the only way to identify a field is its size.
I know that if a record starts with "01" for example, there will be 16 fields. Each field has a its size. If you look at my example "Header", There are 16 different fields and their size will always be the same. So first field 2, second 3, third 3, fourth 4, etc.

Now I want to create a code that takes the data, identifies what dection it is (with the first two figures, which are section ID and store number) and then splits the data and feeds it into the righ table.....
Mar 8 '11 #3

P: 3
Just had another look. In fact, each section is one line ....
Mar 8 '11 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
Its a matter of starting by looking at the code (the first 2 digits)
You can use Left(yourstring,2) to accomplish that.

Then make a function for each code, to read that particular bit. For example a Read01 function and a read03 function and so on.

The 01 function could like so:
Expand|Select|Wrap|Line Numbers
  1. Public Function Read01(strInput as string)
  2. 'Open a connection to a table
  3.   Dim rsDAO as dao.recordset
  4.   Set rsDao=CurrentDb.OpenRecordSet("SELECT * FROM myTable")
  5.   rsDao.New
  6.   rsDao!ID_Store=Mid(strInput,3,3) 'Read the store number into field
  7.   rsDao!fieldName=Mid(strinput,6,3)
  8.   ...
  9.   ...
  10.   rsDao.Update
  11.  
  12.   rsDao.Close
  13.   set rsDao=nothing
  14.  
  15.  

I dont know what exactly your looking for. I can only provide example code. To provide a complete code solution for your problem is several hours of work.
Mar 8 '11 #5

Rabbit
Expert Mod 10K+
P: 12,374
You should be able to record a macro where you use the import wizard to import your fixed width file.
Mar 8 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.