Import Table using a Custom Specification 1

[man5104]

I need to import a text file that has 110 fields. I make a custom specification with all of the fields and when I save it Access truncates everything after the 42nd field. Is there a limited number of fields that can be imported or am I doing something wrong?

Thanks,

Michelle

 

[BobStubbs]

Does your 42nd or 43rd field contain an invalid character, such as the line end characters ASCII 10 and 13? This might cause the problem.

I have just tested an import ...

-- In Excel 2000, I created a test worksheet, 3 rows x 130 columns, populated with test integer numeric data
-- Saved this as a tab delimited text file
-- In Access 2000, ran the File ... Get external data menu option
-- Imported the text file into a new table, accepting the wizard's default options

The three records imported correctly, with all 130 fields populated.

You could try this approach to create your table, and if it works, do your import as a two-stage process:

-- Import into a new Access table, letting Access set default field types and lengths
-- Post this data into your 'production' table, after checking for data errors (visually or in VBA code)

I hope that these ideas will be of some use.

Bob Stubbs (London, UK)

 

[JerryKlmns]

Hi man5104, the fields limit is not the sky but 255.
Currently importing 206 (I hate flat files )

 

[man5104]

Hi Bob,

The file I am importing doesn't contain any invalid characters. The 43rd field is the same as the 42nd. I imported the file using the wizard and it imports the file with 42 fields and there are import errors? What I don't understand is when I am building the spec Access isn't looking at what I am importing. Or is it? I have already selected the text file to import then I hit the advanced button to begin defining the specification. I should be able to define how I want to import the file and where the field breaks are?

Here is a portion of the structure that I am building the spec for. Everything after L3DELTA gets deleted when I hit save?

Col42="L3DELTA" Long Width 11
Col43="ALMINHTAG" Long Width 5
Col44="ALMINHST" Text Width 2
Col45="PRIMDISP" Text Width 8
Col46="ALMGROUP" Text Width 3
Col47="ALMACKBC" Bit Width 1
.......
Col110=....

Is there a way to use VB to look at the structure as a text file rather than building a specification?

Any more ideas?

Thanks,

Michelle