CSV File Created, Phone Number Displayed with Special Char and Letters

[AngelSpeaks]

I'm pretty sure I mentioned that Excel does not use the same .csv spec that Access uses. If it did, we wouldn't be having this conversation.

If you are OK with opening the .csv you created with Access in Excel and fixing it up by saving as a .csv that's fine. Otherwise, you can do an export ONCE manually so that you can create an export spec. Access has the option to not surround text fields with quotes so you can make the same file that Excel makes without having to open Excel. Once you have made the export spec, you then reference it in your TransferText method. Keep in mind that this won't fix the Excel problem. If you open this .csv in Excel, Excel is still going to convert the phone number to scientific notation and strip the leading zeros from zip codes.

To make the export spec, start the export dialog and at some point press the Advanced button. That will allow you to remove the quotes for text setting and also allow you to save the export spec and name it.

Alternatively, if you want the code to create a custom .csv format, I can post some. My state had the brilliant idea that ALL fields in a .csv needed to be enclosed in double quotes. That isn't an option that Access supports with the wizard so I wrote it myself.

Yes, you did. And I think that's why everything is so screwy. It's my belief that the state wanted us to manually create these in Excel and then save as .csv. I created the Export Spec in Access and ran my code and my .csv files do not have the double quotes. I had to do the darn export twice because first I did it against the table and not the query and I got a message about file not found (but it didn't reference the Export Spec not being found) that created the .csv, but I did get it to work. I'm amazed at how backwards the IT departments are for many of our states. Remember when Pres Trump had an additional workmans comp given to everyone? Many states were looking for COBOL programmers because their systems were too old to handle the request (heck I wouldn't be surprised if they needed someone with RPGII experience and yes I'm dating myself here).

 

[AngelSpeaks]

I remember dates stored with a numeric field, five positions (Pic 9(5)) with one position for year. Then they tried to change the field to a packed field (Comp3). One of my Y2K installs was an Accounts Payable package. They used an 80/20 rule. If the year was 20 or less the century was 19. I wonder if they ever upgraded?