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
IMNYCashBreaks.txt
Test.mdb
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duttcomCommented:
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.
IMNYCashBreaks2.txt
0
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.
0
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.
0
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
Test-rev.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.