Saved Imoorts problem with file name

[JayRo71]

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.

 

[JayRo71]

I tried different ways of renaming the xlsx file and none worked. One thing that stands out to me is that Acceess defaults to the original file name when creating the table during the import. I am not at all sure where access is getting that information as the table is already renamed before I try to import it. Maybe this is a known problem?

 

[dhookom]

The import specs are saved in two system tables that you could review. I believe the table names begin with msysIMEX….

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

In this particular case and instance, what I would do:
[ul]
[li]Open your Excel file with your data that causes the issue[/li]
[li]Open another fresh, empty Excel file[/li]
[li]Copy data from your original Excel file[/li]
[li]Paste the data into your ‘fresh’ empty Excel file[/li]
[li]Close your original Excel file and Delete it in Windows Explorer[/li]
[li]Save your remaining Excel file with the name you want[/li]
[li]Do your Access Import again[/li]
[/ul]

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

 

[JayRo71]

Andy, Great suggestion from a troubleshooting standpoint. Abandoning the saved import and doing a new step by step import would be faster though.
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

 

[dhookom]

Ben Clothier has an article that might help at

https://accessexperts.com/blog/2020...ers-for-text-file-sources-schema-information/

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JayRo71]

I will check it out... Thank you!

 

[JayRo71]

dhookum, you definitely pointed me in the right direction. In fact, I went back to the first in the series of articles and I am very intrigued after reading a couple of paragraphs...
The first article is found at

https://accessexperts.com/blog/2020/10/19/opening-or-linking-text-or-excel-in-access/

and it really got my attention. Thanks!

 

[Andrzejek]

This is just my personal opinion, but...
"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 [tt]tblImportTemp[/tt] table, no need for Access' build-in [tt]DoCmd.TransferSpreadsheet[/tt] 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

 

[JayRo71]

Andy, I have run into a wall and will definitely take your advice. If I can bring the csv into an access table using vba, it would solve a lot of problems. My only concern is that tblImportTemp is the source of an append query. It should not make any difference though. I will report back how I make out. I was able to open the excel table using the article that Dhookum provided to me, but I was only able to get the records to "print" in the immediate window. I was not exactly sure how to pass that along to a table object. The csv import might be easier.

 

[Andrzejek]

Let's say this is your [tt]C:\TMP\MyCSVFile.csv[/tt] file with 3 fields (no header):

[pre]
123,Bob,987
234,Bill,345
345,Susie,765
456,Andy,463
567,JayRo,646
567,TheEnd,987[/pre]

and you want to insert those records into your Access table named [tt]MyAccessTable[/tt] with 3 fields defined as number, text, and number

Sub TransferCSVtoTable()
Dim strTextLine As String
Dim ary() As String
Dim strOut As String
Dim x As Integer

Open [red]"C:\TMP\MyCSVFile.csv"[/red] For Input As #1
Do While Not EOF(1)
    Line Input #1, strTextLine
    ary = Split(strTextLine, ",")
    strOut = "Insert Into MyAccessTable Values ("
    For x = LBound(ary) To UBound(ary)
        Select Case x
            Case 1
                strOut = strOut & "'" & ary(x) & "'" [green]'text needs to be in single quotes[/green]
            Case Else
                strOut = strOut & ary(x)
        End Select
        If x < UBound(ary) Then
            strOut = strOut & ", "
        End If
    Next x
    strOut = strOut & ")"[blue]
    Debug.Print strOut[/blue]
Loop
Close #1
End Sub

So you get:[tt]
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)[/tt]
Instead of [blue]Debug.Print[/blue], just [tt]Execute[/tt] 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