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

[Pat Hartman]

Make yourself a .csv

ID, Phone, Zip
1, 2035551212, 06614

Now doubleclick on it. Excel opens. Look how it displays the Phone number. Look how it drops the leading zero in zip

Make a second file

ID, Phone, Zip
1, '2035551212, '06614
Useless for Access or any other application except Excel

Make a third file

ID, Phone, Zip
1, "2035551212", "06614"

The Third file should work but it doesn't.

 

[AngelSpeaks]

I just heard back from the MVP group. The suggestion was to prepend a single quote to any field you want excel to treat as text. So, you would make a query:
Select "'" & Phone as PhoneText, .... From YourTable.
Then export the query. This will of course mess up the column names but you are already adjusting them so what's one more.

The problem with this solution is that it makes the .csv file USLESS for anything except Excel. So, it is a bad solution but it might work for you.

Thanks!

 

[AngelSpeaks]

I must admit, I am confused re the column width issue?
If you just left it as a CSV, that should be it?

It may not be the column width. When the file is saved, the double quotes around each column disappears in the .csv. This is noticeable when using Notepad to look at it.

 

[Gasman]

It may not be the column width. When the file is saved, the double quotes around each column disappears in the .csv. This is noticeable when using Notepad to look at it.

Yes, but if you do not open it in excel that will not happen?

 

[Pat Hartman]

If you export the table/query from Access, and you didn't modify the format defaults for a .csv, the default format is comma separated with double quotes around text fields and around short date fields. If you don't format the date to short date or something else, Access exports as 1/1/2022 00:00:00 which gets interpreted as a date by most applications.

 

[Pat Hartman]

Yes, but if you do not open it in excel that will not happen?

It is Excel that is the problem. Did you make a .csv file and look at what Excel does to it?

 

[AngelSpeaks]

Update. I have confirmed that all of the .csv files that were accepted had been saved in Excel and the double quotes were removed from these files. I sent an email to the IT guy at the state to get confirmation.

 

[AngelSpeaks]

It is Excel that is the problem. Did you make a .csv file and look at what Excel does to it?

Excel removes the double quotes.

 

[AngelSpeaks]

Wish me luck. I created an export specification to not use the double quotes. Let's see if the state portal takes it. Apparently, when they supplied the Excel template, they expected it to be used and then saved as a CSV for upload.

 

[Pat Hartman]

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.

 

[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).

 

[Pat Hartman]

I thought of volunteering You can't blame the problem on COBOL though. You have to blame the problem on the database architecture. If the database design is poor, you can't make even simple expansions. That is the same today as it was more than 50 years ago when I was writing COBOL. I was really proud of myself as 2000 approached and everyone was going crazy about the Y2K problem. I refused to take any Y2K projects because I had never created in all my years of designing databases a Y2K problem. Turns out to be a little bit of luck. Back in 1972 I was developing an application for a mortgage banking client. Mortgages have a 30 year horizon so when you are testing, it is important to test at the limits of your data ranges and so as a consequence, I discovered that storing the date as yymmdd was going to run into a problem in 2000 when
1. 00 was less than 99 so sorts and compares wouldn't work
2. 2000 is actually a leap year if you know the 400 year rule so February had 29 days that year.
So, for every application I developed, my dates were stored as yyyymmdd which solved the Y2K problem 30 years before anyone else knew it existed

 

[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?

 

[Pat Hartman]

MS used a similar rule for defaulting a century