Importing data from an external source into access.

T

Tom

I am trying to import a file into an access table, but all
of the data is in one field. My ultimate goals is to only
utilize Item#, Description, Configuration, and Pallet
Stack Height. How can I make a table using only those
field identifiers and pulling the data from underneath,
which is in the next line?

TFTH,
Tom



Example:


08/20/2003 14:59:07 McHugh
Software, DM plus 5.5
Page: 1 of 8405
Report: I5000G0
ITEM SETUP REPORT

Selection Criteria:
Configuration Detail: Yes
Configuration UOM Detail: Yes
Whse Detail: Yes
Catch Detail: No
Cost Detail: No


Item # Description
00011170050501 DISCON 54/12 #######

Item Stock Inv Mix ABC Lot Date Shelf Expr
Default BOL
Type UOM Stat Lot Class Ctrl Ctrl Life Stat
Config Category
FERT CS GOOD Yes B Yes Shlf 210 LIQU
9/PT BOL


Configuration Pallet Stack Height Pallet Building
Notes
9/PT 2

Length
Conversion Child Prompt Recv
Width Min Catch Max
Catch Catch
UOM Factor UOM UOM UOM Height
Unit Weight Unit Qty Qty Tare
Wgt Unit
PAL 9 CS PAL Yes 4.00000
ft 0.45359 kg 0.00 0.00
4.00000 ft
5.00000 ft

CS 1 CS CS No
m 0.45359 kg 0.00 0.00
m
m


Hot FIFO Put FIFO FIFO Pick LIFO Pick Inv
Full Part
Whse Prod Window Override Window Window Stat
List ID List ID
1474 No 20 Oldest 20 20


Repack
UOM MPCK% Pick Repack Class Parcel
CS 100 Yes No Yes
PAL 100 Yes No Yes
 
J

John Nurick

Hi Tom,

Depending on the balance between your time and your budget, the simplest
approach may be to buy software such as Monarch which is designed for
just this purpose.

Otherwise, you need to write a program or script in your favourite
language that reads the file line by line and picks out the bits it
needs. The general idea would be

Do until end of file
read next line
if it begins with "Item #" then
read the next line
everything up to the first space is ItemNumber
everything from the first character after the white space
is Description
elseif it begins with " Configuration" then
read next line
everything up to the first space is Configuration
if there's a number starting at the 19th character
it's the PalletStackHeight
end if
Loop




I am trying to import a file into an access table, but all
of the data is in one field. My ultimate goals is to only
utilize Item#, Description, Configuration, and Pallet
Stack Height. How can I make a table using only those
field identifiers and pulling the data from underneath,
which is in the next line?

TFTH,
Tom



Example:


08/20/2003 14:59:07 McHugh
Software, DM plus 5.5
Page: 1 of 8405
Report: I5000G0
ITEM SETUP REPORT

Selection Criteria:
Configuration Detail: Yes
Configuration UOM Detail: Yes
Whse Detail: Yes
Catch Detail: No
Cost Detail: No


Item # Description
00011170050501 DISCON 54/12 #######

Item Stock Inv Mix ABC Lot Date Shelf Expr
Default BOL
Type UOM Stat Lot Class Ctrl Ctrl Life Stat
Config Category
FERT CS GOOD Yes B Yes Shlf 210 LIQU
9/PT BOL


Configuration Pallet Stack Height Pallet Building
Notes
9/PT 2

Length
Conversion Child Prompt Recv
Width Min Catch Max
Catch Catch
UOM Factor UOM UOM UOM Height
Unit Weight Unit Qty Qty Tare
Wgt Unit
PAL 9 CS PAL Yes 4.00000
ft 0.45359 kg 0.00 0.00
4.00000 ft
5.00000 ft

CS 1 CS CS No
m 0.45359 kg 0.00 0.00
m
m


Hot FIFO Put FIFO FIFO Pick LIFO Pick Inv
Full Part
Whse Prod Window Override Window Window Stat
List ID List ID
1474 No 20 Oldest 20 20


Repack
UOM MPCK% Pick Repack Class Parcel
CS 100 Yes No Yes
PAL 100 Yes No Yes
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Top