Import complex text file into Access

[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

 

[Uncle Gizmo]

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

vbaInet --- Your observations would be greatly appreciated.

Sergo --- Please post a sample file as VB may have a different solution.

ALSO -- Sergo unless you can post data which is a true representation of the data you are trying to import then there is a high probability you will be disappointed.

I'm not saying you should post your confidential stuff, but it MUST contain REALITY Bla Bla Bla will just lead you to disappointment...

 

[Uncle Gizmo]

AND Sergo, why the double posts?

I already deleted one, now it's your turn... Get it Gone...

 

[Uncle Gizmo]

Will there be a :-
Citation 5
Citation 6
Citation 7
Citation 8
Citation 9

and more?

 

[Uncle Gizmo]

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.

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

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

Please explain..

 

[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

 

[Uncle Gizmo]

Oops! I forgot "Language" field

Citation
Database
Author
Title
Source
Abstract
Language
Publication Type

Do you have a table with the fields named:-
Citation
Database
Author
Title
Source
Abstract
Language
Publication Type

 

[Uncle Gizmo]

Sergo doesn't know that you're a Moderator

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

I know what you mean, however I like concrete confirmation. I have done a lot of work for customers on a misunderstanding, and guess what, they don't like paying for the work they say they didn't order. And it's difficult to argue with them. Better getting it right in the 1st place.

 

[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

 

[Uncle Gizmo]

Line 68 >>> Authors

 

[Uncle Gizmo]

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

So Citation will be 1 to 200? will that cover it?

 

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

 

[Uncle Gizmo]

78 Publication Type

But no where else?

Why?

 

[Sergo]

So Citation will be 1 to 200? will that cover it?

I think so. I can even ask users upload 50 Citations in the text file a time, if the user received over 100 or 200.

 

[Sergo]

78 Publication Type

But no where else?

Why?

That is only one field, sometimes it does in Citation, sometimes not.

 

[Uncle Gizmo]

That is only one field, sometimes it does in Citation, sometimes not.

Are you saying that sometimes there is a "Publication Type" and sometimes it's missing?