Import complex text file into Access

[Sergo]

But what I’m good with; I can read the code and follow it, and learn a lot from it.

 

[vbaInet]

Hopefully you can read our code!

 

[Sergo]

The one Uncle Gizmo sent, it was easy to read, because it has comments. When I write a code I don’t put comments, I’m lazy on comments.

 

[vbaInet]

It's absolutely necessary to comment code especially when you're done with it.

I believe the file attached (Access 2000) should do it. I didn't have time for any fancy forms so just open the module and run the function called TransposeFile from the Immediate Window:

?TransposeFile("[COLOR="Blue"]Path to file[/COLOR]\Sample.txt")

All the relevant comments are there.

One thing to note is that it transposes the text file to an Excel sheet and the reason behind that is because it allows users to scrutinise the file and make corrections (if necessary) before exporting to db.
So if you want to export directly to the db you can implement that. Or if you want to export the transformed spreadsheet, you can also implement that. All the necessary functions are there for you to use and make slight adjustments.

It's now in your hands and yours to manage.

 

[vbaInet]

I just had to delete that post because yours wasn't there anymore.

Let's see how you get on tomorrow.

 

[vbaInet]

I found the problem, the new sample text has an extra "Language" in it:-

ID	SectionName	LineCurrCitation	fCounter	LineStart	LineEnd
4677	Abstract  1 ---------------- 7 ---------------- 11	 ---------------- 10
4678	Language	1 ---------------- 7 ---------------- 12 ---------------- 13
4679	Language	18 ---------------- 1 ---------------- 15 ---------------- 17
4680	Citation	18 ---------------- 2 ---------------- 19 ---------------- 18

So it looked wrong, however I worked through it and it makes sense.

Where's the extra Language field coming from Tony?

 

[vbaInet]

I see. It shouldn't have been a problem though since it's way over there.

 

[vbaInet]

Oh ok. Do you really use those line numbers you created?

 

[Sergo]

It's absolutely necessary to comment code especially when you're done with it.

I believe the file attached (Access 2000) should do it. I didn't have time for any fancy forms so just open the module and run the function called TransposeFile from the Immediate Window:

?TransposeFile("[COLOR="Blue"]Path to file[/COLOR]\Sample.txt")

All the relevant comments are there.

One thing to note is that it transposes the text file to an Excel sheet and the reason behind that is because it allows users to scrutinise the file and make corrections (if necessary) before exporting to db.
So if you want to export directly to the db you can implement that. Or if you want to export the transformed spreadsheet, you can also implement that. All the necessary functions are there for you to use and make slight adjustments.

It's now in your hands and yours to manage.

WOW, works like a champ! I thought we go to plan B, but this way is way better. One think. I know I'm asking to much. Can you change it, so it's saves in database. I tried to change it, I'm getting lost. This way I'll make this table as temperately, so the user can make corrections to data and then click submit button. I'm trying to reduce steps for the user. I'll do all this, all I need is Transpose data in the same database. But rest of it is amazing, it will take some time to understand it! Thank you very much!

 

[vbaInet]

One think. I know I'm asking to much. Can you change it, so it's saves in database. I tried to change it, I'm getting lost. ...it will take some time to understand it!

Like I said, it's in your hands now so "take some time to understand it" and add the extra layer of functionality. You did say "I can read the code and follow it, and learn a lot from it" and "Once I get code working, rest of it I can do it myself." so get coding.

 

[Sergo]

You right, i should try it.
Thank you very much all for your help!

 

[vbaInet]

Give it some good thought you'll get it working in no time. All the field names and functions are there

Uncle Gizmo might have something for you too.

 

[Sergo]

One more question.
Once its transfer to the spreadsheet, why is Citation filed is blank? Is it supposed to be like this so user can add?

 

[vbaInet]

Look at your Sample file.

 

[Sergo]

I'm looking and I see Citation 1, Citation 2 and so on..

 

[vbaInet]

Citation is a column name so it's expecting data below Citation X.

 

[Sergo]

I see, there is no data below.

 

[Sergo]

Looks like it needs spesial case just for Citation field.

 

[vbaInet]

You should be able to figure it out.