ASCII Import problem

  • Thread starter Thread starter James Quigley
  • Start date Start date
J

James Quigley

The date being imported is in an ASCII file in a specific field order. I
completed the import and the field order is rearranged. How can I stop this?
 
Tell us some details... how did you do the import? I assume this is from a
text file? Was the table already set up in your database before you did the
import? Did you use an import specification? Show examples of data in the
file, and data from the table.
 
I'm jumping in here because I have the same problem. I am using Access 2003
SP3. The table is created with
Cnn.execute "CREATE TABLE " & Tbl0 & "(" & VFields & ")"
where
VFields = "VID char(15),Tw char(25),Pr char(5), ..." [35 fields, all of
type char(n)]
Then data are inserted with
Cnn.execute "INSERT INTO " & Tbl0 & " VALUES(" & ZT & ")"
where
ZT = "'a','b','c', ..." coming from a text file.
The text file was sorted; the resulting table is no longer sorted.
I would have used the "ORDER BY " feature but I'm sorting on 8 fields and
Access doesn't do the sort right either.
Thanks ahead of time for any suggestions.
 
Never make any assumptions about the order of data in tables. Relational
database theory states that tables should always be treated as unordered
sets.

If the order of the data is important, create a query with the appropriate
ORDER BY clause, and use that query anywhere you would otherwise have used
the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GerryM said:
I'm jumping in here because I have the same problem. I am using Access
2003
SP3. The table is created with
Cnn.execute "CREATE TABLE " & Tbl0 & "(" & VFields & ")"
where
VFields = "VID char(15),Tw char(25),Pr char(5), ..." [35 fields, all of
type char(n)]
Then data are inserted with
Cnn.execute "INSERT INTO " & Tbl0 & " VALUES(" & ZT & ")"
where
ZT = "'a','b','c', ..." coming from a text file.
The text file was sorted; the resulting table is no longer sorted.
I would have used the "ORDER BY " feature but I'm sorting on 8 fields and
Access doesn't do the sort right either.
Thanks ahead of time for any suggestions.




Ken Snell MVP said:
Tell us some details... how did you do the import? I assume this is from
a
text file? Was the table already set up in your database before you did
the
import? Did you use an import specification? Show examples of data in the
file, and data from the table.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
The whole problem started because the ORDER BY with 8 fields out of 35 did
NOT return the correct order. So I pre-ordered the data and assumed etc.

I got around the problem by adding a running index field to the pre-ordered
data, importing the whole thing, and ORDER BY it on retrieving the table.

There is probably a way to add such an autoindex/autoincrement field
programmatically upon importing but I haven't figured it out yet. I know you
can do it using the Import Wizard, but that's not in the right spirit.

There is always something else to learn. Thanks for the info!

Gernot Metze

Douglas J. Steele said:
Never make any assumptions about the order of data in tables. Relational
database theory states that tables should always be treated as unordered
sets.

If the order of the data is important, create a query with the appropriate
ORDER BY clause, and use that query anywhere you would otherwise have used
the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GerryM said:
I'm jumping in here because I have the same problem. I am using Access
2003
SP3. The table is created with
Cnn.execute "CREATE TABLE " & Tbl0 & "(" & VFields & ")"
where
VFields = "VID char(15),Tw char(25),Pr char(5), ..." [35 fields, all of
type char(n)]
Then data are inserted with
Cnn.execute "INSERT INTO " & Tbl0 & " VALUES(" & ZT & ")"
where
ZT = "'a','b','c', ..." coming from a text file.
The text file was sorted; the resulting table is no longer sorted.
I would have used the "ORDER BY " feature but I'm sorting on 8 fields and
Access doesn't do the sort right either.
Thanks ahead of time for any suggestions.




Ken Snell MVP said:
Tell us some details... how did you do the import? I assume this is from
a
text file? Was the table already set up in your database before you did
the
import? Did you use an import specification? Show examples of data in the
file, and data from the table.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The date being imported is in an ASCII file in a specific field order.
I
completed the import and the field order is rearranged. How can I stop
this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top