Import complex text file into Access

[vbaInet]

Seeing is believing

 

[vbaInet]

It's still doable but it's better to have consistency.

 

[Uncle Gizmo]

I asked them all labels (fields) in citation are consistent and structure is same. They told me “Yes”

"They" always do....

Spreadsheets are the worst for supposedly always being the same. Sometimes I think "They" do it on purpose.

 

[Uncle Gizmo]

I've had break from it, and coming back to it fresh, I've got the missing info, at least the row number of the last line. So a solution is nearer, and more to the point, I'm going in the right direction!

 

[Sergo]

Ok guy, I just talk to users; they give permission to send you a sample.

 

[Sergo]

Oops, It did not attached first time.

 

[vbaInet]

What is the resolution with the Authors and Author inconsistency?

 

[Sergo]

Thank you for pointing this out! I did look and found that some citation has “s” some not. That is another reason it will not work. I need talk to users, because before start this project I asked them all labels (fields) in citation are consistent and structure is same. They told me “Yes”

That’s I was mention earlier, some citation has “s” in Author field, some not.

 

[Sergo]

I talked to user and they agreed for this option.
Citation# ----- Citation_Data
1-----------------(insert whole Citation1)
2-----------------(insert whole Citation2)
3-----------------(insert whole Citation3)

 

[vbaInet]

That’s I was mention earlier, some citation has “s” in Author field, some not.

You said you were going to talk to them and get it clarified. Why would the Author field have either Author or Authors? That's an inconsistency you need to speak to them about.

 

[Sergo]

You said you were going to talk to them and get it clarified. Why would the Author field have either Author or Authors? That's an inconsistency you need to speak to them about.

User told me that's how they receive email, they don't know why it's inconsistency. So because inconsistency, I asked them if we can go plan "B" which is I mention earlier. . They are agreed.

 

[vbaInet]

It's still possible to include the Authors but you should have checked your file properly to ensure you have all these things ironed before posting.

The other thing you mentioned is the file will have record numbers, but the sample file doesn't. What's happening here?

 

[vbaInet]

Another thing that wasn't mentioned is the line breaks and spacing before and after it. What do you do with that?

 

[Sergo]

No. But we can add a line # with the code that send me Uncle Gizmo.

Like I was mention earlier, no it does not have line#, but we can populate Line# with the code that Uncle Gizmo sent.

So, are we still going plan A – initial plan. Or Plan B – transfer only 2 fields Citation# and Citation Data (Memo field)?

 

[vbaInet]

Alright, so that was Uncle Gizmo's output. You don't nee line numbers.

I don't intend changing the original structure so I'll show you how it's done later.

And what about the Line Breaks question in my last post?

 

[Sergo]

We can remove (trim) line breaks. Does trim only removes spaces or line breaks too?

 

[vbaInet]

I'm talking about this in your Sample file between records 1 and 2:

English.
[COLOR="red"]<---- Space here[/COLOR]
[COLOR="Red"]________________________________________[/COLOR]
[COLOR="red"]<---- Space here[/COLOR]
Citation 2.

The line in red and the the spaces above and between it. What happens to those? And does that definitely indicate the end of a record?

 

[Sergo]

Looks like, it is end of record. Can we ignore them?

 

[vbaInet]

"Looks like" isn't exactly "it is". Can you confirm with your users.

By the way, what attempts have you made so far? Can you show us something you've tried that doesn't work?

 

[Sergo]

About attempts. I haven’t done anything yet. All I have is Uncle Gizmo’ copy with some modification. I’m at work right now and this is my outside of work project to get experience in VBA. Like I was mention on the beginning that I helping co-worker to build this database for tracking citation that they receiving via email. I don’t have problem to build database. But I have problem to build that code that read the text file and place it in database, never done it. I know VBA, but not that good like you guys. Once I get code working, rest of it I can do it myself.
I still did not get yet, are we going to plan A or plan B.
Also, English is my second language, so don’t be peaky.