Data is not ordered

[Madzark]

Hi guys,



I need your help!

I have to import and read external data from a txt file.
The big issuee is that the data is not ordered by columns, but it is randomly allocated in a row.

Normally you should have a table like this:

Column A	Column B	Column C
Obi-wan	Kenobi	jedi
Anakin	Skywalker	Sith

According to this, the name should appear always in Column A, surname in Column B, and soon on. Instead i have something like this:

Row 1	AObi-Wan BKenobi CJedi
Row 2	CSith AAnakin BSkywalker

I've always a prefix that identifies the fields (A for name, B for surname, etc), but the data is not tabularized and, above all, is not ordered. You can have in a row the name and then the surname; and in an other row first the surname and then the name.

How can I import this data and make an ordered table like the one in the first example?

Thank you very much!

[orange]

??? Show readers some records from your text file.

[Madzark]

Data from txt is something like this:

AU001004        71652,63AU001008        71652,63AU001009        14330,53
AU001008        49000,00AU001004        80000,00AU001009         9800,00
AU001004        77312,14AU001008        35312,14AU001009        15462,46

While I want to have a table like that:

AU001004	AU001008	AU001009
71652,63	71652,63	14330,53
80000,00	49000,00	9800,00
77312,14	35312,14	15462,46

Please consider the ordination of the second row, where the element AU001004 shoes up after AU001008.

Hope the above is helpful

[orange]

When Access does an import from a text file, each record forms a row, and each row has the same "fields".

eg: Name, Location, Phone

Your data shows different fields in each row of your "intended import" file, so you will have to process the text file to put the data into proper/expected format.

[Madzark]

Exactly.
But I cannot edit the text file.
So Access is not able to read the information row by row?

[orange]

Please explain this

But I cannot edit the text file.


eg:

Make a copy of the text file, manipulate it to get the records in the format used by Access. then import that file.

[CJ_London]

you seem to be saying this is a single row of your text file:

AU001004 71652,63AU001008 71652,63AU001009 14330,53

And you want to get

AU001004 71652,63
AU001008 71652,63
AU001009 14330,53


which can then be interpreted with a crosstab query as

AU001004..AU001008..AU001009
71652,63...71652,63...14330,53

there is no easy generic answer unless one know all the rules.

How many AU.... items are there?
Do they always start with AU?
do the same AU elements appear on each row, but perhaps in a different order?
if not, i.e
AU001004 71652,63AU001008 71652,63AU001009 14330,53
AU001006 71652,63AU001008 71652,63AU001003 14330,53

what would your final result look like?

If they always start with AU, you can use the replace function to insert a character (say |) before AU, then split the row on that character giving 3 elements which can then each be treated separately to populate a table - together with a row number or other identifier

[Micron]

So Access is not able to read the information row by row?

Just about anything is possible if one knows the input and required output AND there is some sort of consistency or pattern. You say it's random, but is it really? There is often order amongst chaos, but your limited data example is not real conclusive.

It may be possible with a query (others here have devised some elegant solutions for such problems) but to me, this looks too complex for that. Here's a take on how it's possible to split the data and remove spaces, but as Ajax has stated, it depends on many factors.

Sub testSplit()
Dim strInput As String, strOutput() As String
Dim i As Integer

strInput = "AU001004        71652,63AU001008        71652,63AU001009        14330,53" & _
"AU001008        49000,00AU001004        80000,00AU001009         9800,00" & _
"AU001004        77312,14AU001008        35312,14AU001009        15462,46"

strOutput = Split(strInput, "AU")
For i = 1 To UBound(strOutput)
   Debug.Print Left(strOutput(i), InStr(1, strOutput(i), " ") - 1) & "-" & LTrim(Right(strOutput(i), InStr(1, strOutput(i), " ") + 1))
Next

End Sub

The output is
001004-71652,63
001008-71652,63
001009-14330,53
001008-49000,00
001004-80000,00
001009-9800,00
001004-77312,14
001008-35312,14
001009-15462,46
I doubt this is the format you're looking for, but it does show that something is possible based on your posts.

[ssanfu]

Exactly.
But I cannot edit the text file.
So Access is not able to read the information row by row?

Yes, BUT.... YOU must write code to handle each line of data. By that I mean you will have to:
- Open the text file
- Read each line
- Split each line into the values
- Append the values to the table
- loop through the text file
- Close the text file when all lines have been read.

How are your VBA skills?????

Attached is an example db with a text data file. The data in the file is not in USA format, so it is giving me fits.

The text file can be places anywhere, just remember where you put it.
You might need to set a reference to Microsoft DAO 3.6 Object Library.