AngelSpeaks
Active member
- Local time
- Today, 00:24
- Joined
- Oct 21, 2021
- Messages
- 548
Hi,
I need to create .csv files to be uploaded to a state portal.
This is the code to create the .csv (it loops to create a csv for each Job)
Bastanu gave me this code to rename some of the columns (the state portal used duplicate column names).
Now here's my issue. The state is rejecting my .csv file. The only time it accepts it is when we open in up in Excel and then save it. The IT guy at the state said not to use UTF-8 after it confirmed that he was having the same issue with the file.
The Specification that I use in the TransferText is to remove the double quotes (which I noticed that Excel save was doing). I did notice that when I used to Export Wizard to create the Specification, it defaulted to Western European (Windows), even though I'm in the US.
Can anyone tell me what CodePage I should be using or is there something else that I'm missing. I don't see anything in the Properties of each file that indicates anything.
I need to create .csv files to be uploaded to a state portal.
This is the code to create the .csv (it loops to create a csv for each Job)
Code:
'Note: Job is a text field in tblCPUpload
rsExportSQL = "SELECT * FROM qryCPUpload " _
& "WHERE (ProjectNumber= " & "'" & Job & "' AND ProjectState = " & "'" & gstrState & "')"
Dim rsExport As DAO.QueryDef
Set rsExport = CurrentDb.CreateQueryDef("myExportQueryDef", rsExportSQL)
filename = directoryName & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.csv"
'Rename Columns
sFieldNamesOrig = Me.FieldNamesOriginal
sFieldNamesTarget = Me.FieldNamesTarget
sFile = filename
DoCmd.TransferText acExportDelim, "CSVExport Specification", "myExportQueryDef", filename, TrueBastanu gave me this code to rename some of the columns (the state portal used duplicate column names).
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 SubNow here's my issue. The state is rejecting my .csv file. The only time it accepts it is when we open in up in Excel and then save it. The IT guy at the state said not to use UTF-8 after it confirmed that he was having the same issue with the file.
The Specification that I use in the TransferText is to remove the double quotes (which I noticed that Excel save was doing). I did notice that when I used to Export Wizard to create the Specification, it defaulted to Western European (Windows), even though I'm in the US.
Can anyone tell me what CodePage I should be using or is there something else that I'm missing. I don't see anything in the Properties of each file that indicates anything.
Last edited:
!
