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

A

AngelSpeaks

Active member
Local time
Today, 00:32
Joined
Oct 21, 2021
Messages
548
My project creates CSV, column delimited files (probably close to 40 each week and they individually have to be uploaded to our State's portal). There are several fields that have telephone number. My tables have them setup as short text. When I open the CSV file, since the column isn't wide enough to display the phone number, so its displayed like 5.E+01. Of course when you click the column, it expands and the phone number is displayed as 9999999999 format, right justified. My issue comes when we have to upload the CSV file to our State's portal. It won't accept it unless we manually go thru each CSV file and expand the column width.

The code to create the CSV:
Code: 
filename = directoryName & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.csv"                
        DoCmd.TransferText acExportDelim, , "myExportQueryDef", filename, True

Now after I create the CSV, I have to rename some of the columns (goofy state has duplicate column names!) using this code (that was so graciously provided by one of you). I'm displaying it incase there's a way to use code to expand the column.

Thanks!

Code: 
  'replace original with target
        Call TextFile_FindReplace(sFile, sFieldNamesOrig, sFieldNamesTarget)
        CurrentDb.QueryDefs.Delete rsExport.Name

Code: 
Sub TextFile_FindReplace(sFileName As String, sFindWhat As String, sReplaceWith As String)
'PURPOSE: Modify Contents of a text file using Find/Replace
'SOURCE: www.TheSpreadsheetGuru.com

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String

'File Path of Text File
  'FilePath = "C:\Users\chris\Desktop\MyFile.txt"
FilePath = sFileName
'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file in a Read State
  Open FilePath For Input As TextFile

'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Clost Text File
  Close TextFile
 
'Find/Replace
  FileContent = Replace(FileContent, sFindWhat, sReplaceWith)

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file in a Write State
  Open FilePath For Output As TextFile
 
'Write New Text data to file
  Print #TextFile, FileContent

'Close Text File
  Close TextFile

End Sub
 
The problem arises because Windows defaults .csv to Excel rather than Notepad. Technically, if you want to open a .csv with Excel, you should open Excel and then use the data dialog to open the .csv file. That solves all problems since you can make an import spec.

Additionally, when you export the data, are you exporting the phone number as a string enclosed by quotes? If not, you need to since that gives Excel some guidance as to the true datatype.

And finally, if they are OK with including the formatting characters in the string that is best since (111) 222-3333 will be treated as a string.
 
Pat Hartman said:
The problem arises because Windows defaults .csv to Excel rather than Notepad. Technically, if you want to open a .csv with Excel, you should open Excel and then use the data dialog to open the .csv file. That solves all problems since you can make an import spec.

Additionally, when you export the data, are you exporting the phone number as a string enclosed by quotes? If not, you need to since that gives Excel some guidance as to the true datatype.

And finally, if they are OK with including the formatting characters in the string that is best since (111) 222-3333 will be treated as a string.
Click to expand...
We tried 111-222-3333 but they rejected it. We also tried (111) 222-3333 and that got rejected. They only accept 1112223333 and we found that the column has to be expanded.
 
You didn't answer the question - did you send it as "1112223333" or as 1112223333?
 
And just for giggles, I used Notepad to edit it and removed the quotes and it still shows up the same way.
 
Then the only way to get around the problem is to instruct them to open the .csv with notepad or using the correct method I described in Excel.

Can you send them an .xlsx file instead? In your code that edits the column names so you can "duplicate" the column names, you can also expand the columns. AND, change the data type of the phone number from General to Text.

I would classify this as an Excel bug and report it to Microsoft. If you enclosed the PN in quotes specifically identifying it as a TEXT column, Excel should respect your definition not ignore it.

Maybe it is a "state" thing. When I was having to create files to interface with my "state", their formats were pretty stupid also.
 
Thanks Pat for your response. I really appreciate all of you on this forum! Everything I found so far, says the file has to be saved as .xlsx because .csv will loose the formatting. I did try Chris's suggestion, but there's probably more that I have to do.

I'm going to contact the IT guy at the state. Yes, their format is stupid. They send their template as an .xlsx but they want you to upload a .csv!!!! In fact, their template shows the phone number the same way I created it!!

Thanks again.
 
If you have a template, why not use TransferSpreadsheet ?
 
AngelSpeaks said:
They want a csv.
Click to expand...
Yes, but you could transfer to a copy of theri template, do what they insist you do, and then save as a csv?
Nothing difficult in that?

Here is something I use to get some stock codes and save to csv to upload into quicken.
I removed all code not relevant, so you can see the wood amongst the trees :)


