Saved Imoorts problem with file name
Saved Imoorts problem with file name
(OP)
Hi,
My work flow is to download a table as a csv from a third party and then save it as an xlsx file. I give it a new filename when I do a saveas xlsx, because the default filename from the third party has special characters in it that are not compatible with access import.
The import works perfectly and I save the steps as a saved import. I can run this saved import over and over again, with no problems at all. That is, until I download a new csv file from the third party and overwrite the previous xlsx file. For some reason, the saved import, now sees the default file name and disregards the saveas name. Consequently, it lists the original filename from the third party as an invalid file name and halts the import.
The main reason I want to use a saved import is because I have a field that I need to change to short text instead of a number each time I do the import. Saved import would automate this process.
I believe that somehow, the excel file logs the original filename somewhere as a constant and it is where Access looks up the filename in its saved import procedure.
Some things I will try.
Instead of overwriting the first xlsx file using excel saveas, I will delete the original file before doing the saveas, so I will not be overwriting an existing file.
I will also try renaming the file in the directory tree, rather than renaming it with a saveas.
Any other ideas as to what is going on or a work around would be helpful.
Thanks.
My work flow is to download a table as a csv from a third party and then save it as an xlsx file. I give it a new filename when I do a saveas xlsx, because the default filename from the third party has special characters in it that are not compatible with access import.
The import works perfectly and I save the steps as a saved import. I can run this saved import over and over again, with no problems at all. That is, until I download a new csv file from the third party and overwrite the previous xlsx file. For some reason, the saved import, now sees the default file name and disregards the saveas name. Consequently, it lists the original filename from the third party as an invalid file name and halts the import.
The main reason I want to use a saved import is because I have a field that I need to change to short text instead of a number each time I do the import. Saved import would automate this process.
I believe that somehow, the excel file logs the original filename somewhere as a constant and it is where Access looks up the filename in its saved import procedure.
Some things I will try.
Instead of overwriting the first xlsx file using excel saveas, I will delete the original file before doing the saveas, so I will not be overwriting an existing file.
I will also try renaming the file in the directory tree, rather than renaming it with a saveas.
Any other ideas as to what is going on or a work around would be helpful.
Thanks.
RE: Saved Imoorts problem with file name
RE: Saved Imoorts problem with file name
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Saved Imoorts problem with file name
- Open your Excel file with your data that causes the issue
- Open another fresh, empty Excel file
- Copy data from your original Excel file
- Paste the data into your ‘fresh’ empty Excel file
- Close your original Excel file and Delete it in Windows Explorer
- Save your remaining Excel file with the name you want
- Do your Access Import again
This way the new Excel file should have no association with any previous, old, problematic names.Just a suggestion...
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Saved Imoorts problem with file name
dhookum, I will see if I can find out how to access system tables.
I just setup a VBA import procedure that works great, except I get errors because of the one field that defaults to "double" instead of "short text". the values are mostly digits as they are account numbers, but they create havoc when importing a table.
So the VBA procedure fixes the filename problem, but I'm back to a datatype problem.
I found a great video that offered the following code for doing an import. I wonder if there is anywhere I can use VBA to handle the data type.
Option Compare Database
Option Explicit
Public Sub ImportExcelSpreadsheet(filename As String, tableName As String)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, filename, True
End Sub
Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant
Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
If diag.Show Then
For Each item In diag.SelectedItems
Me.txtFileName = item
Next
End If
End Sub
Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject
If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file!"
Exit Sub
End If
If FSO.FileExists(Nz(Me.txtFileName)) Then
ExcelImport.ImportExcelSpreadsheet Me.txtFileName, "tblImportTemp"
Else
MsgBox "File not found"
End If
End Sub
RE: Saved Imoorts problem with file name
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Saved Imoorts problem with file name
RE: Saved Imoorts problem with file name
The first article is found at https://accessexperts.com/blog/2020/10/19/opening-... and it really got my attention. Thanks!
RE: Saved Imoorts problem with file name
"download a table as a csv from a third party and then save it as an xlsx file"
Depending how you do it, you may run into an issue of Excel 'interpreting' some data NOT the way you would expect or want.
CSV is just a Comma Separated Value text file that can be easily read by VBA and all elements could be easily inserted into your tblImportTemp table, no need for Access' build-in DoCmd.TransferSpreadsheet functionality.
Yes, I know - it is nice to have it ready to use so you don't have to re-invent the wheel, and if it works the way you want - great. But, if it does not, like in your situation, then your own code of transferring the data straight from CSV file into a table, with your full control, sounds like a better solution.
Just my opinion...
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Saved Imoorts problem with file name
RE: Saved Imoorts problem with file name
and you want to insert those records into your Access table named MyAccessTable with 3 fields defined as number, text, and number
CODE
Insert Into MyAccessTable Values (123, 'Bob', 987)
Insert Into MyAccessTable Values (234, 'Bill', 345)
Insert Into MyAccessTable Values (345, 'Susie', 765)
Insert Into MyAccessTable Values (456, 'Andy', 463)
Insert Into MyAccessTable Values (567, 'JayRo', 646)
Insert Into MyAccessTable Values (567, 'TheEnd', 987)
Instead of Debug.Print, just Execute these Insert statements in Access.
No Excel needed and you are in full control.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson