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

[AngelSpeaks]

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:

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!

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

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

 

[Gasman]

You would need to open the csv file and set the column width in Excel I would expect?

Google Search

 

[AngelSpeaks]

You would need to open the csv file and set the column width in Excel I would expect?

Google Search

This is perfect! Thanks!

 

[AngelSpeaks]

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.

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.

 

[AngelSpeaks]

You didn't answer the question - did you send it as "1112223333" or as 1112223333?

Apologies Pat. When I open the CSV file in Notepad, it shows "1112223333"

 

[AngelSpeaks]

And just for giggles, I used Notepad to edit it and removed the quotes and it still shows up the same way.

 

[AngelSpeaks]

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.

 

[Gasman]

If you have a template, why not use TransferSpreadsheet ?

 

[AngelSpeaks]

If you have a template, why not use TransferSpreadsheet ?

They want a csv.

 

[Gasman]

They want a csv.

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

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

 

[AngelSpeaks]

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

 

[AngelSpeaks]

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

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.

 

[AngelSpeaks]

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.

 

[Gasman]

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

 

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

 

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