TransferText Issue

[Zachariah]

I'm importing from a text file with this code:

Dim stFullPathTest As String
Dim stFullPathTest2 As String

stPath = "C:\FooSample.txt"
stName = "tblFooSample"

DoCmd.TransferText acImportDelim, "EDIFOO", stName, stPath

I get a resulting table but the order of the records isn't
consistent with their order in the text file. There is
consistency if I do the File ... Get External
Data ...Import with the EDIFOO specification within
Access, but if I try it through TransferText, everything
in the resulting table isn't ordered correctly. Any ideas
why this would happen?

 

[Bruce M. Thompson]

DoCmd.TransferText acImportDelim, "EDIFOO", stName, stPath

I get a resulting table but the order of the records isn't
consistent with their order in the text file. There is
consistency if I do the File ... Get External
Data ...Import with the EDIFOO specification within
Access, but if I try it through TransferText, everything
in the resulting table isn't ordered correctly. Any ideas
why this would happen?

An Access table is "an unordered bucket of data" and cannot even be relied upon
to maintain a specific order over a period of time. If you need to view your
records in a certain order, you will need to ensure that there is a unique
record identifier (a field that stores a unique value in each record such as an
autonumber or timestamp field) and generate your view with a query, sorting on
that unique value.

 

[Zachariah]

My specification EDIFOO is already creating a primary key,
but that's irrelevant. If my flat file is

0001.001|John
0001.002|Smith
0002.001|Fred
0002.002|Jones

When I do the import programmatically with TransferText my
table is

PKEY FIELD1 FIELD2
1 0002.001 Fred
2 0002.002 Jones
3 0001.001 John
4 0001.002 Smith


See my problem? The order has been re-arranged. Why? Isn't
this more work for access than just leaving the order
alone during the import?

 

[Bruce M. Thompson]

See my problem? The order has been re-arranged. Why? Isn't

this more work for access than just leaving the order
alone during the import?

I see the problem. The MDB file format has never been fully documented and I
don't know how Access manages imported data in memory, so I really have no
better answer for you with respect to this issue. If the order is important to
you, simply create a query view sorted by the relevant field.

 

[Zachariah]

I'm trying another tack in using a Schema.ini file. The
code:

DoCmd.TransferText
acImportDelim, "C:\Schema.ini", "tblFooSample", "C:\FooSmpl
e.txt"

But I get an

Run-time error '3625':

The text file specification 'C:Schema.ini' does not exist.

I've tried passing the location of the Schema.ini file as
a string, leaving out the C:\, nothing helps. Any ideas on
this one?

 

[John Nurick]

Hi Zachariah,

Schema.ini must be in the same folder as the file you are importing. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the structure.

But I'm with Bruce: there's no way you can control the order in which
Access stores data in its tables, and if you want to see the data in a
particular order the only way is to use a query sorted on the field(s)
or expression in question.

I'm trying another tack in using a Schema.ini file. The
code:

DoCmd.TransferText
acImportDelim, "C:\Schema.ini", "tblFooSample", "C:\FooSmpl
e.txt"

But I get an

Run-time error '3625':

The text file specification 'C:Schema.ini' does not exist.

I've tried passing the location of the Schema.ini file as
a string, leaving out the C:\, nothing helps. Any ideas on
this one?

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Zachariah]

My file Schema.ini is in the same folder as the
FooSample.txt text file I'm trying to import, the C:\
folder. Here is why I need to do the import:

The data in my text file is two fields separated by a |
symbol. The data is laid out by row in segments. The
first x number of rows regard a client, the next x number
of rows regard their liability information. The next x
number of rows are a series of credit scores. Each row
has a segment number (e.g. 0001.001) So an example of a
credit score is

0611.000
0611.001|1
0611.002|EXP
0611.003|SMITH
0611.005|634

etc.

There can be several of these 0611.000 series'

