Import TXT file - data NOT in columns !!????

[bdsii]

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 !

[DBinPhilly]

I'm not sure I fully understand your problem, but:
is this a comma delimited text file? If so, you cannot have commas in the comments or the CC (or anywhere else). If you do, the import program will probably see each comma as a field break.

I am assuming that the field name is part of the .txt file and each field is a separate line? I suppose you could write a VBA routine that essentially operates like an old BASICA or GWBasic program reading a field and identifying field type by the field name it brings in and placing it in the corresponding access record field - just one record in the access record, right?

I've used this technique in the past when I was receiving machine-generated text files from a production line. Cumbersome but workable.

[bdsii]

Thanks DBinPhilly.....let me explain it better............I receive the data as text inside a plain text email and I was going to create my own txt file by copying and pasting into a Notepad document. The file will not be comma or tab delimited. The fields will be on their own line going down the file. The field name is part of the text file. The name is followed by the data for each line except for the comments field.

Suggestions ?

[hansup]

Here is a procedure which reads your file line by line and loads (a few of) the values into memory variables. You would need to flesh it out for the other variables you need. At the end where I Debug.Print the values, you will need to add your code to store them in your table.

Notice I made an assumption about Attachments. The code joins the values from multiple lines (until it hits a blank line) and stores a single string in the memory variable. I didn't know what you would want there.

Notes is similar to Attachments in that it can collect several lines from the data file. However, for Notes I decided you would want the lines joined with CRLF rather than semicolons.

Public Sub ProcessTextFile(ByVal pPath As String)
Dim lngFileNum As Long
Dim strAttachments As String
Dim strLine As String
Dim strLineType As String
Dim strNotes As String
Dim strRequestType As String
Dim strTxtIDNumber As String

lngFileNum = FreeFile
Open pPath For Input As #lngFileNum
Do While Not EOF(lngFileNum)
Line Input #lngFileNum, strLine
strLine = Trim(strLine)
If Len(strLine) > 0 Then ' non-blank line
If Left(strLine, 3) = "ID#" Then
strLineType = "ID#"
Else
strLineType = Split(strLine, ":")(0)
End If
Select Case strLineType
Case "ID#"
strTxtIDNumber = strLine
Case "Request Type"
strRequestType = SplitValue(strLine)
Case "Attachments"
Do While Len(Trim(strLine)) > 0
Line Input #lngFileNum, strLine
If Len(Trim(strLine)) > 0 Then
strAttachments = strAttachments & "; " & strLine
End If
Loop
If Len(strAttachments) > 0 Then
strAttachments = Mid(strAttachments, 3)
End If
Case "Notes"
Do While Len(Trim(strLine)) > 0
Line Input #lngFileNum, strLine
If Len(Trim(strLine)) > 0 Then
strNotes = strNotes & vbCrLF & strLine
End If
Loop
If Len(strNotes) > 0 Then
strNotes = Mid(strNotes, 3)
End If
End Select
End If ' non-blank line
Loop
Close #lngFileNum
Debug.Print "strTxtIDNumber: '" & strTxtIDNumber & "'"
Debug.Print "strRequestType: '" & strRequestType & "'"
Debug.Print "strAttachments: '" & strAttachments & "'"
Debug.Print "strNotes: '" & strNotes & "'"
End Sub

This is a helper function called by ProcessTextFile.

Public Function SplitValue(ByVal pLine As String) As String
Dim strOut As String
strOut = Trim(Split(pLine, ":", 2)(1))
SplitValue = strOut
End Function

Finally, this is the test procedure I used to feed a file path to ProcessTextFile.

Public Sub test_ProcessTextFile()
Const cstrFile As String = "testTXTimportDatabase.txt"
Dim strFolder As String
Dim strPath As String
strFolder = CurrentProject.Path
strPath = strFolder & Chr(92) & cstrFile
Call ProcessTextFile(strPath)
End Sub

[bdsii]

Woo-Hoo ! Awesome Hansup, thanks ! Let me dig into this and I may have a follow-up question or two. I would have never come up with this code, unfortunately. I have to look more at it but I am not sure I understand how to create a new record using code without going through loading the data into a form. Once I have the variables, I would have the data to load, but I am not sure how to bridge the gap to entering a new record with this data.

I do really appreciate it. Thanks so much !

:mbounce2:

[hansup]

Good. I hoped that outline would be enough to get you started. There is still a lot of tedious work ahead to deal with the larger set of variables you need to include, but it's not new ground ... just more of the same.

To save the variable values in a table, I would choose between two methods:

Build a SQL INSERT statement and execute it.

strSql = "INSERT INTO Dumptable (TxtIDNumber)" & _
" VALUES ('" & strTxtIDNumber & "');"

Or open Dumptable as a recordset, then do the AddNew method and add each variable value to the matching field.

rs!TxtIDNumber = strTxtIDNumber

Choose the method which is more comfortable to you.