Coverting Text File to Table

[Garry]

I need to create a table showing part number data for items we manufacture.
Unfortunately this is only available in a printed (i.e. on paper) format.

So far I have done the following:
1) Printed to a text file
2) Imported text file to Access
3) Deleted all unwanted rows

After this stage I had a table with a single column. Example:

Item: Banana
Colour: Yellow
Price: 25
Item: Apple
Colour: Green
Price: 40

I have then used the text functions to extract the data I need from each row
into a table, but doing this although the column headings are correct, the
data is across a number of rows. Example:

Item Colour Price
Banana
Yellow
25
Apple
Green
40

I need to get all the data for each record into the same row.

Please can anyone suggest a simple way of doing this.

Many thanks,
Garry.

 

[scubadiver]

I think you might be better of importing the text file into Excel before
importing into Access.

 

[scubadiver]

I meant sorting the information out in Excel before importing into Access.

 

[Garry]

Thanks for suggestion. I have already used Excel to sort out the data.

What I was trying to acheive was a way of doing it in Access so that if I
ever needed to update the data I could do so with as little manual input as
possible - i.e. all I needed to do was update the original text file and
Access would automatically be able to pull the new data into a table.

However if this is not possible then I will just need to keep on with the
Excel route.

Thanks again,
Garry.

 

[scubadiver]

I think there could be a solution. You may be able to somehow create an ID
field like so:

ID Item Colour Price
1 Banana
1 Yellow
1 25
2 Apple
2 Green
2 40

Split them into separate queries:

ID Item
1 Banana
2 Apple

ID Colour
1 Yellow
2 Green

ID Price
1 25
2 40

Create a new query and show the three queries above. drag the ID field from
one query to the next so you get a single line through all three. Drag the
first ID field and then item, colour and price. Hopefully you would then get

ID Item Colour Price
1 Banana Yellow 25
2 Apple Green 40

Then you can use that as the basis for a make table query. You can then
import and run through all the above automatically.

Hope that gives you an idea.

 

[Garry]

Sorry if this is a dumb question, but how would I add the ID field to the
original table? Can I do this in a query?

 

[scubadiver]

WARNING: I am thinking on the hoof here!

Add an ID field to the table and make it an autonumber. When you import your
information like:

Item: Banana
Colour: Yellow
Price: 25
Item: Apple
Colour: Green
Price: 40

It will hopefully be:

1 Item: Banana
2 Colour: Yellow
3 Price: 25
4 Item: Apple
5 Colour: Green
6 Price: 40

In the query of the following create an expression so that

ID Item Colour Price
1 Banana
2 Yellow
3 25
4 Apple
5 Green
6 40

becomes (divide the ID by 3)

ID1 Item Colour Price

0.3 Banana
0.6 Yellow
1.0 25
1.3 Apple
1.6 Green
2.0 40

Split the three columns into separate queries. For "item" and "colour" round
the ID up to the nearest integer. Then you can create a make-table query that
does this:

ID Item Colour Price
1 Banana Yellow 25
2 Apple Green 40

I know its a long way round but that is the best I can come up with.

 

[scubadiver]

I have managed to do this:

The following is in an Excel spreadsheet ("Info" is in the first row)

Info
Item: Banana
Colour: Yellow
Price: 25
Item: Apple
Colour: Green
Price: 40

Import this into "Table1"

Table1:

ID: (autonumber)
Info: (Text)

Table2:
ID2 (long integer)
Item (text)
Colour (text)
Price (Number)

SQL for "Query1"

SELECT [ID]/3 AS ID1, -Int(-[ID1]) AS ID2, Left$([info],InStr([info],":")-1)
AS ProdLab, Mid$([info],InStr([info]," ")+1) AS Info1,
IIf([Prodlab]="Item",[Info1],"") AS Item, IIf([Prodlab]="Colour",[Info1],"")
AS Colour, IIf([Prodlab]="Price",[Info1],"") AS Price
FROM Table1;



SQL for "Item"

SELECT Query1.ID2, Query1.Item
FROM Query1
WHERE (((Query1.Item)<>""));

SQL for "Colour"

SELECT Query1.ID2, Query1.Colour
FROM Query1
WHERE (((Query1.Colour)<>""));

SQL for "Price"

SELECT Query1.ID2, Query1.Price
FROM Query1
WHERE (((Query1.Price)<>""));



SQL for "Query2" ("make table" query)

SELECT Colour.ID2, Item.Item, Colour.Colour, Price.Price INTO Table2
FROM (Colour INNER JOIN Item ON Colour.ID2 = Item.ID2) INNER JOIN Price ON
Item.ID2 = Price.ID2;

Let me know!