bdsii
06-07-2011, 02:07 PM
I have looked around on the web but really have not found anything that pertains to my problem. I really don't even know what the term is for this situation. I am new to Access and am using Access 2007 if that helps.
I understand how to import excel files and most txt files into a table in ACCESS. However, the data I am receiving comes one record at a time (think one single record submission per week) in a txt file. I think I need code to process this each time I receive the data and would prefer it to be as automated as possible. Geting the data in this format is the best format that I can hope for so I am stuck with my data source. I was going to include a test txt file but I cannot if I include the database. Instead, I am copying the data from inside the txt file below.
I want to use code to import the data from the txt file and append it to an existing table in Access. The fields present will be the same with the data to the left the same from txt file to txt file but for the comments the number of lines for each comment may vary depending on the length of the comment.
If this data were just separated by something but were all on the same line I probably could figure it out but with each field on a separate line (with comments under the comment header line), I am not sure what to do. I tried converting this to Excel and then transposing the data but it would not format correctly. I think I would need code to process these files.
Can anyone suggest how this is done ? I have not tried to code a dialog box that allows you to select and then open a file located on your computer either so that makes this more complicated as well.
Any help or suggestions would be appreciated ! :help
ID# ID12345
Request Type: Alpha
Status: beta
Office Contact: Gamma
Office Prefix: Delta
Date Submitted: 4/26/2011 1:55:17 PM
Report Date: 4/29/2011
Department: Epsilon - Epsilon
Work No: ABC-123
Order No: ZYX987
Priority: 7
Data1: AB-1-CD
Data2: 1234-TEST
Nomenclature: Zeta Eta
Data Number3: probem data
Assign To: HR One (123.4567)
Office Prefix2: Theta
CC: names, spaced, by, comma
Problem:
Comments go under problem on separate line.
Attachments:
123.pdf
456.pdf
Notes:
notes go here on next line.
Thanks !
I understand how to import excel files and most txt files into a table in ACCESS. However, the data I am receiving comes one record at a time (think one single record submission per week) in a txt file. I think I need code to process this each time I receive the data and would prefer it to be as automated as possible. Geting the data in this format is the best format that I can hope for so I am stuck with my data source. I was going to include a test txt file but I cannot if I include the database. Instead, I am copying the data from inside the txt file below.
I want to use code to import the data from the txt file and append it to an existing table in Access. The fields present will be the same with the data to the left the same from txt file to txt file but for the comments the number of lines for each comment may vary depending on the length of the comment.
If this data were just separated by something but were all on the same line I probably could figure it out but with each field on a separate line (with comments under the comment header line), I am not sure what to do. I tried converting this to Excel and then transposing the data but it would not format correctly. I think I would need code to process these files.
Can anyone suggest how this is done ? I have not tried to code a dialog box that allows you to select and then open a file located on your computer either so that makes this more complicated as well.
Any help or suggestions would be appreciated ! :help
ID# ID12345
Request Type: Alpha
Status: beta
Office Contact: Gamma
Office Prefix: Delta
Date Submitted: 4/26/2011 1:55:17 PM
Report Date: 4/29/2011
Department: Epsilon - Epsilon
Work No: ABC-123
Order No: ZYX987
Priority: 7
Data1: AB-1-CD
Data2: 1234-TEST
Nomenclature: Zeta Eta
Data Number3: probem data
Assign To: HR One (123.4567)
Office Prefix2: Theta
CC: names, spaced, by, comma
Problem:
Comments go under problem on separate line.
Attachments:
123.pdf
456.pdf
Notes:
notes go here on next line.
Thanks !