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
= "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
>
>
>.
>

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

"tina" <(E-Mail Removed)> wrote in message
news:0bc801c37f23$38e599e0$(E-Mail Removed)...
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
= "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
>
>
>.
>

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 I. Avila" <(E-Mail Removed)> wrote in message
news:OA2%(E-Mail Removed)...
> 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
>
> "tina" <(E-Mail Removed)> wrote in message
> news:0bc801c37f23$38e599e0$(E-Mail Removed)...
> 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
> = "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
> >
> >
> >.
> >
>
>

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.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

"Jose I. Avila" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>