Text file format conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a delimited text file in the following format
FieldOne FieldTw
A
B
C
A
B
C

The problem is that the first 3 rows are the same record and the next 3 are another record and so on. The file itself is about 6 mb and has 500,000 rows but is actually only 18,000 records (each record actually has 32 columns rather than the 3 shown above). So that I can actually use this data, I need to convert it into the normal format of
A B
1 3
2 9

I am thinking that if one can create a query that returns the source data in the following format, I can use a crosstab query to get the data into the final form

RecordId FieldOne FieldTw
1 A
1 B
1 C
2 A
2 B
2 C

The problem is I don’t know how to populate this additional column through a query. I could generate vba to do this, but then I would need to import the file (rather than link which is what I would like to do) in order to add the additional column

Thank you in advance
 
Hi,

If you don't care to have the last C going with the first A and B, then:


SELECT COUNT(*) As Rank, a.FieldOne, a.FieldTwo
FROM myTable As a INNER JOIN myTable As b
ON a.FieldOne=b.FIeldOne
WHERE a.FieldTwo >= b.FieldTwo
GROUP BY a.FieldOne, a.FieldTwo



( the query is "graphically" reprehensible in the query designer).



Save that query, then, run the crosstab wizard to get the un-normalized
view:

Rank A B C
1 1 3 1
2 2 9 7


In the crosstab, the rank will be the Group, FieldOne will supply the new
fields name (A, B, C, ... ) and you would aggregate with LAST on FieldTwo.



Hoping it may help,
Vanderghast, Access MVP


JimmyJ said:
I have a delimited text file in the following format:
FieldOne FieldTwo
A 1
B 3
C 7
A 2
B 9
C 1

The problem is that the first 3 rows are the same record and the next 3
Click to expand...
are another record and so on. The file itself is about 6 mb and has 500,000
rows but is actually only 18,000 records (each record actually has 32
columns rather than the 3 shown above). So that I can actually use this
data, I need to convert it into the normal format of:
A B C
1 3 7
2 9 1

I am thinking that if one can create a query that returns the source data
Click to expand...
in the following format, I can use a crosstab query to get the data into the
final form:
RecordId FieldOne FieldTwo
1 A 1
1 B 3
1 C 7
2 A 2
2 B 9
2 C 1

The problem is I don't know how to populate this additional column through
Click to expand...
a query. I could generate vba to do this, but then I would need to import
the file (rather than link which is what I would like to do) in order to add
the additional column.
 
Back
Top