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.
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!!!!
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
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
