Solved TransferText to CSV Issues (1 Viewer)

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
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)
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, True

Bastanu 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 Sub

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.

CVSExport Specs.png
 
Last edited:

bastanu

AWF VIP
Local time
Today, 14:38
Joined
Apr 13, 2010
Messages
1,401

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
Apologies. The comment in the code uses Chris in the File Path. I'll send the csvs.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:38
Joined
Sep 21, 2011
Messages
14,047
Wouldn't just be easier to open the csv file with Excel and just save it again?
I know it means using another program other than Access, but if Excel saves it as required?
 

bastanu

AWF VIP
Local time
Today, 14:38
Joined
Apr 13, 2010
Messages
1,401
The OP mentions a loop (one csv for every job) so depending on the numbers it might be well worth spending time to do it straight from Access.

Cheers,
 

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
Wouldn't just be easier to open the csv file with Excel and just save it again?
I know it means using another program other than Access, but if Excel saves it as required?
We create a csv for each job and they have about 50 jobs each week.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:38
Joined
Sep 21, 2011
Messages
14,047
And opening and saving a file is going to take how long?
 

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
Hi,

I thought I was the one that gave you the replace text solution (https://www.access-programmers.co.u...duplicate-column-headers.320742/#post-1799266) but maybe Chris did it somewhere else.... :(

In any case would you please upload a sample of the csv that gets accepted by the state (just one or two dummy records) and the same data as the file generated by your database using your specification.
Cheers,
Before is the rejected file, After was accepted. I did change the email address afterwards. It won't let me upload .csv files, so I have to save as txt. I hope that gives you what you need.
 

Attachments

  • State IL Job 999999 Start Date 01022022 - CP Upload - Before.txt
    2.2 KB · Views: 289
  • State IL Job 999999 Start Date 01022022 - CP Upload - After.txt
    2.1 KB · Views: 329

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
And opening and saving a file is going to take how long?
We are talking about a very busy manager who gets a lot of interruptions. Trying to keep track of everything he opened and saved would be difficult. He's doing that right now.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:38
Joined
Sep 21, 2011
Messages
14,047
No, I meant via code? :(
 

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
Gasman, thought you meant manually. Are you suggesting using something other than Access to open/save/close the files in Excel?
 

bastanu

AWF VIP
Local time
Today, 14:38
Joined
Apr 13, 2010
Messages
1,401
So you are not actually uploading a CSV file (comma separated values) but a tab-delimited text file.... You should be able to easily set up a new specification to do just that. Maybe you could upload a sample Access file with an empty table created by temporarily changing the query you use for the export (myExportQueryDef) into a make-table query; make sure you include the specification also.

Cheers,
 

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
No. The forum wouldn't let me upload .cvs so I had to resave them as text. I selected the text option, apparently it saved it as tab delimited.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:38
Joined
Oct 29, 2018
Messages
21,358
No. The forum wouldn't let me upload .cvs so I had to resave them as text. I selected the text option, apparently it saved it as tab delimited.
Hi. Export the CSVs as normal and then zip them up before posting as attachments. Just a thought...
 

bastanu

AWF VIP
Local time
Today, 14:38
Joined
Apr 13, 2010
Messages
1,401
Or just manually change the extension from csv to txt.
 

AngelSpeaks

Active member
Local time
Today, 16:38
Joined
Oct 21, 2021
Messages
406
Here's the zip.
 

Attachments

  • State IL Job 999999 Start Date 01022022 - CP Upload - Before.zip
    1.9 KB · Views: 294

bastanu

AWF VIP
Local time
Today, 14:38
Joined
Apr 13, 2010
Messages
1,401
Thanks for the files, they seem identical other than some decimals on some of the fields. I actually saved the Before file in Excel (2013) and my trusted ExamDiff app told me they are identical:https://www.prestosoft.com/edp_examdiff.asp

Are the expected file specifications public and if yes could you please share them?

I used (a long time ago when I was the e911 database manager for a big telco) to deal with a lot of text file transfers and the way I found it worked best was to actually employ local tables that were set up to match the expected specifications (some were fixed width and some were character delimited with different settings/precisions for each field) instead of using select queries. So I would empty the local (temp) table first, run an append query to add the data, which would be forced into the right format, export the table and repeat. Of course this would end up bloating the working file but running a compact and repair every once in a while wasn't that hard.

Cheers,
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:38
Joined
Sep 21, 2011
Messages
14,047
Gasman, thought you meant manually. Are you suggesting using something other than Access to open/save/close the files in Excel?
No, I was thinking of just using VBA to open the files in Excel, then saving and closing Excel?
Or output them all to a folder and then process that folder with Excel, opening and closing files again.?
All from Access though?
 

Users who are viewing this thread

Top Bottom