• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 613
  • Last Modified:

Why do I get an empty record imported for every actual record that I import from a txt file using my Access 2003 application with an Import Specification?

I created the barest of my application in Access 2003 using an MDB type file.
I developed an Import specification to import a txt file.
I have attached a sample input txt file and my Access 2003 application.

Do you know why I get empty records with the 2 input records?
For every input record with values that I import , I get an empty record as well.
So if I were to import 100 records from a txt file, I would get 200 records in the Access table (100 records with values plus 100 empty records).

The VBA code is as follows:
Private Sub cmdImport_Click()
On Error Resume Next

DoCmd.TransferText acImportDelim, "IMNYCashBreaks Import Specification1", "OI_Intell_Mod", "U:\Dec20\IMNYCashBreaks.txt"

MsgBox ("File has been transfered successfully!")

End Sub
  • 2
1 Solution
It may because there is an extra return after each record and it's treating the return as an empty record. Try removing the extra returns as per the attached file.
zimmer9Author Commented:
The file you sent works fine. It creates only the 2 records with values.

Do you know how I could programtically remove the extra returns after each record (to accomplish what you did) using Access 2003 VBA code?

This is only a staging table but I import over 60,000 records from a txt file so I end up with over 120,000 records.

I wish I could clean up the txt file with the extra return before the import runs and creates all the wasted space in the import staging table.
zimmer9Author Commented:
My users tends to keep the Access mdb file open for days creating an LDB file and thus the application is sometimes locked for up to 1 week before I can compress the application over the weekend, once the lock is finally removed. And they import the txt file daily. The mdb file may be about 139,000 kb on Monday and by Friday it is over 1,000,000 kb. Then I can compress it over the weekend and get it back down to a 139,000 kb file once again.
Rey Obrero (Capricorn1)Commented:
see this, place the .mdb and the text file in the SAME Folder
the button  "Clean Text File" will clean the text file
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now