Importing Text Files - Access Column Limit using Import Specs

[Larry1216]

Importing a Fixed Width Text file and want to name the fields and start-stop positions for each field.

Was using the Import Specification to do this but when I get to the 60th field-78th Character. The wizard lets me add but won't save the specs.

Field Name Data Type Start Width Indexed
AAP-N20 Text 75 1 No 0
AAP-D45 Text 76 1 No 0
AAP-N45 Text 77 1 No 0

Tried breaking it up in pieces by designating the first 73 characters as one field and then starting the next field at the next starting position but won't save past that last field AAP-N45!

Field Name Data Type Start Width Indexed
EOC Text 1 73 No 0
AAP-D20 Text 74 1 No 0
AAP-N20 Text 75 1 No 0
AAP-D45 Text 76 1 No 0
AAP-N45 Text 77 1 No 0

Any suggestions on how to beat this would be appreciated - I am not up to visual basic - just a QBE and Macro user trying to earn a living......

Have tried looking everywhere but have found no solutions.

 

[JerryKlmns]

It was suggest from a poster a month ago to create the specs in an excel file and import that to the MSysIMEXColumns table. But it is a hard thing to do since you 'll have to use the correct

Data Type Values
------------------
Yes/No = 1
Byte = 2
Integer = 3
LongInteger = 4
Currency = 5
Single = 6
Double = 7
Date = 8
Text = 10

Index Type Values
------------------
No Index = 0
Index Duplicates Ok = 1
Index No Duplicates = 2

Skip ColumnValues
------------------
Skip = -1
Import = 0

and also define the new specification elements in MSysIMEXSpecs tables.

Both tables are system, so if they are not desplayed on Tools-->Options-->View tab select System Objects

Good luck with this

 

[Larry1216]

Checked to view the system files. The MSysIMEXColumns is read only

Do you recreate the table elsewhere, add your desired fields and import it back into Access?

When you speak of the

Data Type Values
------------------
Yes/No = 1
etc

Does that refer to the MSysImexColumns table, another table or elsewhere?

Thanks/ LMc

 

[JerryKlmns]

As I said, create the specs in an excel file. Open the table MSysIMEXColumns, filter for your SpecID no, copy those records, open excel, paste values, build the rest, save and close excel. Swithc to MS-Access, press Ctrl+G (Immediate window comes up), type this

 CurrentProject.Connection.Execute "DELETE FROM MSysIMEXColumns  WHERE SpecID=" YourSpecIDHere

and press Enter. Press Alt+Q. Now the database window is visible. In Tables container, right click Import select your excel file and import to MSysIMEXColumns table.

Forgot to say ... take a Back Up first