Solved TransferText to CSV Issues (1 Viewer)

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
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,
Thanks Bastanu, I was looking for a utility to do the comparison. The File Specs? A joke. The website provided the attached Excel spreadsheet and told us to upload as a .csv. They did not tell us that each .csv had to have a .pdf affidavit attached.

I did some additional testing. The CSVExport Specification first used Western European (Windows), which is what it defaulted to, for the Code Page. In NotePad, the bottom said it was an UTF-8. I changed it to use Unicode. NotePad showed it as UTF-16E and Excel seemed to open up the file as one column. I then used US-ASCII and OEM United States. NotePad showed both as UTF-8. The three Code Pages that were UTF-8 all rejected with a message 'Index was outside the bounds of the array' (I know, tells me a lot).
 

Attachments

  • Copy of Full CTP Upload.zip
    14.8 KB · Views: 148

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
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?
I tried to figure out how to do this, but my search hasn't been useful. That suggestion would would be feasible for this client. Since Excel would be invoked, would he have to refrain from having it launched? (He's a bean counter, Excel is always open on his desktop). Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:23
Joined
Sep 21, 2011
Messages
14,044
I tried to figure out how to do this, but my search hasn't been useful. That suggestion would would be feasible for this client. Since Excel would be invoked, would he have to refrain from having it launched? (He's a bean counter, Excel is always open on his desktop). Thanks
No, you would open another instance of it and then close it.
There should be plenty of examples on here. try a search else Google 'Excel automation from Access'

Here is something I had to create as a csv for import into the Xero accounting system, when I was working.

Code:
Dim XLApp As Excel.Application, wbs As Excel.Workbooks, ws As Excel.Worksheet

' Now open the Excel template
Set XLApp = New Excel.Application
Set wbs = XLApp.Workbooks


SetStatusBar ("Creating Xero Invoice Import file ")
' Now create a workbook for invoicing to Xero
wbs.Open (strPath & strXeroTemplate)
Set ws = XLApp.ActiveWorkbook.ActiveSheet
strInvAccount = "001"
strInvNum = "SA-" & Format(Now(), "yymmdd")
iRow = 2
rst.MoveFirst
Do While Not rst.EOF
    ' Set the Excel data cells
    With ws
        .Range("A" & iRow).Value = "Specialist Advisors Ltd"
        .Range("K" & iRow).Value = strInvNum
        .Range("L" & iRow).Value = "Investment Fee"
        .Range("M" & iRow).Value = Format(Date, "dd/mm/yyyy")
        .Range("N" & iRow).Value = Format(Date + 7, "dd/mm/yyyy")
        .Range("P" & iRow).Value = "Investment Fee"
        .Range("Q" & iRow).Value = rst!Client
        .Range("R" & iRow).Value = 1
        .Range("S" & iRow).Value = Format(rst!AppliedAmount, "£##0.00")
        .Range("U" & iRow).Value = strInvAccount
        .Range("V" & iRow).Value = "No VAT"
    End With
    iRow = iRow + 1
    rst.MoveNext
Loop
XLApp.ActiveWorkbook.SaveAs FileName:=strInvoicePath & "Xero SA Import File - " & strDate & ".csv", FileFormat:=xlCSV
XLApp.ActiveWorkbook.Close Savechanges:=False
MsgBox "Xero Invoice import file created in " & strInvoicePath
SetStatusBar (" ")

I believe you could just get by, just by opening the file and saving? Worth a try at least, if you cannot get it working the way you are doing now?
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
No, you would open another instance of it and then close it.
There should be plenty of examples on here. try a search else Google 'Excel automation from Access'

Here is something I had to create as a csv for import into the Xero accounting system, when I was working.

Code:
Dim XLApp As Excel.Application, wbs As Excel.Workbooks, ws As Excel.Worksheet

' Now open the Excel template
Set XLApp = New Excel.Application
Set wbs = XLApp.Workbooks


SetStatusBar ("Creating Xero Invoice Import file ")
' Now create a workbook for invoicing to Xero
wbs.Open (strPath & strXeroTemplate)
Set ws = XLApp.ActiveWorkbook.ActiveSheet
strInvAccount = "001"
strInvNum = "SA-" & Format(Now(), "yymmdd")
iRow = 2
rst.MoveFirst
Do While Not rst.EOF
    ' Set the Excel data cells
    With ws
        .Range("A" & iRow).Value = "Specialist Advisors Ltd"
        .Range("K" & iRow).Value = strInvNum
        .Range("L" & iRow).Value = "Investment Fee"
        .Range("M" & iRow).Value = Format(Date, "dd/mm/yyyy")
        .Range("N" & iRow).Value = Format(Date + 7, "dd/mm/yyyy")
        .Range("P" & iRow).Value = "Investment Fee"
        .Range("Q" & iRow).Value = rst!Client
        .Range("R" & iRow).Value = 1
        .Range("S" & iRow).Value = Format(rst!AppliedAmount, "£##0.00")
        .Range("U" & iRow).Value = strInvAccount
        .Range("V" & iRow).Value = "No VAT"
    End With
    iRow = iRow + 1
    rst.MoveNext
