471,625 Members | 1,577 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,625 software developers and data experts.

Flat File Import to Access DB

I receive several flat files daily that need to be improted into my Access Database.
Each file record starts with a specific letter or group of letters, the following lines or rows in the flat file starts with an identifier followed by several data fields each seperated by ^ and each line end with ^. The record ends with a specific letter (T) or group of letters. Each file received may contain one or more records. The end of file is marked by X^ followed on the next line by Z^.
I need to import the records to my database tables in MS Access 2003, each table would receive data based on the start of each line (i.e. "Patient" table would receive data from the second line: P^^10064175^...., Table "Result" would receive data from lines starting with R^.... . Can this be done and can someone guide me?
Thank you for any suggestions.
The following is an example of the information that needs to be imported

H^KP^BSWUUQHPC^11112006KP^0^N6.01^N^
P^^10064175^2323232^597028O^123456789^SMITH JR,JOHN^F^PRE-EMPLOYMENT^1^11102006^11:45 AM^11102006^2334^ABC COMPANY^^19140^TIN,BRAD^20061111^0227^YNNNNNN^^232 3232^597028OKP^
R^35105N^AMP^AMPHETAMINES (1000 ng/mL SCREEN)^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^1000^50 0^^^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^AMP^
R^35105N^COC^COCAINE METABOLITES ^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^300^150 ^^^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^COC^
R^35105N^T50^MARIJUANA METABOLITES, (50 ng/mL SCREEN)^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^50^15^^ ^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^T50^
R^35105N^OPI2K^OPIATES (2000 NG/ML SCREEN)^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^2000^20 00^^^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^OPI2K ^
R^35105N^PCP^PHENCYCLIDINE ^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^25^25^^ ^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^PCP^
R^35105N^CREAR^CREATININE ^D^N^ ^KP^177.8^mg/dL^^350^>/= 20 mg/dL^^20061111^0227^10064175^2323232^R^^^^^^SAP^
R^35105N^PHR^pH ^D^N^ ^KP^6.3^^^^4.5-8.9^^20061111^0227^10064175^2323232^R^^^^^^SAP^
R^35105N^NITRQL^OXIDIZING ADULTERANTS ^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^^^^^^SA P^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^NITRQ L^
T^10064175^2323232^15^
X^00000^00001^00006^00008^00001^11112006KP^00000
Z^00001^
Nov 12 '06 #1
1 1952
willakawill
1,646 1GB
I receive several flat files daily that need to be improted into my Access Database.
Each file record starts with a specific letter or group of letters, the following lines or rows in the flat file starts with an identifier followed by several data fields each seperated by ^ and each line end with ^. The record ends with a specific letter (T) or group of letters. Each file received may contain one or more records. The end of file is marked by X^ followed on the next line by Z^.
I need to import the records to my database tables in MS Access 2003, each table would receive data based on the start of each line (i.e. "Patient" table would receive data from the second line: P^^10064175^...., Table "Result" would receive data from lines starting with R^.... . Can this be done and can someone guide me?
Thank you for any suggestions.
The following is an example of the information that needs to be imported

H^KP^BSWUUQHPC^11112006KP^0^N6.01^N^
P^^10064175^2323232^597028O^123456789^SMITH JR,JOHN^F^PRE-EMPLOYMENT^1^11102006^11:45 AM^11102006^2334^ABC COMPANY^^19140^TIN,BRAD^20061111^0227^YNNNNNN^^232 3232^597028OKP^
R^35105N^AMP^AMPHETAMINES (1000 ng/mL SCREEN)^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^1000^50 0^^^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^AMP^
R^35105N^COC^COCAINE METABOLITES ^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^300^150 ^^^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^COC^
R^35105N^T50^MARIJUANA METABOLITES, (50 ng/mL SCREEN)^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^50^15^^ ^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^T50^
R^35105N^OPI2K^OPIATES (2000 NG/ML SCREEN)^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^2000^20 00^^^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^OPI2K ^
R^35105N^PCP^PHENCYCLIDINE ^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^25^25^^ ^^SAP^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^PCP^
R^35105N^CREAR^CREATININE ^D^N^ ^KP^177.8^mg/dL^^350^>/= 20 mg/dL^^20061111^0227^10064175^2323232^R^^^^^^SAP^
R^35105N^PHR^pH ^D^N^ ^KP^6.3^^^^4.5-8.9^^20061111^0227^10064175^2323232^R^^^^^^SAP^
R^35105N^NITRQL^OXIDIZING ADULTERANTS ^D^K^ ^KP^^^^^^^20061111^0227^10064175^2323232^R^^^^^^SA P^
A^A^NEGATIVE^^20061111^0227^10064175^2323232^NITRQ L^
T^10064175^2323232^15^
X^00000^00001^00006^00008^00001^11112006KP^00000
Z^00001^
Does each record take up one line?
Or is it spread over several lines as above.
Even better if you could email me one of these files or a section thereof.
You can alter the patient data for privacy.
Nov 12 '06 #2

Post your reply

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

Similar topics

1 post views Thread by ED | last post: by
15 posts views Thread by lxyone | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
1 post views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.