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:
013906810681681 201103010029361 404000000000000 00000 300000010000000 0000000000000
023906810681681 201103010029360 3600541022867+0 00100 0+000000650+000 000000000006772 1292120000+0000 000+0 000000+0000000+ 0000000+0000000 +0000000+000000 0+000 0000+0000000+00 00000+0000000-000200000000000 0000000
103906810681681 201103010029360 000677200000020 00000 006500000000000 000000000
033906810681681 201103010029360 010000000000000 00000 000000000000000 000000000004500 100151 300000000020200 0000045000000
033906810681681 201103010029360 010000000000000 00000 060045202000000 000000000002001 413989 300000000020100 0000020000000
853906810681681 201103010029361 40400+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)(2011 0301)(002936)(1 404)( 00000000)(0)(00 000000)(3)(0000 00)(1)(0)(00000 00000 000000000)
(B) Transaction Details -> (02 id the ID for trans details)
(02)(390)(681)( 0681)(681)(2011 0301)(002936)(0 36005 41022867)(+0001 000)(+000000650 )(+000000000)(0 )(000 06772)(1292)(12 )(0)(0)(00)(+00 00000)(+0000000 )(+00 00000)(+0000000 )(+0000000)(+00 00000)(+0000000 )(+00 00000)(+0000000 )(+0000000)(+00 00000)(-0002000)(0)(000 00000)(000000)
(C) Discount Voucher-> (10 is the discount voucher)
(10)(390)(681)( 0681)(681)(2011 0301)(002936)(0 00067 72)(000000200)( 000000650)(0000 000000000000000 )
(D) Payment Details Main -> (03 is payment)
(03)(390)(681)( 0681)(681)(2011 0301)(002936)(0 01)(0 0000000)(000000 0000000000000)( 0000000000)(000 00004 50)(01)(0)(01)( 51) (3)(0000)(00)(0 )(002)(02)(0000 000450)(0)(0000 )
(F) Payment Details Main II -> (again 03 as there is a second method on this record)
(03)(390)(681)( 0681)(681)(2011 0301)(002936)(0 01)(0 0000000)(000000 0000060045202)( 0000000000)(000 00002 00)(14)(1)(39)( 89) (3)(0000)(00)(0 )(002)(01)(0000 000200)(0)(0000 )
(G) End Details -> (always 85)
(85)(390)(681)( 0681)(681)(2011 0301)(002936)(1 404)( 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
5  3014 
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,Righ t,InStr,InStrRe v,Mid
I suggest you look in the VBA help file on how to use them.
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.....
Just had another look. In fact, each section is one line ....
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: - Public Function Read01(strInput as string)
-
'Open a connection to a table
-
Dim rsDAO as dao.recordset
-
Set rsDao=CurrentDb.OpenRecordSet("SELECT * FROM myTable")
-
rsDao.New
-
rsDao!ID_Store=Mid(strInput,3,3) 'Read the store number into field
-
rsDao!fieldName=Mid(strinput,6,3)
-
...
-
...
-
rsDao.Update
-
-
rsDao.Close
-
set rsDao=nothing
-
-
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.
Rabbit 12,516
Recognized Expert Moderator MVP
You should be able to record a macro where you use the import wizard to import your fixed width file.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Bryan Zash |
last post by:
When querying a bit field, I am encountering a problem with MS SQL
Server returning a larger number of records for a table than the
actual number of records that exist within that table.
For example, my customer table has 1 million unique records, so the
results of the following query are as such:
select count(customer_nbr) from customer = 1,000,000
There is bit field in the customer table that denotes whether a
|
by: Milo Woodward |
last post by:
Does anyone know how to retrieve the number of records an XML file
contains with a vb.net method? I can read through an entire XML file,
import records into SQL Server, etc. However, I don't want to have to
cycle through the entire XML file in order to do this. When I am
importing the XML file, I want to display record X of XX. Any ideas
would be appreciated. Thanks in advance.
|
by: Dalan |
last post by:
I am finalizing an Access 97 db and need to decide was is the best
method to limit the number of records one can enter in the demo
version of same. The archives seem to be replete with many variations
on this theme. A couple that I have tried simply failed. So I'm
willing to listen to what works the best and why, and of course, the
"how" would be appreciated too.
Incidentally, the demo db will be on a CD at least initially, so
having an...
|
by: vanvee |
last post by:
Hi
I have an application for my company's HR department where we store
resumes for candidates we receive. I have an application that uses
VB.Net and ADO.Net and data bindings (through code) to controls on a
Windows form. The question I have is that once the database grows and
contains a large number of records, I am worried the dataset is going
to create too much overhead when the user clicks "show all records"
(the full resume which...
|
by: Vadim |
last post by:
Hi!
I imported some table (about 1500 records) using "LOAD DATA LOCAL
INFILE..." (command line console). No warnings, no skipped, no deletes
- all the recored are written to be imported.
However, in MySQL Administrator UI tool I see completely different
number in the "Rows" column. Making a SELECT query shows that the
actual number of records in the table is the correct one (the same
number as in text file table). Command line SHOW TABLE...
| | |
by: john |
last post by:
I have a form with 1 table. I would like to create a field in my form that
shows the number of records (of the whole table) that have one particular
value. Is that possible? I fiddled with a calculated field but I couldn't
make it work.
Thanks in advance,
john
|
by: itm |
last post by:
I have a mail out to send to a group of owners with multiple accounts.
I want to limit the number of accounts that print on the first page to
20. I want remaining accounts to print on a second page. Is there a
way to accomplish this?
Thanks so much for your help!
|
by: =?Utf-8?B?UHJpeWE=?= |
last post by:
Hi,
I'm faced with a classic problem of how to update a large number of
records from a web page. I;m trying to build an interface that will
display recordset in the order of 3000 rows and allow the user to edit and
update records from that interface. I have cached some static lists in
the page but the main recordset should reflect updated data so it can't
be cached. I also compute some control functions over the whole data.
This will...
|
by: deppeler |
last post by:
I am wanting to return the number of records (rows) in a flat file db.
My script is a resource kit library and each item has a catalog number ($catnumber), instead of the user having to remember the next catalog number to enter as they add a record, I want to be able to either show them the next number or have it as a value in a field on the form.
I just need to know how to return the total number of records (rows?) in the db as a number...
|
by: rfdjr1 |
last post by:
I'm using Access 2000. I have a raher simple database of my CD collection, with
just three fields, Artist, Album Title and Type of Music. While going to update
it today with a couple new CD's, I noticed that if I sort alphabetically by
Artist it have 387 records but if I sort by Album Title it shows 393 records.
Any idea why this would be? And I know of at least two titles that don't show
up when I look for them under the Artist sort....
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