Loop
XLApp.ActiveWorkbook.SaveAs FileName:=strInvoicePath & "Xero SA Import File - " & strDate & ".csv", FileFormat:=xlCSV
XLApp.ActiveWorkbook.Close Savechanges:=False
MsgBox "Xero Invoice import file created in " & strInvoicePath
SetStatusBar (" ")

I believe you could just get by, just by opening the file and saving? Worth a try at least, if you cannot get it working the way you are doing now?
Thanks. I definitely used the wrong search criteria
 

bastanu

AWF VIP
Local time
Today, 09:23
Joined
Apr 13, 2010
Messages
1,401
Hi Cathy,

I think you should be able to modify the export specification to match the format after saving in Excel. Looks like the Excel dates don't have the leading zeros and most of the numbers towards the end seem to be integers with no decimals versus the "before" version that has two decimals. You can force the data in those formats by using data type conversion functions in your source query (Format([YourDate],"d/m/yyyy"), Cint([YourDecimalField]) or better you can convert the query into an append query and push the data to be exported into a temporary table that is setup to match the required output.

Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2002
Messages
42,970
If you are exporting more than a couple of files at once, opening/closing Excel for each would make the process slow.

It might be easier to not use Access methods to export the file at all. It must be "states" that come up with these stupid requirements because I've had to deal with them also.

I ended up having to use FSO to write the output .csv file to get it to conform to the specs the state wanted. Using your own code, will allow you to substitute your own header line with the duplicate column names (really, who thinks that is a good idea???????????) And then concatenate the columns with what ever delimiter you need and even include the option to enclose strings in quotes if you need that. The code I pasted here puts quotes around EVERY field which was the stupid requirement of my state people. So, you will need to change that. The procedure takes an open recordset, the filename (including path) and a headers flag as inputs. To use FSO (File System Object), you need to set a reference to Microsoft Scripting Runtime. The code uses the field collection to gather the headers. You probably need to hard code them. I was exporting a bunch of different files so this routine is fairly generalized. Since the recordset is opened by the calling procedure, you can move that part of the code to the calling procedure and build the string there and then pass it as a fourth argument.

Code:
    Call ExportToCSV(rs, sFileName, False)

Public Function ExportToCSV(rs As DAO.Recordset, strFile As String, Headers As Boolean)

''' needs a reference to Microsoft Scripting Runtime

    Dim fldLoop As DAO.Fields
    Dim fld As DAO.Field
    Dim strRec As String
    Dim FSO As FileSystemObject
    Dim fsoFile As TextStream
    Dim ExportCount As Long
    Dim RecLen As Integer
    Dim HoldEMS As String
    Dim holdAssessDate As String
   
   
   On Error GoTo Err_Proc
   
'''fso stuff
    Set FSO = New FileSystemObject
    Set fsoFile = FSO.CreateTextFile(strFile, True)
    strRec = ""
   
'''initialize fields collection
    Set fldLoop = rs.Fields
   
    'write headers if headers flag = true
    If Headers = True Then
        For Each fld In fldLoop
            strRec = strRec & """" & fld.name & ""","
        Next fld
        RecLen = Len(strRec)
        strRec = Left(strRec, RecLen - 1)       'chop off last comma
        fsoFile.WriteLine strRec
    End If
   
    'write data records
    Do While Not rs.EOF
        ExportCount = ExportCount + 1
        strRec = ""
        For Each fld In fldLoop
            strRec = strRec & """" & fld.Value & ""","
            If fld.name = "ems" Then
                HoldEMS = fld.Value
            End If
            If fld.name = "AssessDate" Then
                holdAssessDate = fld.Value
            End If
        Next fld
        RecLen = Len(strRec)
        strRec = Left(strRec, RecLen - 1)       'chop off last comma
        fsoFile.WriteLine strRec
        rs.MoveNext
    Loop
'''fso stuff
    fsoFile.Close
    Debug.Print ExportCount
Exit_Proc:
   On Error GoTo 0
   Exit Function

Err_Proc:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportToCSV of Module mExportToText"
            MsgBox "EMS = " & HoldEMS & " AND AssessDate = " & holdAssessDate, vbOKOnly
    End Select
    Resume Exit_Proc
    Resume
End Function
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
Thanks Pat. There could be fifty or more files so I am hoping for a solution that doesn't loop thru them and save them in Excel.

I wish i knew why they used duplicate column names, especially since in my mainframe days, duplicate field names weren't allowed. Thanks for the code.

Since Bastanu's suggestion is quick, I will try that first. I'm learning a lot from you guys. I thought Access would be quicker than VB.Net but I am seeing how powerful this almost 30 year old database is.
 
Last edited:

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
Hi Cathy,

I think you should be able to modify the export specification to match the format after saving in Excel. Looks like the Excel dates don't have the leading zeros and most of the numbers towards the end seem to be integers with no decimals versus the "before" version that has two decimals. You can force the data in those formats by using data type conversion functions in your source query (Format([YourDate],"d/m/yyyy"), Cint([YourDecimalField]) or better you can convert the query into an append query and push the data to be exported into a temporary table that is setup to match the required output.

