Solved Text File Import Error

[pooldead]

I am trying to import a text file but getting the following error:

"Field 'SubSystem_Screen_Owner Department_.... ' doesn't exist in destination table 'TableA'"

The problem is all of those fields are in my TableA, but it is inserting underscores between each header name. I've attached a snippet of how the headers look in the text file itself.

 

[Isaac]

The first thing I wonder about is if the recognition of the text file's delimited columns is off. And so Access is trying to see it all as a single column. Being as it can't insert Tab characters into a column name, it's displaying the error message with underscores. I would triple-check your import specs vs. the content of the file..maybe the text file header column isn't quite matching up.

 

[theDBguy]

Hi. How exactly are you performing the import? If by code, are you using a specification?

 

[pooldead]

I'm thinking all the space in the top row of the text file is causing those problems. As a workaround, I saved it as a .xlsx and then imported with no issues. Thanks pisorsisaac!

 

[pooldead]

Hi. How exactly are you performing the import? If by code, are you using a specification?

I didn't use a specification in the code, just left it blank. Would using a specification work better? I found a workaround as noted above, but I'd like to get it to work without the extra step.

 

[Isaac]

I'm thinking all the space in the top row of the text file is causing those problems. As a workaround, I saved it as a .xlsx and then imported with no issues. Thanks pisorsisaac!

Well the whole delimited import process is based on the premise that the file has a delimited quality to it, and that that quality holds true for every single line, header included. So it depends on how you have your import specification (or not) set up. You definitely want to have one.
Glad you found a solution.

 

[theDBguy]

I didn't use a specification in the code, just left it blank. Would using a specification work better? I found a workaround as noted above, but I'd like to get it to work without the extra step.

Hi. Typically, you would use a specification when importing text files. I am not sure how it works without using one.

 

[pooldead]

I was able to confirm the text file is Tab Delimited. What specification would I use for that? I've never really had a clear understanding of the options for specs, which is why I ignore them if I'm able to.

 

[Isaac]

If you walk through the import text wizard, there is a screen where you can click something like Advanced ... there you can examine every bit of the specification and adjust accordingly, and then Save them. One tip...don't forget the name you saved it with.

 

[namliam]

The import export manager is bugged; if you run it enough times eventually it will fail ... fortunately the workaround is easy, restart the database but its painfull.

If you want a guaranteed working solution which is faster to boot, research "open"-ing a file manually and processing it from there.