Code: 
Dim strTicker As String, strPrice As String, strOutFile As String, strMainFile As String

strOutFile = Range("K1").Value
...
SaveFile:
Application.DisplayAlerts = False

Application.StatusBar = "Saving csv file as " & strOutFile
' Save the CSV version
ActiveWorkbook.SaveAs Filename:=strOutFile, FileFormat:=xlCSV, _
    CreateBackup:=False
 
Actually, I think I've discovered something else. After changing the phone number (from before when it had hyphens in it) or expanding the columns and saving the .csv, the upload was accepted. When I used Notepad to look at the .csv that was saved using Excel, the double quotes surrounding all fields have been removed.

I will keep you posted. Thanks
 
Last edited:
You would never see the double quotes if you open the .csv file in excel. You need to open it in notepad to see the quotes and comas. Can you explain what you did to get the file accepted? Do you even know what you did:)
 
Pat Hartman said:
You would never see the double quotes if you open the .csv file in excel. You need to open it in notepad to see the quotes and comas. Can you explain what you did to get the file accepted? Do you even know what you did:)
Click to expand...
I didn't handle the portal upload. My associate (aka my husband) did. We compared my file with the sample and if a difference was noted, it was corrected in Excel and then uploaded. He did the same with each file. When the phone number appeared with special characters and alpha, he resaved them after he resized the columns. We're going to check his files at work with Notepad. I'm betting that saving the files, removed the double quotes and that was the cause. The only specs that the state provides is the sample Excel file but in .csv format. With the financial state of this state I wouldn't be surprised if they were still using the same mainframes that I worked on almost 20 years ago. I remember any .csv files we imported didn't have double quotes unless the field had a comma in it.

P.S. I edited my post. I forgot to note that I used Notepad to check it after it was saved in Excel.
 
The problem is that the spec for .csv files is very flexible. Access does it right in my opinion but clearly the Access team doesn't speak with the Excel team or this problem would be happening. I did some testing and sent two error reports from Excel and posted in my MVP forum to see if I can actually get the attention of someone at MS to explain how to fix this issue. When Access exports a text data type, it encloses the string in double quotes by default. That should be a trigger for Excel to assume the field is text not numeric.

The "correct" way to open a .csv file in Excel is to first open Excel, then using the Data tab, import the data from the .csv file. In O2010, which is the only old version I have handy, Excel pops up a wizard similar to the Access wizard (but no where near as good) and it allows you to specify data types for each column which solves the problem but sadly, it also doesn't understand the double quotes are a delimiter even though the wizard recognizes them and so stupidly, Excel imports the quotes as part of the text.

I don't understand why people haven't complained more about this since it also affects zip codes. Of course unless you live in New England or care about any address in New England, you might never have noticed that Excel strips the leading zeros off zip codes. Personally, this affects me so I complain. My zip is 06614 and Excel keeps dropping the 0. I feel persecuted:(
 
Maybe they do complain but MS ignores them. For years they neglected Access, which, IMO, is a very powerful tool. I did find a couple of discussions in this forum that explains how I can strip the double quotes.

Thanks to your very detailed description, I know why Excel is treating my text fields as numeric.
 
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.
 
I must admit, I am confused re the column width issue?
If you just left it as a CSV, that should be it?
 
You must log in or register to reply here.

Similar threads

I
write contents of txt file in textbox with scrollbars
Replies
3
Views
195
theDBguy
theDBguy
N
Code does not support certain characters such as full-width symbols or Chinese
Replies
1
Views
524
ebs17
E
A
Solved TransferText to CSV Issues
2
Replies
39
Views
3,330
AngelSpeaks
A
P
Reading a long text file to insert rows in a table
Replies
8
Views
335
pacctono
P
H
Solved Export Error Report as Text File.
Replies
14
Views
1,061
HillTJ
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom