×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Saved Imoorts problem with file name

thread700-1820222

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.

RE: Saved Imoorts problem with file name

(OP)
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?

RE: Saved Imoorts problem with file name

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

RE: Saved Imoorts problem with file name

In this particular case and instance, what I would do:
  • 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

(OP)
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

RE: Saved Imoorts problem with file name

(OP)
I will check it out... Thank you!

RE: Saved Imoorts problem with file name

(OP)
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-... and it really got my attention. Thanks!

RE: Saved Imoorts problem with file name

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

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Saved Imoorts problem with file name

(OP)
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.

RE: Saved Imoorts problem with file name

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

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

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

CODE

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

Open "C:\TMP\MyCSVFile.csv" 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) & "'" 'text needs to be in single quotes
            Case Else
                strOut = strOut & ary(x)
        End Select
        If x < UBound(ary) Then
            strOut = strOut & ", "
        End If
    Next x
    strOut = strOut & ")"
    Debug.Print strOut
Loop
Close #1
End Sub
So you get:
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.wiggle
No Excel needed and you are in full control.



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close