Reformating Records

[mrtroy]

Newbie: I have some records that I am importing from a text file into Access. There are 4 fields for each record, but the data in the source file is "not flat"., i.e. it looks like this:

Record1.field1
Record1.field2
Record1.field3
Record1.field4
Record2.field1
Record2.field2
Record2.field3
Record2.field4
.
.
.
Is there a way to import the record so that the info goes "across" or is there a way to convert it after importing? Thanks in advance.

 

[lameid]

If there is truly a way to determine what field and record a row belongs to, you could write a series of queries to populate the fields in the correct record. Otherwise, you are stuck reading the file programatically or a really creative series of queries (I'm thinking import to a table with an autonumber key and use MOD to determine the field and Integer division to determine the record but this method is prone to errors but quick and dirty).

That leads to the question, do you know what record each field belongs to by data in the file? Do you know the field? If not, does it matte (i.e. a list of items on an order)?

 

[mrtroy]

Each record is composed of four fields. The table that I am importing has only 2 columns. The data I need formated is in the second column and the data in the first column repeats itself for each new record. Here is a better example:

invnum..123
invdat..03/11/2004
refnum..890
disnum..294
invnum..549 (This is the beginning of the second record.)
invdat..03/02/2004
refnum..329
disnum..321
...

 

[lameid]

I hope this is a one time thing otherwise this less than elegant solution could get ugly...

Create a table with a Fieldname column, a data column, a third column that has the autonumber datatype that increments by 1 (default) and a fourth column that is a Long integer (number).

Import your text file into the table.

Update the values in the fourth column with the expression below (assuming Column3 is named column3). Note the use of the backslash in the expression. That performs integer division instead of the forward slash which will return a decimal.

column3\4 + IIF(column3 Mod 4 > 0,1,0)

Make a crosstab query and make the 4th column the row Heading, the fieldname column the column heading and the data the value. Group by the 4th Column, the field name and use first on the data field (so Access doesn't have a fit). Finally you can make this query an append or make a table. You may need to change the datatypes if you make a table.

I hope that's enough detail...
This autonumber trick isn't a good way to go if you are going to do this more than once because it relies on the first value begining with an autonumber of 1.