Linking non-related things (1 Viewer)

[alexfwalker81]

I have a problem that I don't how to get round, and I feel that VB is likely to be the answer here.

My database imports files in the format shown above - this is fixed as it's produced by another system which has no flexibility in the output. I only need the 4th line, which is easy to isolate within a query. However, the tricky part is that I need the 'BGG001' (this varies constantly, so I can't 'hard-code' it) on the same line so that I can work with a query which looks like;

BGG001 | 700208544-1443919 | 001 | 82296

I don't know how I can achieve this. 'BGG001' is always one 'cell' above the long number '700208544-1443919' (again this varies constantly), so I feel like this could be a useful key to link, but have no idea how to get the 'BGG001' into a place where I can use it!

 

[gemma-the-husky]

Note that you can't assume that a table/file will be imported in the same order as in the source. The only way to be able to tell is if you have a line reference number in the file.

Is this a text file? In which case read a line at a time, and examine the file contents.

needs a bit of tidying, but

open filename for input as filenumber
dim textline as string

while not eof(filenunmer)
    line input filenumber, textline
wend
close filenumber

 

[alexfwalker81]

Note that you can't assume that a table/file will be imported in the same order as in the source. The only way to be able to tell is if you have a line reference number in the file.

Is this a text file? In which case read a line at a time, and examine the file contents.

needs a bit of tidying, but

open filename for input as filenumber
dim textline as string

while not eof(filenunmer)
    line input filenumber, textline
wend
close filenumber

Thanks for this, much appreciated. I'm not sure how it works though!

 

[MajP]

Are you importing a single record at a time? If you can get the 4th line, can you do a query to get the 1st line? Then do one more query doing a cartesian join to get all information. Use that for you insert

 

[Pat Hartman]

Note that you can't assume that a table/file will be imported in the same order as in the source.

That's not quite true. Flat files such as .txt and .csv are just that, flat. What you see is what you get. spreadsheets though can be deceptive. The rows may not be in physical order so they may import into Access in a different order than what you are seeing.

 

[gemma-the-husky]

That's not quite true. Flat files such as .txt and .csv are just that, flat. What you see is what you get. spreadsheets though can be deceptive. The rows may not be in physical order so they may import into Access in a different order than what you are seeing.

Thanks Pat
I just assumed any file might be imported in a different order.