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

Flat File Import to Access DB

P: 3
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
Share this Question
Share on Google+
1 Reply


100+
P: 1,646
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.