Import complex text file into Access

[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

 

[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...

 

[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.

 

[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!

 

[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

 

[Sergo]

Database shows "Not Responding"

 

[vbaInet]

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

 

[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

 

[Sergo]

here is my output by using that code
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

 

[vbaInet]

AND Sergo, why the double posts?

Sergo doesn't know that you're a Moderator

I don't understand the solution you suggest here:-

So the code would probably read form next line after fieldName3 till fieldName4.

Please explain..

I'm sure once you see the file it'll be clear

 

[vbaInet]

I'm only on the sidelines anyway. Just ignore me

 

[Sergo]

So I copy 4 Citation in text file and ran it in database you sent me. Here is my output. Note: After I got output I replace all confidential data with the line, just left field names and I did not change the structure.

1 Citation 1.
2
3 Database
4 -----------------------
5 Author
6 --------------------------------------------------------------------------
7 Title
8 --------------------------------------------------------------------------------------
9 Source
10 -------------------------------------------------------------
11 Abstract
12 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13
14 -----------------------------------------------------
15
16 Language
17 -----------------
18
19 Citation 2.
20 Database
21 ------------------------------
22 Author
23 --------------------------------------------------------------------------------------
24 Title
25 ----------------------------------------------------------------------------------------------------------------------------------
26 Source
27 ----------------------------------------------------------------------------------------------------------------------------
28 Abstract
29 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
30
31 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
32
33 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
34
35 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
36
37 ----------------------------------------------------------------------
38
39 Language
40 ------------------------------------
41
42 Citation 3.
43 Database
44 -----------------------------
45 Author
46 ---------------------------------------------------------------------------------------------
47 Title
48 -----------------------------------------------------------------------------------------------------------------
49 Source
50 ------------------------------------------------------------------------
51 Abstract
52 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
53
54 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
55
56 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
57
58 -------------------------------------------------------------------------------------------------------------------------------------------------------
59
60 ------------------------------------------------------------------------------------------------------------------
61
62 Language
63 ------------
64
65 Citation 4.
66 Database
67 -------
68 Authors
69 ------------------------------------------------------------
70 Title
71 --------------------------------------------------------------------------------------------
72 Source
73 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
74 Abstract
75 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
76 Language
77 -------------------------------------------------------
78 Publication Type
79 -----------------------------------

 

[Sergo]

I will have the database with all these fields.
Citation
Database
Author
Title
Source
Abstract
Language
Publication Type

 

[Sergo]

Yes, sometime they receive 4,5 Citation, sometimes over 100 in one email

 

[vbaInet]

Great catch Tony. There's also the incrementing Citation.

Edit: Ignore, you've already seen that. Right, I'm going back to the background.

 

[Sergo]

as you can see some Abstracts have multiple lines.