delimited file, starting at position...

[kerrigirrl]

i have a file that i want to link. every field after position 1082 is separated by a ;. The first part of the file, however is not. I'd like to separated the fields like this:
Field 1 = everything before 1082.
Field 2 = everything from 1082 to first ;.
Field 3 = from first ; to second ;.
Field 4 = from second ; to third ;.
and so on...

But when you choose to link by delimited fields, it won't allow you to create other fields separaters.

any suggestions?
kerri

 

[tlbroadbent]

You could link the file twice with two different names.

First link would skip every field after column 1081. You could specify the delimiter for this link or link as fixed width.

The second link would skip all fields up to 1081 and specify the ";" delimiter.

You would need to be careful about properly handling rows from each table that belong together. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[kerrigirrl]

there's nothing separating the first part of the file from the 1082. it won't let me set a field separater there when i link it with delimited fields. it kinda looks like this:

aolkj akdfu jaf45621 jalkjlkajsd kja dkaljf 5646 216543 154;62135432;46543210;5641

where 6 or 7 or sometimes 10 positions before the first ;, is position 1082.

does that make sense?

 

[tlbroadbent]

It makes sense and you can still do as I recommended. Let me explain.
[ol][li]Start the Link Text Wizard and choose the file.
[li]Select Fixed Width
[li]On the next screen, mark the field breaks including column 1082. Remove all field breaks after column 1082.
[li]On the next screen set up the first 1082 characters for import. Mark the field after 1082 as Do not import (Skip
[li]Click Next and on the next screen type a table name.
[li]Click finish.[/ol]Now we will link the table again.[ol][li]Start the Link Text Wizard and choose the file.
[li]Select Delimited
[li]Choose the delimiter ";"
[li]On the next screen set up the characters after 1081 for import. Mark the field before 1082 as Do not import (Skip
[li]Click Next and on the next screen type a table name.
[li]Click finish.[/ol]You now have two tables, one displaying the data upto 1082 and the other displaying the data after.

I've just tested this technique and found that if I open both tables at the same time, I get the layout of whichever was opened first. So this technique is limited in that yo can't open both links to one table at the same time. You can work around that limitation by copying the file and linking one file as fixed width and the other as delimited.

I hope this helps. It's only one suggestion. You could also import the file to a table and parse it with queries or code. You could read each line of the table in a module, parse the line and insert into a table. There are numerous possibilities. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.