import txt file with multiple headers

[vojinb]

Hi,


I have txt file which have header and footer occurring every 70-80 lines. how to import file avoiding this header and footer, just to get data into table?
Thanks

[June7]

The Access import utility might not handle this cleanly. The values that do not match datatype defined for respective fields will error. Errors are kicked to an ImportErrors table. So your real data might pass through fine. Try it and find out. If the header/footer rows do come in you can delete those.

Otherwise, would have to use VBA code to read the file and write to table only those lines that are actual data.

[vojinb]

I know that, I'm looking of example of VBA code for reading lines of txt file, and for example in code :
If "line" starts with "some text" ignore next five lines.

I need of example of code in VBA which reads line by line

[rpeare]

dim f
dim fs
dim sSourceFile
dim sline

set fs = createobject("scripting.filesystemobject")
sSourceFile = "<path and name of your file>"
f = fs.opentextfile(ssourcefile)

do while f.atendofstream <> true
    sline = f.readline
    msgbox sline
    f.readline
loop

[vojinb]

Hi ,
this code is not working.
What I need is that for txt file like:

Header


Class 1
Value 1 Value 2 Value 3
Value 1 Value 2 Value 3
Value 1 Value 2 Value 3
.
.
Class 2
Value 1 Value 2 Value 3
Value 1 Value 2 Value 3

to have code which reads file line by line and when it comes to header ignore it, when it comes to class1 puts in table in different columns values 1,2,3

[rpeare]

The code works, you're just being lazy

I've given you the main thing you need (filesystemobject code). You have to figure out how to determine which lines need to be ignored, what you want to do with the 'good' lines etc, I can't do that for you.

[SteveF]

Here's another possible solution:

1. Import the "raw" data into a temporary table, with no effort made to distinguish between header and detail records.

2. Now use a query to find & append the detail records from the temporary table to the final table.

Steve

[vojinb]

I have imported data into raw table and now I have different problem. When I try to delete all those which I don't need via query, access gives me a message "You are about to delete "number of records", but after delete those data are still in table, and after repeating delete query it gives me message "You are about to delete 0 records", but data are still there and after refresh and after waiting of 10-15 minutes.
here is query:
DELETE *
FROM temp
WHERE field1 Like '*RE000010*' And '* AREA* ' And '* TO:*' And '*---- ------------*';

[rpeare]

WHERE field1 Like '*RE000010*' And '* AREA* ' And '* TO:*' And '*---- ------------*';

what field does *area* refer to?

what field does " to :* refer to

what field does *---------------* refer to?

you've only told the query which field *RE000010* refers to. If they all refer to field 1 then you have to have

WHERE field1 Like '*RE000010*' And field1 like '* AREA* ' And field 1 like '* TO:*' And field1 like '*---- ------------*';

but since you are using AND I suspect they are different fields so you'd need:

WHERE field1 Like '*RE000010*' And field2 like '* AREA* ' And field3 like '* TO:*' And field4 like '*---- ------------*';

However you really only need one of these like statements if nothing else in your database is going to match them. but not being familiar with the entire possible dataset I couldn't say.

[SteveF]

I'll also point out that you're including a needless extra step: deleting the unwanted records from the temporary table.

I haven't seen an actual sample of your data, so I'm not totally certain, but I'd be willing to bet that if you can identify the unwanted records, you can also positively identify only the records to be transferred.

Then it just becomes a matter of using a query along the lines of:

INSERT INTO [FinalTableName] ( Fieldname1, Fieldname2, ... ) SELECT (Fieldname1, Fieldname2, ... ) FROM [TemporaryTable] WHERE (criteria which identify records to be transferred);

Steve