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,
FileError.JPG
FileError2.JPG
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,
FileError.JPG
FileError2.JPG
Why not post a sample of this text file for us to examine?
ASKER
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.
@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.
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...
JeffCoachman
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...
JeffCoachman
ASKER
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.
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.
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
Keep us posted
ASKER
Thanks. I'm done for the day. It is Sunday and Monday is another day:)
ASKER
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.
WriteTestPATVer5.zip
WriteTestPATVer5.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Essentially MS solved the problem with some patch. The problem was never tracked down to its source.
ASKER
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.
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.
see this link
http://www.granite.ab.ca/access/decompile.htm
then do Debug>compile
correct any error raised.
then do a compact and repair
after doing the above, try your import again