AngelSpeaks
Active member
- Local time
- Today, 12:59
- Joined
- Oct 21, 2021
- Messages
- 587
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:
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!
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