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.
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).
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
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?
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?
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.
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
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.
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.
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)
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)
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.
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.
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!!!!
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.
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,
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.
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
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.
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
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
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
