1 Excel row to 3 Access Table rows (formatted).

G

gabedellafave

Here is hoping that someone out there can help me with this
question(s):

What I want to do is to take the following 2 rows in Excel:

005555 HAMADFIAR, ALI A234 S WINES STREET FERNDEL,NY 10405
URO02 2125551122 ATTG 5161112222 X12345 NYNB12345 BB1234567 7654321
112255 1234567890 ROSE, STEVEN D 123 N MACKS ST FERNDEL NY
10405 OPH02 2124445516 ATTG 9145553333 Z12345 NYDF23456 CC2345678
8765432

and convert them to text file fixed length records with the following
format by using MS Access:

DOCTOR ALL 005555 MD REF01
ATTG HAMADFIAR ALI ANYNB12345
X1234NYNB12345 7654321
1
BB1234567
DRADDR1 ALL 005555 234 S WINES
STREET
FERNDEL NY10405
5161112222
DRADDR2 ALL
005555
2125551122
DOCTOR ALL 112255 MD REF01
ATTG ROSE STEVEN DNYDF23456
Z12345NYDF23456 8765432
1
CC2345678
1234567890
DRADDR1 ALL 112255 123 N MACKS
ST
FERNDEL NY10405
9145553333
DRADDR2 ALL
112255
2124445516

The output consists of fixed length fields and records (450
characters).

I also need to format the Last, First, Middle name and City/State/Zip
into three separate fields each.

I found it easy to do a one for one reformatting of the file, using
Access (except for the name/address reformat), but I haven't got a
clue as to where to go with this as far as using Modules is concerned.

I would be very grateful for any ideas that anyone might have. Thank
you!

GDF
 
G

Graham Mandeno

Hi Gabe

I'm sorry, but the line wrapping of the input and output data as you pasted
it into your message makes it impossible to see where one field stops and
the next one starts. This makes it very difficult to understand what you
are trying to achieve.

However, here are some tips:

1. You can use TransferSpreadsheet to open an Excel spreadsheet as a linked
table:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, sTable,
sFile, True

2. If the linked table is required only temporarily, you can remove it when
you have finished:
DeleteObject CurrentDb.TableDefs, sTable

3. To create fixed-length text fields padded with spaces, create a long
sting of spaces, append it to your data, and then use the Left function to
cut it to the right length:
Dim sSpaces as String
sSpaces = Space(1000)
then...
sPaddedName = Left(sName & sSpaces, 25)

4. To parse strings such as names and addresses into individual components,
you can use the following functions:
Split - breaks a string into an array of strings, with a given delimiter
InStr and InStrRev - find a given string (delimiter) in another string.
Left(str, pos-1) - returns the part of the string to the left of a found
delimiter
Mid(str, pos+1) - returns the part of the string to the right of a found
delimiter

I notice that your data is not consistent. For example, in one record you
have a comma between the city and state, and in another you do not.
Inconsistencies such as these will need to be catered for, and make the job
more difficult. I suggest that with an address it is easier to parse from
the right hand side, picking off elements from the end of the string. You
will still get problems when, for example, you have a city that consists of
more that one word.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand
 
G

gabe.dellafave

Hi Graham,

Thanks for your advice. It is very helpful in that it gives me some
direction and good tips.

I'll keep plugging away at it.

By the way, I'm in New Jersey, US.

Gabe

Hi Gabe

I'm sorry, but the line wrapping of the input and output data as you pasted
it into your message makes it impossible to see where one field stops and
the next one starts. This makes it very difficult to understand what you
are trying to achieve.

However, here are some tips:

1. You can use TransferSpreadsheet to open an Excel spreadsheet as a linked
table:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, sTable,
sFile, True

2. If the linked table is required only temporarily, you can remove it when
you have finished:
DeleteObject CurrentDb.TableDefs, sTable

3. To create fixed-length text fields padded with spaces, create a long
sting of spaces, append it to your data, and then use the Left function to
cut it to the right length:
Dim sSpaces as String
sSpaces = Space(1000)
then...
sPaddedName = Left(sName & sSpaces, 25)

4. To parse strings such as names and addresses into individual components,
you can use the following functions:
Split - breaks a string into an array of strings, with a given delimiter
InStr and InStrRev - find a given string (delimiter) in another string.
Left(str, pos-1) - returns the part of the string to the left of a found
delimiter
Mid(str, pos+1) - returns the part of the string to the right of a found
delimiter

I notice that your data is not consistent. For example, in one record you
have a comma between the city and state, and in another you do not.
Inconsistencies such as these will need to be catered for, and make the job
more difficult. I suggest that with an address it is easier to parse from
the right hand side, picking off elements from the end of the string. You
will still get problems when, for example, you have a city that consists of
more that one word.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Here is hoping that someone out there can help me with this
question(s):
Click to expand...
What I want to do is to take the following 2 rows in Excel:
Click to expand...
005555 HAMADFIAR, ALI A234 S WINES STREET FERNDEL,NY 10405
URO02 2125551122 ATTG 5161112222 X12345 NYNB12345 BB1234567 7654321
112255 1234567890 ROSE, STEVEN D 123 N MACKS ST FERNDEL NY
10405 OPH02 2124445516 ATTG 9145553333 Z12345 NYDF23456 CC2345678
8765432
Click to expand...
and convert them to text file fixed length records with the following
format by using MS Access:
Click to expand...
DOCTOR ALL 005555 MD REF01
ATTG HAMADFIAR ALI ANYNB12345
X1234NYNB12345 7654321
1
BB1234567
DRADDR1 ALL 005555 234 S WINES
STREET
FERNDEL NY10405
5161112222
DRADDR2 ALL
005555
2125551122
DOCTOR ALL 112255 MD REF01
ATTG ROSE STEVEN DNYDF23456
Z12345NYDF23456 8765432
1
CC2345678
1234567890
DRADDR1 ALL 112255 123 N MACKS
ST
FERNDEL NY10405
9145553333
DRADDR2 ALL
112255
2124445516
Click to expand...
The output consists of fixed length fields and records (450
characters).
Click to expand...
I also need to format the Last, First, Middle name and City/State/Zip
into three separate fields each.
Click to expand...
I found it easy to do a one for one reformatting of the file, using
Access (except for the name/address reformat), but I haven't got a
clue as to where to go with this as far as using Modules is concerned.
Click to expand...
I would be very grateful for any ideas that anyone might have. Thank
you!
Click to expand...
GDF
Click to expand...
Click to expand...
 
Top