Import text specification file

[M Skabialka]

I am trying to create a specification file for importing a text file into an
Access 2007 database.
If I select import to a new table I can then go in and change field type
from Long Interger to Text because it is misinterpreting some data, e.g.
0001 is not a number in this field of the database. I can save that
specification.
But if I select import to a table and select the table, I am unable to
change the data type of any field. If I use the specification file created
above it says can't find Field1, Field2, etc because those are not the right
field names. But then I get errors on the import.

Is there a way around this?
Mich

 

[M Skabialka]

I tried the new table route and went into each field and gave it the correct
name and file type for my table, then saved that specification file. For
this table, every field is a text value because of the nature of the data
(even the date info).
But I still get errors when I try to use that spec file to import the data:
Invalid argument.

A sample record from the text file:
7-50000|8V613||E|R|26-AUG-2008||0001|0001|R|0001|0001|29|PDF|PDF|PDF|||8613|EC01|pdf|.|G22|||N|U|N|N|N||||BD||||||D|NT|EC01|8613||||BA|1.0|

 

[Ken Snell \(MVP\)]

I am trying to create a specification file for importing a text file into
an Access 2007 database.
If I select import to a new table I can then go in and change field type
from Long Interger to Text because it is misinterpreting some data, e.g.
0001 is not a number in this field of the database. I can save that
specification.
But if I select import to a table and select the table, I am unable to
change the data type of any field. If I use the specification file
created above it says can't find Field1, Field2, etc because those are not
the right field names. But then I get errors on the import.

When you use an Import Specification to an existing table, and the text file
does not have field names in the first row, then ACCESS "substitutes" the
generic Field1, Field2, etc. names for the text file's columns, and then
tries to find those field names in the existing table.

Solutions:

1) Add a first row to your text files that provides the table's field names,
and tell the import specification that the file does have field names /
headers.

OR

2) Import the data into a temporary table that uses the Field1, Field2, etc.
names, and then use an append query to copy the data into your permanent
table from that temporary table. Note that the temporary table can remain in
the database --you don't need to create it each time -- but you'll need to
run a delete query on that table before you import more data into it.

 

[M Skabialka]

Solutions:

1) Add a first row to your text files that provides the table's field
names, and tell the import specification that the file does have field
names / headers.

These text files are sent to us without the headings.

OR

2) Import the data into a temporary table that uses the Field1, Field2,
etc. names, and then use an append query to copy the data into your
permanent table from that temporary table. Note that the temporary table
can remain in the database --you don't need to create it each time -- but
you'll need to run a delete query on that table before you import more
data into it.

When I do this the text fields that should say 0001 are turned into a number
1. I can't have the data changed from the original.

 

[Ken Snell \(MVP\)]

These text files are sent to us without the headings.


When I do this the text fields that should say 0001 are turned into a
number 1. I can't have the data changed from the original.

Build a table with the correct datatypes for each field. If a field should
have a TEXT datatype, then make the field that datatype. Use this table as
the temporary table. Then continue with what I have suggested in the 2)
option.
--

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

 

[M Skabialka]

I made a local copy of the table without data, and was able to import
without the error.
Then I made an empty copy of the same table in the back end and linked to
it. It gave me the error when I tried to import the file.

So, something about being a linked table is the problem.
Mich

 

[Ken Snell \(MVP\)]

Somewhere in my memory is a fleeting memory about this situation. I cannot
say for certain, but I'm thinking that you cannot import into a linked
table. However, this link shows a post thread that suggests this can be
done:
http://en.allexperts.com/q/Using-MS-Access-1440/2008/10/transferdata-import-data-2.htm

Specifically, what error do you get when you try to import to the linked
table?
--

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

 

[M Skabialka]

Here's what I ended up doing to import the text file into a linked table:
I started the import text wizard, told it a new table, then created a
specification file with the correct delimiter, field names and types, and
saved it as "Index Import Specification" I deleted the table.

I found code on the Internet for browsing to a window to select a file -
LaunchCD.

Then I created a button on a form to process the import using code and it
works with a linked file!

Dim RecsBefore, RecsAfter As Double
Dim Response
Forms!frmImportInstructions!txtPathName =
LaunchCD(Forms!frmImportInstructions)
RecsBefore = DCount("[ID]", "INDEXAll")

Response = MsgBox("Ready to import from the chosen location?",
vbYesNoCancel, "Import Index records")

If Response = vbYes Then ' User chose Yes.
DoCmd.TransferText acImportDelim, "Index Import Specification",
"INDEXAll", Forms!frmImportInstructions!txtPathName, False
RecsAfter = DCount("[ID]", "INDEXAll")
MsgBox "Successfully imported " & RecsAfter - RecsBefore & "
Records"
Else
MsgBox "Operation cancelled"
End If

----------------------------
Errors I had doing the import manually:
Error: Import Text Wizard: Invalid argument. OK
Import Text Wizard: An error occurred trying to import file
'c:\temp\import.txt'. The file was not imported.
The import errors table shows that there is a 'Null value in an auto-number
field' in the ID field.

Invalid argument. (Error 3001)
You tried to perform an operation that involves a routine in a DLL, and one
of the arguments to the routine is invalid. Check your entry to make sure
you have specified the correct arguments, and then try the operation again.
This error also occurs when you attempt to use mutually exclusive constants
in an argument to a method, such as specifying both dbConsistent and
dbInconsistent in the options argument to the OpenRecordset method.

The above code worked using the exact same text file that I was having
problems with in the errors shown here.

Thanks for everyone's help
Mich

 

[hor vannara]

Somewhere in my memory is a fleeting memory about this situation. I cannot
say for certain, but I'm thinking that you cannot import into a linked
table. However, this link shows a post thread that suggests this can be
done:
http://en.allexperts.com/q/Using-MS-Access-1440/2008/10/transferdata-import-data-2.htm

Specifically, what error do you get when you try to import to the linked
table?
--

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