Import .txt rather than .xlsx

[camollie]

HiI have a number of modules to import “.xlsx” files – which works all though I sourced this code from a forum and I think it was originally written to loop the import function for all files in the folder I have a separate module for each file I want to import as defined by the “strFile Like “DayXX_SL.xlsx” request line.The problem I have is that I don’t really want the files to be imported in .xlsx format as there is a manual process currently needed to change the Excel file from .xls (XML) format to .xlsx – reason being the XML version has meta data in the top rows and this gets removed when saving to .xlsx.I can get the source data in a .txt format however how do I change the code below to import “Day16_SL.txt” file instead of excel version. I tried just changing ‘name of excel spreadsheet to import If strFile Like "Day16_SL.xlsx; ThenBut life is not that easy ☹ ….any ideas as to achieve the below results but with the importing of text file rather than .xlsx ???Current Script….. Dim strPath As String Dim strFile As String Dim strSQL As String' Set file directory to load fields from strPath = 'C:\Import\SEP'' Tell it to import all Excel files strFile = Dir(strPath)' Start loop Do While strFile 'name of excel spreadsheet to import If strFile Like 'Day16_SL.xlsx' Then ' Import file DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, 'tblDay16_SL', strPath & strFile, True ' Run update query to put import .xlsx into db table tblDay16 and add filename to table records in table field SL_FileDate strSQL = 'UPDATE tblDay16_SL SET SL_FileDate=' & Chr(34) & strFile & Chr(34) & 'WHERE SL_FileDate IS NULL' DoCmd.RunSQL strSQL DoCmd.SetWarnings False ' Loop to next file in directory End If strFile = Dir LoopDoCmd.SetWarnings FalseEnd FunctionPlease note aware double quotes not appearing in message (this is due to cutting and pasting from Word

 

[camollie]

Sorry for format of posting - trying to sort justification

 

[arnelgp]

use DoCmd.TransferText:

DoCmd.TransferText acImportDelim,,"tableToImportTo","textFileToImport",True

 

[arnelgp]

   Dim strPath As String
    Dim strFile As String
    Dim strSQL As String
    
    strPath = "C:\Import\SEP\"
    strFile = Dir(strPath & ".TXT")
    While strFile <> ""
        If strFile Like "Day16_SL.txt" Then
            DoCmd.TransferText acImportDelim, , "tblDay16_SL", strPath & strFile, True
            strSQL = "UPDATE tblDAy16_SL SET SL_FileDate=" & _
            Chr(34) & strFile & Chr(34) & " WHERE SL_FileDate Is Null"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
        End If
        strFile = Dir()
    Wend

 

[camollie]

Many thanks for swift reply, I'll give it a try and see how I go - thanks again

 

[camollie]

Hi - I have given it a go but nothing happens, no error message, no debug, just dead - do I need to specify "specificationFile" ? would it be more practical to import files as .csv format??? - based on your experience/best practice ?

 

[camollie]

Solved - thanks ArneLpg - you got me heading in the right direction (ended up settling with *.csv as that appears easier for IT. Stuck with original script with you DoCmd knife and forked in - thanks for your directionublic Function ImportDay02() Dim strPath As String Dim strFile As String Dim strSQL As String' Set file directory to load fiels from strPath = "C:\AccessImport\APR"' Tell it to import all Excel files strFile = Dir(strPath)' Start loop Do While strFile "" If strFile Like "ImportTest.csv" Then ' Import file DoCmd.TransferText acImportDelim, CSVImport, "tblDay01", strPath & strFile, True ' Run update query DoCmd.SetWarnings False strSQL = "UPDATE tblDay01 SET WiP_ProgressFileDate=" & Chr(34) & strFile & Chr(34) & "WHERE FileDate IS NULL" DoCmd.RunSQL strSQL DoCmd.SetWarnings False ' Loop to next file in directory End If strFile = Dir LoopDoCmd.SetWarnings FalseEnd Function