My code is looking through the resulting imported table
using FindNext to find the right segment value and then
pull the info on the other side of the | into a
variable. However not every series of credit information
has all the same segment numbers. One may be missing a
name. Another may be missing a score. Yet I have to bring
them all in. So my FindNext code also has an If Then
statment checking the NoMatch to see if the cursor
travelled to many rows. If it has it means its probably
pulling data from the next 0611 segment and that's not
the data I'm looking for. I can't do a sorted query
because then all my 0611.001s would be bunched together
and I'd have no idea which ones went with the rest of
their subsequent data. FindNext is the only way to work
through the table. That's why the imported table has to
look exactly like the original text file. Granted I
could just import it as one field and do some string
examination to pull my data but that means editing my
entire code to do so.

-----Original Message-----
Hi Zachariah,

Schema.ini must be in the same folder as the file you are importing. See
http://msdn.microsoft.com/library/default.asp? url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the structure.

But I'm with Bruce: there's no way you can control the order in which
Access stores data in its tables, and if you want to see the data in a
particular order the only way is to use a query sorted on the field(s)
or expression in question.

I'm trying another tack in using a Schema.ini file. The
code:

DoCmd.TransferText
acImportDelim, "C:\Schema.ini", "tblFooSample", "C:\FooS mpl
e.txt"

But I get an

Run-time error '3625':

The text file specification 'C:Schema.ini' does not exist.

I've tried passing the location of the Schema.ini file as
a string, leaving out the C:\, nothing helps. Any ideas on
this one?

so
I really have no the
order is important to

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

As I read it: (a) your code needs to process the records in the order in
which they appear in the textfile from which they are imported, and (b)
there's no key field or combination of fields in the data that would
permit it to be sorted into that order if it's ever lost.

In that case there are two ways to go:

1) Modify your existing code so that instead of importing the textfile
and then processing the data, it opens the textfile itself and reads the
data line by line, splitting each line on the "|" and processing the
fields as needed. This guarantees that the records are processed in the
order in which they appear in the textfile.

2) Add a key to the data before you import it, e.g. by adding a line
number field to the textfile. The Unix "nl" utility is designed for the
job and a Windows version can be downloaded from
http://unxutils.sourceforge.net/, or if Perl is installed on your
computer this command will do it:
perl -ne "print qq($.\|$_)" Infile.txt > Outfile.txt
Then use a query sorted on this field to ensure that the data is
processed in the order you need.

My file Schema.ini is in the same folder as the
FooSample.txt text file I'm trying to import, the C:\
folder. Here is why I need to do the import:

The data in my text file is two fields separated by a |
symbol. The data is laid out by row in segments. The
first x number of rows regard a client, the next x number
of rows regard their liability information. The next x
number of rows are a series of credit scores. Each row
has a segment number (e.g. 0001.001) So an example of a
credit score is

0611.000
0611.001|1
0611.002|EXP
0611.003|SMITH
0611.005|634

etc.

There can be several of these 0611.000 series'

My code is looking through the resulting imported table
using FindNext to find the right segment value and then
pull the info on the other side of the | into a
variable. However not every series of credit information
has all the same segment numbers. One may be missing a
name. Another may be missing a score. Yet I have to bring
them all in. So my FindNext code also has an If Then
statment checking the NoMatch to see if the cursor
travelled to many rows. If it has it means its probably
pulling data from the next 0611 segment and that's not
the data I'm looking for. I can't do a sorted query
because then all my 0611.001s would be bunched together
and I'd have no idea which ones went with the rest of
their subsequent data. FindNext is the only way to work
through the table. That's why the imported table has to
look exactly like the original text file. Granted I
could just import it as one field and do some string
examination to pull my data but that means editing my
entire code to do so.

-----Original Message-----
Hi Zachariah,

Schema.ini must be in the same folder as the file you are importing. See
http://msdn.microsoft.com/library/default.asp? url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the structure.

But I'm with Bruce: there's no way you can control the order in which
Access stores data in its tables, and if you want to see the data in a
particular order the only way is to use a query sorted on the field(s)
or expression in question.

I'm trying another tack in using a Schema.ini file. The
code:

DoCmd.TransferText
acImportDelim, "C:\Schema.ini", "tblFooSample", "C:\FooS mpl
e.txt"

But I get an

Run-time error '3625':

The text file specification 'C:Schema.ini' does not exist.

I've tried passing the location of the Schema.ini file as
a string, leaving out the C:\, nothing helps. Any ideas on
this one?


