Writing garbage to text files

Sometime between 7:30 PM edt 9/19/13 and 4PM edt 9/20/13, fso.write stopped writing normal text and switched to something else; possibly Unicode.  After some hours, I modified the code to concatenate the string and then use fso.writeline (you don't want to know why I was using fso.write to begin with).  That made some progress in that notepad seems to be able to open the file and display the expected data but there is still something wrong with the text file since it will not import correctly in Access.

This is a 2000-4000 character record with a fixed length header and up to 40 details attached.  I was using the write so it was easy for me to view in code what I had collected so far for each record.  Please do NOT suggest a different format.  Some bozo created this one as his desired import format and I can't change it.  I simply have to produce it - which I was until it broke.  Yes there were code changes but none in this form.

Although the txt file looks correct in Notepad, there is still something wrong with it.  I created an import procedure in a throw-away database to validate this stupid record format.  I create an import spec and was importing the file using the import spec and then using a report I created to format the data into something more readable.  Here's the bizarre thing - the import wizard shows the first 30 or so records and they look fine.  I apply the import spec and the data all lines up swell.  I complete the import and then open the imported table in datasheet view.  All the columns are there but there are only random characters spread around the fields.  I tried using the GUI to import the file and I also tried using TransferText.  TransferText has a codepage option but that didn't seem to solve the problem but I may not have been using the correct codepage (1252).  I replaced the fso. write with fso.writline and that produced a better file but still not good.  I also replaced all the fso with internal Access File I/O and that fails also.  Good stuff in but garbage out.

I thought that perhaps my default code page got changed but the code exhibits the same strange behavior on two other computers.    Google search has been fruitless.  I came up with two similar problems but neither solution completely worked.  The one that partially worked was adding the fourth argument - fso.OpenTextFile(filePath, ForWriting, True, TristateTrue)  TristateTrue is supposed to be the default and perhaps that is what changed overnight for me.  Specifying TristateTrue made the file "look" OK with notepad but didn't fix Access so there is still something wrong with the way the file is being written and I'm going to guess it revolves around codepages but I don't know how to fix it.
LVL 43
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.

Rey Obrero (Capricorn1)Commented:
try decompiling your access db,

        see this link

then do Debug>compile
    correct any error raised.

then do a compact and repair

after doing the above, try your import again
Jeffrey CoachmanMIS LiasonCommented:
Why not post a sample of this text file for us to examine?
PatHartmanAuthor Commented:
The program was compiled without errors.  I decompiled, and totally recreated the app by importing all objects PRIOR to posting here.  This is an OUTPUT problem since the file I am WRITING cannot be read as a standard text file by ANY application.

@boag2000 - I posted a picture of what it looks like and what it should look like.  When examined with a hex editor, the bad file has two characters (or one depending on your perspective) that apparently tell everyone that this is not plain text.  I didn't write them.  They are not in the data.  I think they are FF FE but I am not at the office with my notes.

A new development - When I got home last night I tried using A2007 on an old computer and the app worked fine.  The output file is correct.  Using A2010 on my primary computer, the output is messed up.  I also tried it on three other computers all with A2010 but with different OS's and all failed.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
Then this would seem to be an issue in Access 2010.

So the first thing to do would be to make sure you are up to date with all your Updates for Office 2010 AND Windows.

Lets see what other experts might post...

PatHartmanAuthor Commented:
I AM up to date and that is very likely what caused the problem.  I know EXACTLY when the app broke (ok almost exactly).  In the first sentence of my original post, I said it was working when I left the office on Thursday and on Friday when I tried to create the export file again for that day, it was broken.  I don't reboot the PC every day since I sometimes dial in from home so I just leave it on.  I'm sure it hadn't been rebooted in several days but was rebooted by force on Thursday night.
I just converted the app to an .mdb and tried it with XP.  Sadly that didn't solve the problem.  But the computer has Office 2010 in addition to several old versions of Access so I'm thinking now that the problem is an office one rather than an Access one.  On the computer where I tried A2007, it has Office 2007 and no other versions of Office or Access.  I'm going to copy the code into Excel next and see if it has a problem also.
Jeffrey CoachmanMIS LiasonCommented:
oK, great, thanks for the update, ...yes these "Version" issues can be hard to track down and resolve (especially if you need multiple versions installed for development purposes).

Keep us posted
PatHartmanAuthor Commented:
Thanks.  I'm done for the day.  It is Sunday and Monday is another day:)
PatHartmanAuthor Commented:
I turned this over to MS last week and they're still working on it.  At the moment, I can append to an existing text file using the ForAppending option of FSO but the file must contain a record, so after I append my data, I have to open the file and delete the dummy record.  I'll attach the database I sent to MS.  It includes 9 dummy records that I extracted from all the SQL Server tables with a make table.  It has 5 options for writing the file.  Some options look like Chinese when you open them in notepad and others don't.  Interestingly, if you import the file into Access using the import dialog (break into three fields - 60, 2000, 60), you'll see that the ones that look like gibberish actually import correctly and the ones that look fine don't.
PatHartmanAuthor Commented:
As if by magic, the patches that got applied yesterday fixed the problem.  I can now write ASCII files again.

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
PatHartmanAuthor Commented:
Essentially MS solved the problem with some patch.  The problem was never tracked down to its source.
PatHartmanAuthor Commented:
An update:
MS didn't actually solve the problem but I narrowed down the situation sufficiently so that I could "fix" the problem and hopefully MS will take the database and instructions I sent them to really fix the problem.

It turns out that whether you are using FSO or Print to write a text file, if the following two conditions are met, the file gets corrupted in some way that many programs such as notepad cannot read it.

1. Somewhere you have a x"00" character.  Mine came from a notes field in a FoxPro table that I had linked to.
2. The record length is even.

The solution was to use the Replace() function to replace any x"00" characters with x"20" which are spaces.

If anyone wants to actually see this bug in action, post a request and I'll either email or upload the sample db.
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.