Changing the Default delimiter

  • Thread starter Thread starter Jose I. Avila
  • Start date Start date
J

Jose I. Avila

All,

I have a vb script that imports every file in a directory. The problem is
that the files are pipe(vertical bar) "|" delimited. I need to know how to
tell the docmd.transfertext line to use a "|".

Can anyone help me?

Thank you in advance for your help.

========================
Sub Import_Allura_Tbls()
Dim fs, FullPath,FC
Dim FileName As String ' file name
Dim TableName As String ' table name = file name without the .txt
Dim i As Integer


' Define text file
Set fs = CreateObject("Scripting.FileSystemObject")
FullPath = "Y:\data\Allura\u\staff\bin\Data\txt_data_files" ' 50+ text
files reside here.
Set Fol = fs.GetFolder(FullPath)
Set FC = Fol.Files
For Each fi In FC ' files in Files collection
FileName = fi ' fullpath & fi.ShortName
i = InStr(1, fi.Name, ".")
TableName = Left(fi.Name, i - 1)
'
DoCmd.TransferText acImportDelim, , "dbo_" & TableName, FileName
'<=== need help here
Next

End Sub
 
start a manual import of one of the text files. using the
wizard that opens up, create an import specification.
click on the Advanced... button and change the field
delimiter to the vertical bar, make any other changes
needed, and save the specification with a name that makes
sense.
use the specification name in the docmd.transfertext line.
hth

-----Original Message-----
All,

I have a vb script that imports every file in a directory. The problem is
that the files are pipe(vertical bar) "|" delimited. I need to know how to
tell the docmd.transfertext line to use a "|".

Can anyone help me?

Thank you in advance for your help.

========================
Sub Import_Allura_Tbls()
Dim fs, FullPath,FC
Dim FileName As String ' file name
Dim TableName As String ' table name = file name without the .txt
Dim i As Integer


' Define text file
Set fs = CreateObject("Scripting.FileSystemObject")
FullPath
Click to expand...
= "Y:\data\Allura\u\staff\bin\Data\txt_data_files" ' 50+
text
 
I know about the Import Specification... and that is not what I am looking
for b/c it only imports a file containing a specific format. I got 50+
files with different formats (i.e. # of columns and datatypes) I need to
import.

Thanks Anyways,

Jose

start a manual import of one of the text files. using the
wizard that opens up, create an import specification.
click on the Advanced... button and change the field
delimiter to the vertical bar, make any other changes
needed, and save the specification with a name that makes
sense.
use the specification name in the docmd.transfertext line.
hth

-----Original Message-----
All,

I have a vb script that imports every file in a directory. The problem is
that the files are pipe(vertical bar) "|" delimited. I need to know how to
tell the docmd.transfertext line to use a "|".

Can anyone help me?

Thank you in advance for your help.

========================
Sub Import_Allura_Tbls()
Dim fs, FullPath,FC
Dim FileName As String ' file name
Dim TableName As String ' table name = file name without the .txt
Dim i As Integer


' Define text file
Set fs = CreateObject("Scripting.FileSystemObject")
FullPath
Click to expand...
= "Y:\data\Allura\u\staff\bin\Data\txt_data_files" ' 50+
text
 
Jose,
You can either use an Import Specification or create a Schema.ini file,
which is not very different from an import spec.
In either case, you will have to have a means of telling Access the field
names and datatypes, unless you want access to guess at the datatypes, which
I would not do.

A third choice would be to replace the delimiter. You could write a little
VBScript
to do this. Look for `|` and replace with `","`
But remember, you will have to place a double quote at the beginning and at
the end of each record.

Good luck
HS
 
Jose:

You do this by creating an Import/Export specification to use in your
Transfer Text method call. Creating these is most easily done manually by
going to the File -> Get External Data -> Import option from the menus and
starting an import from your target file. When you get to the dialog that
allows you to set delimiters, choose the Advanced option and in that dialog
you can specify just about anything including delimiter and most importantly
you are given an option to save the specification. Save it under any name
you choose and then when you call Transfer Text, specify the name of the
Import Specification you just designed.
 
Back
Top