Solved text (Tab Delimited)(*.txt)

[zezo2021]

Hello frinds

I want VBA code open all files in a folder with extension txt (Tab Delimited)(*.txt) and save it again as xls or xlsx in backend process

Thank you so much

 

[The_Doc_Man]

This isn't so easy as you might think. See this article from Stack Overflow.

import tab-delimited txt into Access table using VBA

I am trying to import a tab-delimited txt file into an Access table using VBA. In my code, I want to insert it into a table that has not yet been created. Here is what I tried doing. Note - I was ...

stackoverflow.com

Or you might research this yourself by doing a web search for "ms access vba import tab delimited file" and look at the articles you drag up. You can do it with an Import Text where you specify that your fields are tab-delimited and the SO article includes screen shots. Doing it via vba code might be a little trickier. The "all files in a particular folder with a particular extension" is EASILY within the abilities of using File System Object to find the folder, generate the collection of matching files, and iterate through the matched files. But that code-based Import of arbitrary files? Not so easy.

 

[zezo2021]

This isn't so easy as you might think. See this article from Stack Overflow.

import tab-delimited txt into Access table using VBA

I am trying to import a tab-delimited txt file into an Access table using VBA. In my code, I want to insert it into a table that has not yet been created. Here is what I tried doing. Note - I was ...

stackoverflow.com

Or you might research this yourself by doing a web search for "ms access vba import tab delimited file" and look at the articles you drag up. You can do it with an Import Text where you specify that your fields are tab-delimited and the SO article includes screen shots. Doing it via vba code might be a little trickier. The "all files in a particular folder with a particular extension" is EASILY within the abilities of using File System Object to find the folder, generate the collection of matching files, and iterate through the matched files. But that code-based Import of arbitrary files? Not so easy.

Thanks for your reply

I'm start step by step
First Step

here select folder

Sub SelectFolder()
Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(4)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
    End If
End Sub

Second
I need to oben excel file in backend
open the file and save it again in xls or xlsx

 

[zezo2021]

Here is the code Open Excel File

Sub OpenExcel()
Dim objExcelApp As Excel.Application
Dim wb As Excel.Workbook
Set objExcelApp = Excel.Application
Set wb = objExcelApp.Workbooks.Open("E:\55.xls")
objExcelApp.Visible = True
End Sub

 

[Pat Hartman]

Unless you have to be able to import other tab delimited files on the fly, use the import wizard to define THIS one so you can create an import spec with column names, if they are there and the correct data type for each column.

In the TransferText, select the link option since you don't really want to import the data. Then you can use a TransferSpreadsheet to copy the linked txt file to a spreadsheet.

TWO lines of code unless you need to customize the file names.

 

[zezo2021]

I need now

Loop throug files one by one

Unless you have to be able to import other tab delimited files on the fly, use the import wizard to define THIS one so you can create an import spec with column names, if they are there and the correct data type for each column.

In the TransferText, select the link option since you don't really want to import the data. Then you can use a TransferSpreadsheet to copy the linked txt file to a spreadsheet.

TWO lines of code unless you need to customize the file names.

when try to import it as text delimited
this message appears

the file format and extension of don't match. the file could be corrupted or unsave. unless you trust its source , don't open it . do you want to open it anyway


maybe is exported form old computer system
but when open it and save it again in xls or xlsx work

 

[Pat Hartman]

You didn't post the code you are using and you didn't confirm that you created an Input spec. Are there file types other than .txt in the input folder?

 

[zezo2021]

The wizard is unable to access information in the file please check that the file exists and is in the correct format


The import wizard in access says that

I can't reach to spec page


I try to find the solution by opening it in Excel and saving it in a good format

 

[zezo2021]

I can open the file in excel

 

[zezo2021]

error also

DoCmd.TransferText acLinkDelim, , "SalesByHourSummary", strFileName, True


Table Does not exist

 

[Pat Hartman]

