The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625) (1 Viewer)

StuartBowen

New member
Local time
Today, 03:39
Joined
Jul 12, 2017
Messages
11
OK. Banging my had for hours now.

I am doing something exceptionally simple yet it fails.

I am importing some data in various CSV files in a loop into Access, and all CSV files have the same format.

I need to check a file exists for a client (filename is in format domainname.co.uk-Users.csv
Because Access doesn't like importing a file with extra .'s I rename if it exists to M365Temp.csv
I then import to M365Temp, run a query to append to a main table, delete the M365Temp.csv file and loop though to the next client.

DoCmd.TransferText acImportDelim, "M365Temp", "M365Temp", "C:\CIPPExports\" & "M365Temp.csv", True

Some clients run through fine yet others I hit the 'The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)' error. However, it does because it worked 2 minutes ago importing the same file with the same name using the same specification. I also see it in MSysIMEXSpecs.

Also, thinking it may be the file structure content, if I just take the line that imports, pop that on a button and run, it is fine.

Any ideas how to troubleshoot this one?

BTW the CSV files are on sharepoint, but I have a 30 sec pause now just in case it is synching and locking. It is definitely synched and fine before it tries to import.
 
Quick update, I moved the location of the files to c:\CIPP\ so outside SharePoint. I run now and one of the files it struggled on, it runs the problem code, imports the file into M365Temp (definitely there) goes on to a delete query, asks if I would like to delete, I click yes and it then comes up with the 'The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)' error!!! I click on debug and it is not on the import line but on the delete query line but it comes up with the same error. I have run a repair and compact, still the same.
 
"Also, thinking it may be the file structure content, if I just take the line that imports, pop that on a button and run, it is fine."

Teaching grandmothers to suck eggs, I assume you have checked the detailed content of the failed records?

And how are you deleting the Temp file?
 
this is the code in question




'On Error Resume Next

Dim Rst As Recordset
Dim Rst2 As Recordset
Dim SngSec As Long

'open the table that the report reads to make sure there is a record in it.

Set Rst = CurrentDb.OpenRecordset("CompanyClientReport")

Rst.MoveFirst

Do Until Rst.EOF

[Forms]![Switchboard]![Recipient] = Rst!Recipient
[Forms]![Switchboard]![CompanyName] = Rst![Company Name]

Set Rst2 = CurrentDb.OpenRecordset("SELECT DISTINCTROW M365Users.Tenant FROM CompanyClientReport LEFT JOIN (CompanyMicrosoft LEFT JOIN (M365Users RIGHT JOIN M365Tenants ON M365Users.Tenant = M365Tenants.defaultDomainName) ON CompanyMicrosoft.Microsoft = M365Tenants.customerId) ON CompanyClientReport.[Company ID] = CompanyMicrosoft.[Company ID] WHERE (((CompanyMicrosoft.[Company Name]) = " & Chr(34) & [Forms]![Switchboard]![CompanyName] & Chr(34) & ")) GROUP BY M365Users.Tenant;")
Rst2.MoveFirst

strPath = "C:\M365\"
MsgBox strPath & Rst2!Tenant & "-Users.csv"
If FileExists(strPath & Rst2!Tenant & "-Users.csv") = True Then

Name strPath & Rst2!Tenant & "-Users.csv" As strPath & "M365Temp.csv"
SngSec = Timer + 30
Do While Timer < SngSec
Loop
DoCmd.TransferText acImportDelim, "M365Temp", "M365Temp", strPath & "M365Temp.csv", True
DoCmd.OpenQuery "M365 User Refresh 2 Delete"
DoCmd.OpenQuery "M365 User Refresh 3 Append"
Kill strPath & "M365Temp.csv"
DoCmd.DeleteObject acTable, "M365Temp"

End If

Rst.MoveNext


As mentioned, I have suspected the content of the particular csv but it has just imported fine, content of M365Temp table looks good, no import errors and yet it fails with the same error on the following delete query.

Also, if I do a manual import using the same specification from sharepoint, it is fine.
 
Just a thought in passing before I look further - how many records are in the csv files? Are the ones that are failing bigger than the others? That would explain why a single import works and multi file exports don't as the processing needs a finite minimum time to reset.
 
thought I would reboot PC but still same.

So just run again, first file went fine with only approx 8 records. Second one did the thing where it actually imports the 82 records, but errors with the import specification error on the delete query line.
 
just did a switcheroo. I swapped the names on the files so the 82 record import went first. It was fine. Then went to import the 8 record file. it errors. This file was fine two minutes ago.
 
I altered so that it runs and as soon as it is successful in importing one file, it exits. Intention was to see if it was the fact it was importing a second file. It imports the first then exits. I click the button to import again, it doesnt import the first company file because the file doesnt exist any more (deleted in first run) and it then fails on the second with same error.
 
decided to pick my battles since this made zero sense.

I now use [REDACTED] to merge my several dozen identical format csv files into one and just have some code to import, run a delete query, then append query and finally delete the temp table and csv file.

EDITED BY The_Doc_Man to remove questionable reference.
 
Last edited by a moderator:
btw this large csv which contains all data from all files, including those that failed with the 'specification doesnt exist' error, is being imported using the same transfertext line of code using the same specification
 
Please be aware that your reference to a particular web site could be misconstrued as improper advertising, which for this site falls into the category of SPAM. Acting as a moderator, I'm going to give you the benefit of the doubt and simply redact the reference without any other action.
 
apologies, undertsood. dont have any links to the site, it just got me out of a hole
 
I figured that, which is why the only thing I chose to do was a redaction, nothing else. Now that you know we are a touch sensitive about specific site references in posts, you know to be more careful. And the truth is I could have left it in, but the post was reported by another member and as a moderator I have to take reports seriously. Call that the equivalent of a couple of lashes with a wet noodle and don't worry about it.
 

Users who are viewing this thread

Back
Top Bottom