so simple yet it eludes me

[pensived]

Please let me know if posting in the wrong place, and let me say thank you upfront.

I have a strange text file that contains values separated by a 2 character abbreviation. ie. "PN" and "QT" I can import just fine, but I can't seem to get the data to separate into separate columns.


This is what the text file looks like.
PN=DSAI6612
QT=3
PN=CT22L
QT=3


after I import and filter through a query it looks like


Field1 Field2
PN DSAI6612
QT 3
PN CT22L
QT 3


Problem is, I need it to look like this


Field1 Field2
DSAI6612 3
CT22L 3




I don't know what I'm missing.
Please help!!


Thank you in advance

 

[John Big Booty]

Welcome to the forum.

I'd do this in a number of steps;

1. Import the data to a new table (you've done this bit)
2. Create a query to first select all (and only) the PT, and append this to a new table
3. Create another query to select all (and only) the QT and update you field 2 to that value.

 

[Simon_MT]

I pretty sure that specify the delimiter on import I always used the Pipe "|" character, because the fields had commas, so you should be able to specify the "=" sign. I import text files rarely so I'm a bit rusty.

Simon

 

[pensived]

The problem lies in what happens to the data after the import. You are correct though, I have the import set to the = as the delimiter. But the data winds up in the same column.

 

[John Big Booty]

Actual there is an additional step I missed prior to step two, you will need to add a pseudo key to your table so that you can relate the first PT to the first QT and the second PT to the second QT and so on. This article may help you do that.

 

[gemma-the-husky]

the problem with this, is that the file itself is not structured in a "regular" way. ie - each row is not self contained, but relates to the previous row

so, i would process this file manually, by reading it in a row at a time, although this will reqire some moderately advanced VBA skills