Open FileDialog convert .tsv to .txt then DoCmd.TransferText Tab Delimited file

[oxicottin]

Hello, I have been struggling with importing a .tsv file (Tab Delimited) into my table (tblExtractedTD). I have ran the wizard and created the Specification name (OracleSpecification) and everything was fine. I then needed to create a process:

1) Create/Open a FileDialog
2) Find my .tsv file (.tsv Tab Delimited file)
3) After selecting file and clicking "OK" (One File only allowed: .AllowMultiSelect = False)
4) Then my table (tblExtractedTD) needs cleared out for the new data but make sure a file was selected befor I do.

DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryRemoveExtractedTDData", acViewNormal, acEdit
   DoCmd.SetWarnings True

5) Then the file selected needs to have the extension temporarily changed to .txt for Access to import.
6) Then run the import:

DoCmd.TransferText , acImportDelim, "OracleSpecification", "tblExtractedTD",[b] **FILE NAME WITH THE .TXT EXT***[/b] , True

I have been looking for a working example but there is very little if any info on the subject. Thanks!

Thanks,
SoggyCashew.....

 

[SkipVought]

hi,

Why can't you change the extension before the entire process begins?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, changing the extension from .tsv to .txt is not a conversion of any kind. BOTH are simply text files.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[oxicottin]

Why can't you change the extension before the entire process begins?

I only want to change the extension so Access will import it, as a .tsv it wont import unless I change the extension. I want to keep the extension I have. Reason being I could have another text file when someone is searching for the correct one and if the wrong one is selected it could be a jumbled mess. I cant go by the file name eather because it always changes.

BTW, changing the extension from .tsv to .txt is not a conversion of any kind. BOTH are simply text files.

Yes I now I could just change .tsv to .txt but not everyone is computer smart and I want to make it as automated as possible.

Thanks!

Thanks,
SoggyCashew.....

 

[PHV]

So, what have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oxicottin]

No longer stuck I got it worked out.....

Thanks,
SoggyCashew.....

 

[SkipVought]

It would be gracious and courteous to share your solution for the benefit of other browsers.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[oxicottin]

Yes, it would be but how can I post something nobody knows what im taking about anyway... lol

I always post my results to help others and in case I need to look back again at it, the only reason I hadn't yet is because I had been sick. Below is what worked for me... Sorry for the delay!
=======================================================================================================
First get your .tsv file and import it and in advanced select the columns you want and be sure to name (Specification) it so every time you import the file it will import exactly how you set it up using the name you gave it. I called mine (OracleSpecification) and the table its in is called tbl_ImportedTabDelimited

Note: I think I had to change the .tsv to .txt so it would import the first time, you can change the extension and it wont hurt nothing.
========================================================================================================

Module Name: FileDialogPicker

Option Compare Database
Option Explicit

Public Function cmdFileDialogPicker(strTitle As String, strDescription As String, strExtension As String) As String
'*********************************************************************
' This Module Requires Including Microsoft OFFICE 12.0 Object Library
'*********************************************************************
Dim fDialog As Office.FileDialog
Dim strFileSelected As String
Dim i As Integer

'****************************************************************
' Instantiate the File Dialog Object

'****************************************************************
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'****************************************************************
' Set Up The File Dialog Parameters
'****************************************************************
With fDialog
    .AllowMultiSelect = False
    .Title = strTitle
    .Filters.Clear
    .Filters.Add "tsv files", "*.tsv"
    
'****************************************************************
' Present the file picker dialog box
'****************************************************************
    If .Show = True Then
          strFileSelected = .SelectedItems(1)
    Else
       strFileSelected = ""
    End If
End With

'****************************************************************
' Return The Full Path and File Name Selected
'****************************************************************
cmdFileDialogPicker = strFileSelected

End Function

Button Name of Form: cmdImportTabDelimited

Option Compare Database
Option Explicit

Private Sub cmdImportTabDelimited_Click()

' **********************************************************
' For File Open Setup
' **********************************************************
Dim strTitle As String
Dim strDescription As String
Dim strExtension As String
Dim strInputFileName As String
Dim strFileCopy As String, objFile, objFileSystem

' **********************************************************
' Prepare Parameters To Pass To The File Open Dialog
' **********************************************************
strTitle = "Import Tab-Delimited File"
strDescription = "tsv File"
strExtension = "*.tsv"

' **********************************************************
' Get The File Name From The User Dialog Box
' **********************************************************
strInputFileName = cmdFileDialogPicker(strTitle, strDescription, strExtension)

' **********************************************************
' If Null Then Exit
' **********************************************************
If strInputFileName = "" Then
    MsgBox ("You Clicked The Cancel Button")
    Exit Sub
End If

' **********************************************************
' Prepare the table for import
' **********************************************************
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_ClearImportedTabDelimited", acViewNormal, acEdit
DoCmd.SetWarnings True

' **********************************************************
' Import the tab-delimited file to the target table
' **********************************************************

'   Create a filesystem object so we can play with files
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    
'   Create a reference to the .TSV file
    Set objFile = objFileSystem.GetFile(strInputFileName)
    
'   Copy the .TSV file, using a .TXT extension, which Access knows about
    strFileCopy = Left(strInputFileName, Len(strInputFileName) - 3) & "txt"
    objFile.Copy strFileCopy, True
    
'   Don't need the filesystem objects any more - cleanup memory
    Set objFileSystem = Nothing
    Set objFile = Nothing
    
'   Wait till the file is done copying (just in case)
    DoEvents
    
'   Import the data from the TXT file
    DoCmd.TransferText acImportDelim, "OracleSpecification", "tbl_ImportedTabDelimited", strFileCopy, True
    
'   Wait till we're done importing (again, just in case)
    DoEvents

'   Delete the TXT file
    Kill strFileCopy

End Sub

Thanks,
SoggyCashew.....