Import complex text file into Access (1 Viewer)

[Sergo]

Hi! I’m trying to help my coworker to create a database. They are receiving e-mail with some data that they want to transfer to access database to track. The one think they can do is copy past, copy paste… That is a lot of data to copy and paste. The date looks like this format
Field1
Text1

Field2
Text2 (text2 can be more than 255 char)

Field3
Text3 (text3 can be more than 255 char)

Field4
Text4



Field1
Text1

Field2
Text2 (text2 can be more than 255 char)

Field3
Text3 (text3 can be more than 255 char)

Field4
Text4

and so on.. It can be 50 records

I’m thinking they can copy this to the text file. Then the code form Access grabs the text file and imports to access table with format below. Can you please help me to create VBA code, I would really appreciate.
Field1 Fied2 Field3 Field4
Text1 Text2 Text3 Text4

 

[Uncle Gizmo]

Why?
Field1 Field2 Field3 Field4
Text1 Text2 Text3 Text4

Why not:-
FieldName........ Text
Field1..............Text1
Field2..............Text2
Field3..............Text3
Field4..............Text4

 

[Sergo]

Thank you foe the reply.
Because Field1, Field2, Field3, Field4 are field names. Text1, Text2, Text3, Text4, are data. So once it's uploaded to table it will look like

Field1 Field2 Field3 Field4
Text1 Text2 Text3 Text4
Text1 Text2 Text3 Text4
Text1 Text2 Text3 Text4
Text1 Text2 Text3 Text4
And so on...

 

[Uncle Gizmo]

Ah! so you already have a DB with a table with the Field1, Field2, Field3, Field4. The text file you are importing from has the name of each field, and under that field name is the text you want to place in that field?

 

[Sergo]

That is correct. But text3 most of the time has more the 255 char (multiple lines in the text file). So the code would probably read form next line after fieldName3 till fieldName4.

 

[Uncle Gizmo]

Please post a sample of the data.

 

[Sergo]

Sorry I could not find attachment option, so I paste it here. Text is confidential, so I replaced with "bla bla". Field names I bold it. This "Citation" can continue over 100 records. Thank you!


Citation 1.

Database
Bla bla bla bla
Author
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Title
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla bla bla bla Bla bla bla bla Bla bla bla bla
Source
Bla bla bla bla Bla bla bla bla
Abstract
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla

Language
English.

Citation 2.
Database
Bla bla bla bla
Author
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Title
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Source
Bla bla bla bla Bla bla bla bla
Abstract
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla

Language
English.

Citation 3.
Database
Bla bla bla bla
Author
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Title
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Source
Bla bla bla bla Bla bla bla bla
Abstract
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla

Language
English.

 

[Sergo]

Waiting for approval post!

 

[Uncle Gizmo]

Database
Author
Title
Source
Abstract

So you will ALWAYS have these?

 

[Sergo]

I just found that some Citation will have "Publication Type" in the end. But rest of is always same.

 

[Sergo]

So all fields will look like

Citation
Database
Author
Title
Source
Abstract
Publication Type

 

[Sergo]

Oops! I forgot "Language" field

Citation
Database
Author
Title
Source
Abstract
Language
Publication Type

 

[Uncle Gizmo]

Open the attached DB and replace this string:-

C:\ATH_Drive_DT\ACRE\Database\ForumAnswers\Reading Text File\xxx.txt

with the URL to your text file.

Hit the button and report what happens...

 

[Sergo]

Database shows "Not Responding"

 

[Uncle Gizmo]

Create a new blank database, create a form with a command button on it, and a BIG Text box.

Name the Text box "txtTesting"

Put this code in the forms code module and call it from the command button.

Private Sub fX()
Dim szTemp As String
Dim conTemp As String
Dim intLineNumb As Integer

On Error Resume Next

 Me.txtTesting = ""
 

'   open the text file
    Open "C:\ATH_Drive_DT\ACRE\Database\ForumAnswers\Reading Text File\xxx.txt" For Input As #1

    '   until the end of file
        While Not EOF(1)
        
            intLineNumb = intLineNumb + 1
        '   read the line and store it in a variable
            Line Input #1, szTemp   'Gets the lines in ORDER
    
        '   Build the string to put in the TEXT Box
            conTemp = conTemp & " " & intLineNumb & " " & szTemp & vbCrLf
    '   loop
        Wend
   ' Put the string in the text box
    Me.txtTesting = conTemp

'   close the file
    Close #1

End Sub

 

[vbaInet]

By the way Tony, did you see the sample file? Just curious because there's none in the thread.

 

[Uncle Gizmo]

Now don't forget to replace this line:-
C:\ATH_Drive_DT\ACRE\Database\ForumAnswers\Reading Text File\xxx.txt

with the url to your text file...

 

[Uncle Gizmo]

By the way Tony, did you see the sample file? Just curious because there's none in the thread.

Hi VB, If you mean a sample of the OP's text file then no, haven't seen it.

 

[vbaInet]

Might be worth asking for one. There might be a quicker way to do it in Excel instead.

 

[Sergo]

Sorry I miss-typed a derectory where my text file located. It did work. Here is the output.

1 Citation 1.
2
3 Database
4 Bla bla bla bla
5 Author
6 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
7 Title
8 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla bla bla bla Bla bla bla bla Bla bla bla bla
9 Source
10 Bla bla bla bla Bla bla bla bla
11 Abstract
12 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
13 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
14
15 Language
16 English.
17
18 Citation 2.
19 Database
20 Bla bla bla bla
21 Author
22 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
23 Title
24 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
25 Source
26 Bla bla bla bla Bla bla bla bla
27 Abstract
28 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
29 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
30 Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla Bla bla bla bla
31
32 Language
33 English.
34
35 Citation 3.
36 Database
37 Bla bla bla bla
38 Author