Solved Problems exporting text file

[Blueskies]

Hi

I have a table with a Long Integer field which is causing me some difficulties. When I try to export it to a text file I get an error:

The Microsoft Access database engine could not find the object 'text export#txt'. Make sure the object exists and you spell its name correctly. If 'text export#txt' is not a local object, check your network connection or contact the server administrator

Here are the fields used:

ID Autonumber
1 Number
2 Short Text
3 Short Text
4 Number
5 Number
6 Short Text

If I create a query based on the table and export from there, I get the same message, but if I alias the fields in the query like this:

SELECT [test export].ID, [test export].[1] AS f1, [test export].[2] AS f2, [test export].[3] AS f3, [test export].[4] AS f4, [test export].[5] AS f5, [test export].[6] AS f6
FROM [test export];

... Then it works!

However, if I rename the fields in the table to match the alias names, then try to export as a table, I still get the error.

I've tried removing fields one at a time as a test and the one that's giving me the problem is '1' - delete this and the table exports OK.

'1' is a Long Integer, so I've tried creating a new Long Integer field with the same field attributes and this also means the table exports as text file OK (as long as the original '1' is not there)

Exporting with the problem field to an Excel file also works - it's just this one field that's the issue and just when exporting to a text file.

I've tried compact and repair - no difference. I've put just test data in the table, in case there was an issue with one of the records - also no change.

So I can only think there's some form of corruption going on. I tried the export from a different PC and got the same error.

I've attached the database - could someone please try to right click the table and export as text to see if it works for you? - I'd be really interested in getting to the bottom of this if possible.

Thanks!

 

[Gasman]

I can confirm I get the same error, but export to Excel (as an example) no problem.

DId you by chance create a specification for it previously?
I created another table and had no issues with that one.
I tried a make table query from that table and had the same issue.

Edit:
Just changed your table to Field1 to 6 and that exported fine?

 

[Blueskies]

Hi

Thanks for looking at it.

The table would have been created from a text file. I've changed the field names now, but attached is the one I used, with a few records in it.

I did find it exported to Excel myself, so something to do with a text export specifically.

Odd that you managed to get it to work by changing the field name - this would tally with using an alias in a query, but when I tried changing the field name in the table before, I still got the error. I'll have another look at doing this.

Cheers

 

[Gasman]

No idea as to what is going on TBH.
I imported that file fine, and then when I tried to export I received your error message.

 

[Blueskies]

Well, I just had a look at ASCII values of the characters in the field name line in case there was some hidden character causing an issue (I've seen this before with xml files)

I found all the characters are in the usual ASCII range - all between decimal 44 and 122, which covers 'normal' punctuation plus numbers and letters.

So no help there and I'm stumped as well.

I've done some googling and found the odd reference to this issue, but no-one seems to have found the answer.

It's avoidable with field name changes, but it's bugging me now!

 

[Gasman]

Well, I just had a look at ASCII values of the characters in the field name line in case there was some hidden character causing an issue (I've seen this before with xml files)

I found all the characters are in the usual ASCII range - all between decimal 44 and 122, which covers 'normal' punctuation plus numbers and letters.

So no help there and I'm stumped as well.

I've done some googling and found the odd reference to this issue, but no-one seems to have found the answer.

It's avoidable with field name changes, but it's bugging me now!

Not for that data. I changed the names and it still complained.

 

[Blueskies]

Weird - so we know:

1. It's a specific field that's the problem
2. This field is Long Integer type, but has no unusual field attribute settings
3. Changing the name of the field or using an alias in a query can make a difference between getting an export error or not
4. It's nothing to do with the data in records
5. It's only the text export that gives the error - Excel is fine
6. Compacting the database doesn't help
7. Exporting to a different location or file name doesn't help

 

[Blueskies]

This is really odd now.

Here's the test file I've been importing into Access 2016:

PAF_URN,Town,Postcode,Latitude,Longitude,Coordinate_Level
9941,ABERDEEN,AB10 7HJ,57.12534,-2.121958,Address
9942,ABERDEEN,AB10 7HJ,57.12569,-2.121909,Address
9943,ABERDEEN,AB10 7HJ,57.1257,-2.121728,Address
9945,ABERDEEN,AB10 7HJ,57.12591,-2.121547,Address


I just emailed this text file to another PC with Access 2013 to see if it that would make a difference and it now looks like this:

B10 7HJ,57.12569,-2.121909,Address
9943,ABERDEEN,AB10 7HJ,57.1257,-2.121728,Address
9945,ABERDEEN,AB10 7HJ,57.12591,-2.121547,Address

NB I'm viewing both files in Notepad - nothing to do with Access. The header has vanished with the top record and a bit of the next one.

... so something has mangled the text somehow.

Virustotal says there are no problems with the file, so I'm even more confused now!


I don't know if this latest oddity is related to the export problem or not. Maybe I need to sleep on this and look again tomorrow - it seems to be completely illogical at the moment.

 

[Gasman]

Very weird. I just changed the extension, tried csv, same problem. Removed extension and get

 

[Blueskies]

This has to be one of those (thankfully rare) situations where the error message doesn't bear any relation to the actual problem.

Makes finding out what's actually going on all the more fun.

 

[Blueskies]

I just transferred the text file via Dropbox instead of email and this time it opened the same on the other PC.

I imported into Access 2013 and then tried exporting again as text. I still got the error message.

I tried re-creating the text file by copying and pasting the text into a new text file via Notepad and importing/exporting from Access - that worked.

So there must be something about this text file itself that's a problem. I created it by exporting from a program in vb.net, so that must be the source of this. There must be some odd characters hidden in there somewhere.

I'll keep investigating!

 

[Blueskies]

OK I've finally got to the bottom of this I think - there are some additional characters at the start of the file.

I opened it up in hexed.it which is a very useful online hex editor and found this:

My previous check didn't pick up these characters to the left of 'PAF_URN'

Access accepted them as a field name, but couldn't handle exporting this text into a text file again.

Remove these characters and it all works OK.

They must have been generated when I exported as a test stream from vb.net, so I'll look into why this happened next.

Maybe this will help anyone else with this issue: check the text file you imported from with a hex editor for any odd characters.

 

[Gasman]

Well done.

 

[Blueskies]

Well done.

Thanks! - I can sleep tonight now.

And many thanks for looking at this with me. I think I might have given up earlier without someone to communicate with!

 

[CarlettoFed]

Using the Output method of the DoCmd object, the export takes place regularly, as you can see in the attached example.