Parsing .tsv file into table using VBA (1 Viewer)

[pstraszynski]

I have about 75 .tsv files that I want to load into an Access table using VBA. Each .tsv file contains about 25 fields and one .tsv file corresponds to one record in the table.
I don't know how to parse the fields or design a loop that will continue until "end of file" (i.e. until all .tsv files have been processed).

 

[jdraw]

By .tsv do you mean Tab separated variables?

Access can import .csv and if you use the import wizard, you have an option to define the separator character ----- "," or Tab...

 

[pstraszynski]

Need to use VBA for this

I need to do this in VBA so that I can loop through all of the tsv files. There is also some logic I need to apply for error handling. Therefore using a wizard will not help. (.tsv stands for tab seperated values, but in my file the delimiter is actually a "|"). - Paul

 

[jdraw]

A few questions:

Are the 75 files all the same layout? Same fields etc.

Do some searching in Access help on the

Transfertext method

If you maually do the import of 1 file, just to establish an import specification, then save that spec.

Create a loop to get each of your 75 files

You may have to build 75 tables then do a loop to append each of those tables to a final table with all records.

Just some thoughts.

 

[pstraszynski]

I'm trying to load a table with this code

DoCmd.TransferText acImportDelim, Nov3Import, "U:\Power Smart\ecoEnergy\Test\2706d00312.tsv", True

where the Access table is Nov3Import and the .tsv file to be loaded is "U:\Power Smart\ecoEnergy\Test\2706d00312.tsv". When I run the code, it tells me "You cannot import this file." Could I be missing a parameter in the code? Or do I need to specify the delimiter, and if so, where?

 

[jdraw]

I think you may be missing some parameters. I think you'll have to rename the file -- I don;t think it likes .tsv.

You could call it .txt .csv .asc

This is from Access Help

TransferText Method
See AlsoApplies ToExampleSpecificsThe TransferText method carries out the TransferText action in Visual Basic.

expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
expression Required. An expression that returns one of the objects in the Applies To list.

TransferType Optional AcTextTransferType.

AcTextTransferType can be one of these AcTextTransferType constants.
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim default
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
If you leave this argument blank, the default constant (acImportDelim) is assumed.

Notes

Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).


SpecificationName Optional Variant. A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

TableName Optional Variant. A string expression that's the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.

FileName Optional Variant. A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.

HTMLTableName Optional Variant. A string expression that's the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the transfertype argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.

CodePage Optional Variant. A Long value indicating the character set of the code page.

Remarks
For more information on how the action and its arguments work, see the action topic.

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.


Note You can also use ActiveX Data Objects (ADO) to create a link by using ActiveConnection property for the Recordset object.


Example
The following example exports the data from the Microsoft Access table External Report to the delimited text file April.doc by using the specification Standard Output:

DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"

Did you try making a specification file and saving it. During that process you have an option to identify the delimiter.

Do you have a sample tsv file you can post? I could try the specification.

 

[pstraszynski]

Below is a sample file that I am trying to load. I have changed the extension to .txt. The values are parsed by "|". - Paul

 

[Kiwiman]

Howzit

Here is what I use to loop through a file folder and get each of the relevant file names based on the file extension. In my case I was looking at the avi files.

HTH

Private Function IMPORTDATA(x As String) 'Import any new movies added to my films table
'Stop
Dim sPath As String
Dim dPath As String
Dim strSQL As String
Dim strFile As String
Dim strFileList() As String
Dim FileName As String
Dim intFile As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

sPath = "Y:\Films\"         ' Set the intial directory
sPath = sPath & x & "\"    ' Add the supplied sub folder - the genre

dPath = sPath


'Loop through the folder & build file list looking for only avi files
  strFile = Dir(dPath & "*.avi")
  
 While strFile <> ""
     'add files to the list
     intFile = intFile + 1
     ReDim Preserve strFileList(1 To intFile)
     strFileList(intFile) = strFile
      strFile = Dir()
  Wend
  
  'see if any files were found
  If intFile = 0 Then
    MsgBox "No files found"
    intCONTINUE = 1
    Exit Function
  End If


' For each file found compare to the existing list of movies added, and if missing add new file name to table - not the extension
 For intFile = 1 To UBound(strFileList)
    
    For each file found perform some actions
  
Next intFile


End Function__________________

 

[pstraszynski]

It's still not working. I can't see why it's giving me the error message "You cannot import this file". (err 31519) It would seem a simple task to import a .txt file into a table (with the same number & type of fields) using TransferText. I can import the file into the table using the wizard but I need to do this with VBA code. Any ideas? Here it is again:

DoCmd.TransferText acImportDelim, "Customer1", "U:\Power Smart\ecoEnergy\Test\Hold\2706d00312.txt", True

 

[jdraw]

Part of the problem is in the test file there is an attempt to put 2 names in the ClientName field. This has caused other fields to be offset by 2 fields. I used one of my procs to list the fields and data. You may have to cleanse the data before attempting to import it into Access.


I could not get the wizard to accept the | delimiter, so I changed all | to ,
It still said some field names were not acceptable and Access would adjust those.

When I saw Ken in the Telephone field I knew something was wrong.
Hope this is helpful to you.

 

[Kiwiman]

Howzit

This works below with the pipe delimiter - you will need to create an import spec first.

Create your import spec with the pipe delimiter, has fieldnames etc. You will need to go into Advanced to set the delimiter and save the import etc.

I created 3 separate files from your txt file and placed in a folder. This process imported all 3 just fine. I did not spend any time validating any data.

Private Sub IMPORTDATA() 'Import any new files 
Dim sPath As String
Dim dPath As String
Dim strSQL As String
Dim strFile As String
Dim strFileList() As String
Dim FileName As String
Dim intFile As Integer
Dim strImport As String
Dim strTable As String

sPath = "C:\Users\Les\Downloads\Text Files\"         ' Set the intial directory

'dPath = sPath
strTable = "tblImport"          ' Set the target table
strImport = "impTest"           ' Set the import spec

'Loop through the folder & build file list looking for only avi files
  strFile = Dir(sPath & "*.txt")
  
 While strFile <> ""
     'add files to the list
     intFile = intFile + 1
     ReDim Preserve strFileList(1 To intFile)
     strFileList(intFile) = strFile
      strFile = Dir()
  Wend
  
  'see if any files were found
  If intFile = 0 Then
    MsgBox "No files found"
    Exit Sub
  End If


' For each file found import the data into the table
 For intFile = 1 To UBound(strFileList)
    
    FileName = strFileList(intFile)
   
    DoCmd.TransferText acImportDelim, strImport, strTable, FileName, True
    
    'Update a file name field so we can trace back to original source file
    strSQL = "UPDATE tblImport SET tblImport.FileName = '" & FileName & "' WHERE (((tblImport.FileName) Is Null));"
    
    CurrentDb.Execute strSQL

    
Next intFile

End Sub

 

[marnieg]

I am trying a similar task for reading in a .csv. I have an .adp file and when I try to import the csv using the "Get External Data" Import Wizard it will NOT allow me to create a SPEC. Is this because I have SQL backend? The option to save the spec is greyed out.

Thanks for any help. I am using Access 2003 and SQL backend for my database.

Marnie