Cheers,
Thanks for the suggestion. I will try this first. Since I have access to the state portal for the upload, I don't have to worry about waiting for the client (aka my hubby)
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
Thanks for the suggestion. I will try this first. Since I have access to the state portal for the upload, I don't have to worry about waiting for the client (aka my hubby)
Update.

Ok, I changed the format for dates as suggested, for the other fields, they displayed as text, so I changed to table to make them Numeric, Double, and Auto for decimals. The table was fine. The query against the table was fine. When I was going thru The Export Wizard, the numeric fields were fine, but the .txt file created was the same as the .csv file. The numbers were formatted as 0.00, 1.50, etc instead of 0 and 1.5.

Table results
1643997720587.png

Query results

1643997623795.png


.csv results (same as Export Wizard)
1643997785420.png


I will look into Pat's suggestion. Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2002
Messages
42,970
Change the default on the table to auto for decimals.
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
Update.

Ok, I changed the format for dates as suggested, for the other fields, they displayed as text, so I changed to table to make them Numeric, Double, and Auto for decimals. The table was fine. The query against the table was fine. When I was going thru The Export Wizard, the numeric fields were fine, but the .txt file created was the same as the .csv file. The numbers were formatted as 0.00, 1.50, etc instead of 0 and 1.5.

Table results
View attachment 98049
Query results

View attachment 98047

.csv results (same as Export Wizard)
View attachment 98050

I will look into Pat's suggestion. Thanks
I did that. I tried your suggestion and the damn state still rejected it until I resaved it in Excel (the auto for decimals even had some columns with three decimal positions and it accepted that). Ugh!!!!

Oh, your code is pretty cool.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2002
Messages
42,970
Whether the decimals have trailing zeros or not shouldn't affect anything. Either your husband is jerking your chain or the state really doesn't like you:)

You seem to have to export to excel and then save the .csv. Beats the hell out of me But you can automate the task.
 
Last edited:

bastanu

AWF VIP
Local time
Today, 09:23
Joined
Apr 13, 2010
Messages
1,401
Hi Cathy,
Would you be able to upload an sample Access file with the empty tblCPUUpload and the export spec (when you import you need to click the checkbox to import the specs and relationships. If you have a lookup table that does the replacing of the duplicating field names please include that as well, I am sure we should be able to make this work.
Cheers,
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
Whether the decimals have trailing zeros or not shouldn't affect anything. Either your husband is jerking your chain or the state really doesn't like you:)

You seem to have to export to excel and then save the .csv. Beats the hell out of me But you can automate the task.
I have access to the upload process. It seem to me that behind the scenes Excel is saving the file is a slightly different format. I agree that number of decimal positions should not matter.
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
VpHi Cathy,
Would you be able to upload an sample Access file with the empty tblCPUUpload and the export spec (when you import you need to click the checkbox to import the specs and relationships. If you have a lookup table that does the replacing of the duplicating field names please include that as well, I am sure we should be able to make this work.
Cheers,
Let me see what I can do. TblCPUpload is created by a make table query and then updated with an apend query. It has no relationships with other tables. Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2002
Messages
42,970
If you have a text editor that can show the ASCII values, export a small file from Access and then run it through Excel to see if there are any differences when you look at the two side by side.

You might be able to download a free editor or sign up for a short trial.
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
I used some editors, I didn't find any differences. I do try to Automation and I have the following code that opens and saves the .csv files. The state portal has accepted them.

Code:
Dim oApp As Excel.Application      'Excel Application
    'dim oApp as Object    'you can remove the reference to Microsoft Excel xx.0 Object library)
    Set oApp = CreateObject("Excel.Application")     'opens new instance of Excel
    oApp.Visible = False            'for testing, you can see what Excel does, set to false when done
    'Dim oWkbk As Object      'defines a workbook
    Dim oWkbk As Excel.Workbook
    oApp.DisplayAlerts = False            'turn off alerts

and

Code:
 'Use Excel to save file in format IDOL Portal wants
        With DoCmd
            .SetWarnings False
             Set oWkbk = oApp.WorkBooks.Open(sFile)
             oWkbk.SaveAs sFile
             oWkbk.Close
             .SetWarnings True
        End With

and

Code:
  'Close Excel
    Set oWkbk = Nothing
    oApp.DisplayAlerts = True
    Set oApp = Nothing

Thanks for all your help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2002
Messages
42,970
At least you got the automation done so whatever the issue is with Access and the state, it is now masked. I would add a summary o the issue as comments in the procedure that automates Excel to save the .csv file because this is going to look pretty convoluted to anyone else who reads the code, possibly even you a few years from now:)
 

AngelSpeaks

Active member
Local time
Today, 11:23
Joined
Oct 21, 2021
Messages
406
At least you got the automation done so whatever the issue is with Access and the state, it is now masked. I would add a summary o the issue as comments in the procedure that automates Excel to save the .csv file because this is going to look pretty convoluted to anyone else who reads the code, possibly even you a few years from now:)
Will do!
 

Users who are viewing this thread

Top Bottom