WHAT is the file extension??????

 

[zezo2021]

xls
but it corrupts


Friends

Why does the loop not work
Exit the loop
There is a lot of files

Option Compare Database
Dim sFolder As String


Private Sub Command0_Click()
SelectFolder


Dim strFileName As String
'TODO: Specify path and file spec
Dim strFolder As String: strFolder = sFolder
Dim strFileSpec As String: strFileSpec = strFolder & "*.*"
Dim FileList() As String
Dim intFoundFiles As Integer
strFileName = Dir(strFileSpec)


Do While Len(strFileName) > 0
    ReDim Preserve FileList(intFoundFiles)
    FileList(intFoundFiles) = strFileName
    intFoundFiles = intFoundFiles + 1
    strFileName = Dir
    
    
    
Dim objExcelApp As Excel.Application
Dim wb As Excel.Workbook
Set objExcelApp = Excel.Application

Set wb = objExcelApp.Workbooks.Open(strFileName)
With wb
    ActiveWorkbook.SaveAs FileName:=strFileName & ".xls", FileFormat _
        :=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False

objExcelApp.Visible = True
End With
    
    
    
    
    
    
Loop

End Sub
Sub SelectFolder()
    ' Open the select folder prompt
    With Application.FileDialog(4)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
   
End Sub

 

[Pat Hartman]

You said the file type was .txt We are talking about converting a .txt file to an .xlsx file.

If what you have is a file with a bad extension, rename the file to have the correct extension first, then do the conversion.

 

[The_Doc_Man]

Pat, renaming a file to a different extension doesn't always work. For example, ANSI text files and UTF-8 text files both have .TXT as a type but they are different. Old Word Perfect documents and old Word documents have .DOC as a type but they are different. The more complex the format, the less odds you have of getting it to work correctly - particularly now that Office 19 and O365 have eliminated some of the "automatic translation" cases that used to offered.

If zezo2021 has a .TXT file, the features in the file will confuse Excel if you rename it to .XLS or .XLSX, because certain features won't be present. The file will appear to be corrupted. (Not always... but not uncommon either.)

 

[gemma-the-husky]

You can't tell in code what separator a file has. The most common is Commas, but some use pipe symbols, tabs, or anything else. You could try each separator, and see how many sections you get. That might indicate which is most likely, but you can't know for certain.

 

[CJ_London]

Not sure why you are using access to import to excel, would think it makes more sense to have a routine in excel

The problem with delimiters is there are numerous possibilities. The default is ; and is set in the registry - so you can change the default.

Assuming this is a repeating exercise where the file name and possible location varies from week to week (and the file structure remains unchanged) the way I resolve the issue:

1. use the external data tab to link to the file once. This will create a connection string in the msysobjects table. It also populates the msysIMEXSpecs and msysIMEXColumns tables which is where the actual spec is stored - but no need to worry about that.

Connect	Database
Text;DSN=Testtab Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;ACCDB=YES;	D:\Dev

2. you can then copy this into a basic query

SELECT *
FROM
 (SELECT * FROM [TEXT;DSN=Testtab Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;ACCDB=YES;DATABASE=D:\Dev].testtab.txt)
 AS txt;

3. Save the query and you can now delete the original linked file

4. Now you have the sql, you can use code to modify the folder (DATABASE=D:\Dev) and the file name(.testtab.txt)

5. within this query and by aliasing as txt you can go to the QBE and add other tables to make more complex queries - for example using a left join to only import new records or delete existing ones.

6. or simple use the saved query in another query

7. or use transferspreadsheet to export the query to excel.

 

[Pat Hartman]

Doc, really, don't you think I know you can't arbitrarily change a file type and have the file magically change to be that type. The OP SAID he had a .txt file. People frequently get files from other applications with strange suffixes. I've had to change .dat files to .txt on multiple occasions. Especially when files are created by Unix. I'm just happy that they are really a text format behind the scenes no matter what the extension.