TransferText macro does not import data

[donbolinger]

I've got a macro which opens an exsisting table, deletes all the records then imports certain columns from a .txt file (using import specifications). This is done daily so the latest information is present in the table (the text file is updated each day).

After running the macro, the records are deleted but no new data is imported (no error messages occur). At first I thought the .txt file was too large but it only has 9857 records. I have tried it on a smaller version of the file with only a couple hundred of records and it works fine. The macro takes a little longer (so you can tell it's pulling in the data).

I had to set up the import specifications on a smaller version of the .txt file because Access hangs when I try to import the full file using the Import Text Wizard.

Any ideas?

 

[PaulF]

ACCESS needs to have Carriage Returns & Line Feeds in order to import the text file... My experience in the past, is when ACCESS hangs while trying to import a text file it is because the file is either one long record, or only includes Line Feeds (Unix files are this way). Is it possible that you copied and pasted the data from the large file to the smaller file using a text editor and this created the CrLf for you, and ACCESS had no problem importing that data?

PaulF

 

[donbolinger]

Actually, no. This is a comma delimited txt file. Here is a sample:

"Product","Product Line","Description 1","Description 2","Price","Qty Available","Weight","UPC","Qty Backordered","Qty On Order"

"00-69032-000","MAG","MAGAELLAN CANVAS CASE","FOR GPS",6.49,25,0.16,"763357100850",0,0

"00102SCB","SCP","SCP UNIVERSAL ADJUSTABLE","CELL PHONE CRADLE",7.99,7,0.48,"738516001029",0,0

"00108SCB","SCP","SCP UNIVRSL SWIVEL CLIP","",3.19,124,0.08,"738516001081",2,0

I put a blank line between each record to make is more readable for you.

Any other ideas?
Thanks.

 

[PaulF]

not one.. I've just sucessfully imported this data (after saving to a file made by WordPad), by creating an import spec and saving it, then running it via a macro.

But, just as a test, open the large file in WordPad, save it to a different name, and try to import that.

PaulF

 

[donbolinger]

Yep - I saved it under a different name and was able to manually import the new file in its entirety! I went back and tried the original file but Access "stops responding" and will eventually give me an "overflow" error.

The file name is dblinv.txt and the table it is being imported into is Dblinv

Any suggestions? If I change the Access table name to something different, will it update in all of my queries and relationships?

Thanks!

 

[PaulF]

it has to be the CrLf versus Lf .... really, I've seen it quite a lot in the past... by saving it to a new file in WordPad, it automatically converts the Lf to CrLf, which is why you were able to import it without any problems.
I've also seen CrCrLf in several files I import, so I've gotten to the point where I write code to open the file, capture each character, if its ASCII value is less than 32 ignore the value (unless its 10 for LF) otherwise store it to a variable, and keep adding to this variable til I hit a Line Feed, then I append the record, and continue on one character at a time til the End of File. Takes a litle longer to do it this way, but works every time, regardless of what format I receive.

PaulF

 

[donbolinger]

Thanks for all your help!

Don.