-----Original Message-----
See my problem? The order has been re-arranged. Why?
Isn't
this more work for access than just leaving the order
alone during the import?

I see the problem. The MDB file format has never been
fully documented and I
don't know how Access manages imported data in memory, so
I really have no
better answer for you with respect to this issue. If the
order is important to
you, simply create a query view sorted by the relevant
field.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Zachariah]

Thanks John. I went for a combination of both. I'm editing
my text file and adding unique line numbers to it and then
doing an import with a specification that indexes on the
unique line numbers.

-----Original Message-----
As I read it: (a) your code needs to process the records in the order in
which they appear in the textfile from which they are imported, and (b)
there's no key field or combination of fields in the data that would
permit it to be sorted into that order if it's ever lost.

In that case there are two ways to go:

1) Modify your existing code so that instead of importing the textfile
and then processing the data, it opens the textfile itself and reads the
data line by line, splitting each line on the "|" and processing the
fields as needed. This guarantees that the records are processed in the
order in which they appear in the textfile.

2) Add a key to the data before you import it, e.g. by adding a line
number field to the textfile. The Unix "nl" utility is designed for the
job and a Windows version can be downloaded from
http://unxutils.sourceforge.net/, or if Perl is installed on your
computer this command will do it:
perl -ne "print qq($.\|$_)" Infile.txt > Outfile.txt
Then use a query sorted on this field to ensure that the data is
processed in the order you need.

My file Schema.ini is in the same folder as the
FooSample.txt text file I'm trying to import, the C:\
folder. Here is why I need to do the import:

The data in my text file is two fields separated by a |
symbol. The data is laid out by row in segments. The
first x number of rows regard a client, the next x number
of rows regard their liability information. The next x
number of rows are a series of credit scores. Each row
has a segment number (e.g. 0001.001) So an example of a
credit score is

0611.000
0611.001|1
0611.002|EXP
0611.003|SMITH
0611.005|634

etc.

There can be several of these 0611.000 series'

My code is looking through the resulting imported table
using FindNext to find the right segment value and then
pull the info on the other side of the | into a
variable. However not every series of credit information
has all the same segment numbers. One may be missing a
name. Another may be missing a score. Yet I have to bring
them all in. So my FindNext code also has an If Then
statment checking the NoMatch to see if the cursor
travelled to many rows. If it has it means its probably
pulling data from the next 0611 segment and that's not
the data I'm looking for. I can't do a sorted query
because then all my 0611.001s would be bunched together
and I'd have no idea which ones went with the rest of
their subsequent data. FindNext is the only way to work
through the table. That's why the imported table has to
look exactly like the original text file. Granted I
could just import it as one field and do some string
examination to pull my data but that means editing my
entire code to do so.

-----Original Message-----
Hi Zachariah,

Schema.ini must be in the same folder as the file you are importing. See
http://msdn.microsoft.com/library/default.asp? url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the structure.

But I'm with Bruce: there's no way you can control the order in which
Access stores data in its tables, and if you want to

see
the data in a

particular order the only way is to use a query sorted on the field(s)
or expression in question.

wrote:

I'm trying another tack in using a Schema.ini file. The
code:

DoCmd.TransferText
acImportDelim, "C:\Schema.ini", "tblFooSample", "C:\Foo

S
mpl

e.txt"

But I get an

Run-time error '3625':

The text file specification 'C:Schema.ini' does not exist.

I've tried passing the location of the Schema.ini file as
a string, leaving out the C:\, nothing helps. Any

ideas
on

this one?


-----Original Message-----
See my problem? The order has been re-arranged. Why?
Isn't
this more work for access than just leaving the order
alone during the import?

I see the problem. The MDB file format has never been
fully documented and I
don't know how Access manages imported data in

memory,
so

I really have no
better answer for you with respect to this issue. If the
order is important to
you, simply create a query view sorted by the relevant
field.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at
http://www.mvps.org/access)
NO Email Please. Keep all communications
within the newsgroups so that all might benefit.<<


.


John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Bruce M. Thompson]

I'm glad to see you got that sorted out. I think I can now see why the *import*
order was so important to you - the additional information you provided to John
was the clue that I didn't have. And thanks to John for info on that useful
utility.