transfertext specifications

[TerryC]

Thanks very much for any help

Hi All,

I have a need to import tab delimited text files into my DB (first row has
field names).
The files are similar in layout BUT not the same (unfortunately).
I can set up an import spec for a specific layout and all works fine.

Linking to the text file without a spec doesn't seem to be any better.

What I want is a way to map the fields I want in the text file to my table
fields.
The text file field names (I am interested in) are consistent but not always
in the same column. Is there are way to iterate through the first text file
row and build an import spec using VBA?
Importing without defining a spec bombs out as Access defaults to comma
seperated. And even if I overcome that I really want to import all fields as
text and preserve the leading zero(s) in some fields.

eg.
One text file will look like:
Sort Order*
Name
address1
address2
Locality
State
Postcode*
ppsp *
ppsp code*

And another will look like:
Sort Order*
title
first name
last name
position
company
address1
address2
address3
Locality
state
postcode*
phone
email
ppsp*
ppsp code*

The fields with * are the only fields I really want and they will be in all
varieties of the text files.

Any suggestions on getting these into my mdb either straight in or via a
temp table or something would be greatly appreciated.

Getting things changed at text file generation end is not an option -
unfortunately.


Thanks very much for reading and for any assistance.

Terry

 

[j.nurick]

Hi Terry,

To handle the layout, IMO it's simpler to write code to create a
schema.ini file than to create an import specification. The following
articles explain schema.ini:

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Having created schema.ini (in the same folder as the file to be
imported, and with a section who name matches the filename), you can
then use TransferText to create a linked table connected to the file
(with a standard name such as tblImportTemp), then run an append query
to move just the data you want into your 'real table' and finally
delete tblImportTemp.

Alternatively you can cut out the middleman by using an append query
that gets its data directly from the text file, with syntax like this:

INSERT INTO MyTable
SELECT [Sort Order], Postcode, ppsp, [ppsp code]
FROM [Text;HDR=Yes;Database=C:\My Folder\;].[MyFile#txt]
;

The simplest way to do that is to have a dummy query in the database,
then build the SQL for the query in a string variable and assign it to
the query's SQL property and run the query. At the end of this post
I've pasted a VBA procedure that helps turn an ordinary filespec into
the source string needed for the query.

Thanks very much for any help

Hi All,

I have a need to import tab delimited text files into my DB (first row has
field names).
The files are similar in layout BUT not the same (unfortunately).
I can set up an import spec for a specific layout and all works fine.

Linking to the text file without a spec doesn't seem to be any better.

What I want is a way to map the fields I want in the text file to my table
fields.
The text file field names (I am interested in) are consistent but not always
in the same column. Is there are way to iterate through the first text file
row and build an import spec using VBA?
Importing without defining a spec bombs out as Access defaults to comma
seperated. And even if I overcome that I really want to import all fields as
text and preserve the leading zero(s) in some fields.

[snip]

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[TerryC]

John,

Thanks a lot for the response. I'll give the schema option a go and post
back with how I went.

Thanks again,

Terry

Hi Terry,

To handle the layout, IMO it's simpler to write code to create a
schema.ini file than to create an import specification. The following
articles explain schema.ini:

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Having created schema.ini (in the same folder as the file to be
imported, and with a section who name matches the filename), you can
then use TransferText to create a linked table connected to the file
(with a standard name such as tblImportTemp), then run an append query
to move just the data you want into your 'real table' and finally
delete tblImportTemp.

Alternatively you can cut out the middleman by using an append query
that gets its data directly from the text file, with syntax like this:

INSERT INTO MyTable
SELECT [Sort Order], Postcode, ppsp, [ppsp code]
FROM [Text;HDR=Yes;Database=C:\My Folder\;].[MyFile#txt]
;

The simplest way to do that is to have a dummy query in the database,
then build the SQL for the query in a string variable and assign it to
the query's SQL property and run the query. At the end of this post
I've pasted a VBA procedure that helps turn an ordinary filespec into
the source string needed for the query.

Thanks very much for any help

Hi All,

I have a need to import tab delimited text files into my DB (first row has
field names).
The files are similar in layout BUT not the same (unfortunately).
I can set up an import spec for a specific layout and all works fine.

Linking to the text file without a spec doesn't seem to be any better.

What I want is a way to map the fields I want in the text file to my table
fields.
The text file field names (I am interested in) are consistent but not always
in the same column. Is there are way to iterate through the first text file
row and build an import spec using VBA?
Importing without defining a spec bombs out as Access defaults to comma
seperated. And even if I overcome that I really want to import all fields as
text and preserve the leading zero(s) in some fields.

[snip]

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[TerryC]

John,

Finally an update on progress.

Created a function to generate the schema.ini however it appears the schema
is ignored when a delimited file is specified. The help indicates a schema
will be read when a fixed width file is specified.
Seeing as I have pulled the first row to pieces to generate the schema file
I may as well loop through all the rows and write the fields I want into a
table and forget about the transfer text functions.

A somewhat frustrating excercise this is turning into.

Once again thanks for your interest.

Terry

Hi Terry,

To handle the layout, IMO it's simpler to write code to create a
schema.ini file than to create an import specification. The following
articles explain schema.ini:

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Having created schema.ini (in the same folder as the file to be
imported, and with a section who name matches the filename), you can
then use TransferText to create a linked table connected to the file
(with a standard name such as tblImportTemp), then run an append query
to move just the data you want into your 'real table' and finally
delete tblImportTemp.

Alternatively you can cut out the middleman by using an append query
that gets its data directly from the text file, with syntax like this:

INSERT INTO MyTable
SELECT [Sort Order], Postcode, ppsp, [ppsp code]
FROM [Text;HDR=Yes;Database=C:\My Folder\;].[MyFile#txt]
;

The simplest way to do that is to have a dummy query in the database,
then build the SQL for the query in a string variable and assign it to
the query's SQL property and run the query. At the end of this post
I've pasted a VBA procedure that helps turn an ordinary filespec into
the source string needed for the query.

Thanks very much for any help

Hi All,

I have a need to import tab delimited text files into my DB (first row has
field names).
The files are similar in layout BUT not the same (unfortunately).
I can set up an import spec for a specific layout and all works fine.

Linking to the text file without a spec doesn't seem to be any better.

What I want is a way to map the fields I want in the text file to my table
fields.
The text file field names (I am interested in) are consistent but not always
in the same column. Is there are way to iterate through the first text file
row and build an import spec using VBA?
Importing without defining a spec bombs out as Access defaults to comma
seperated. And even if I overcome that I really want to import all fields as
text and preserve the leading zero(s) in some fields.

[snip]

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[j.nurick]

Schema.ini should work with delimited files. I can't remember the
details and can't get to my test database just now, but if you're
using TransferText try omitting the TransferType parameter or
specifing acImportFixed if acImportDelim isn't doing what you need.

John,

Finally an update on progress.

Created a function to generate the schema.ini however it appears the schema
is ignored when a delimited file is specified. The help indicates a schema
will be read when a fixed width file is specified.
Seeing as I have pulled the first row to pieces to generate the schema file
I may as well loop through all the rows and write the fields I want into a
table and forget about the transfer text functions.

A somewhat frustrating excercise this is turning into.

Once again thanks for your interest.

Terry

Hi Terry,

To handle the layout, IMO it's simpler to write code to create a
schema.ini file than to create an import specification. The following
articles explain schema.ini:

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Having created schema.ini (in the same folder as the file to be
imported, and with a section who name matches the filename), you can
then use TransferText to create a linked table connected to the file
(with a standard name such as tblImportTemp), then run an append query
to move just the data you want into your 'real table' and finally
delete tblImportTemp.

Alternatively you can cut out the middleman by using an append query
that gets its data directly from the text file, with syntax like this:

INSERT INTO MyTable
SELECT [Sort Order], Postcode, ppsp, [ppsp code]
FROM [Text;HDR=Yes;Database=C:\My Folder\;].[MyFile#txt]
;

The simplest way to do that is to have a dummy query in the database,
then build the SQL for the query in a string variable and assign it to
the query's SQL property and run the query. At the end of this post
I've pasted a VBA procedure that helps turn an ordinary filespec into
the source string needed for the query.

Thanks very much for any help

Hi All,

I have a need to import tab delimited text files into my DB (first row has
field names).
The files are similar in layout BUT not the same (unfortunately).
I can set up an import spec for a specific layout and all works fine.

Linking to the text file without a spec doesn't seem to be any better.

What I want is a way to map the fields I want in the text file to my table
fields.
The text file field names (I am interested in) are consistent but not always
in the same column. Is there are way to iterate through the first text file
row and build an import spec using VBA?
Importing without defining a spec bombs out as Access defaults to comma
seperated. And even if I overcome that I really want to import all fields as
text and preserve the leading zero(s) in some fields.

[snip]

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[TerryC]

John,

The never ending thread.

I have coded the import by readline etc and written it into a temporary
import table and then append it to the final table. Typical imports are only
between 2000 and 15000 rows and this works plenty quick enough for nmow.

I will experiment further with transfertext and schema files in the near
future.

Once again thanks very much.

Terry

Schema.ini should work with delimited files. I can't remember the
details and can't get to my test database just now, but if you're
using TransferText try omitting the TransferType parameter or
specifing acImportFixed if acImportDelim isn't doing what you need.

John,

Finally an update on progress.

Created a function to generate the schema.ini however it appears the schema
is ignored when a delimited file is specified. The help indicates a schema
will be read when a fixed width file is specified.
Seeing as I have pulled the first row to pieces to generate the schema file
I may as well loop through all the rows and write the fields I want into a
table and forget about the transfer text functions.

A somewhat frustrating excercise this is turning into.

Once again thanks for your interest.

Terry

Hi Terry,

To handle the layout, IMO it's simpler to write code to create a
schema.ini file than to create an import specification. The following
articles explain schema.ini:

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Having created schema.ini (in the same folder as the file to be
imported, and with a section who name matches the filename), you can
then use TransferText to create a linked table connected to the file
(with a standard name such as tblImportTemp), then run an append query
to move just the data you want into your 'real table' and finally
delete tblImportTemp.

Alternatively you can cut out the middleman by using an append query
that gets its data directly from the text file, with syntax like this:

INSERT INTO MyTable
SELECT [Sort Order], Postcode, ppsp, [ppsp code]
FROM [Text;HDR=Yes;Database=C:\My Folder\;].[MyFile#txt]
;

The simplest way to do that is to have a dummy query in the database,
then build the SQL for the query in a string variable and assign it to
the query's SQL property and run the query. At the end of this post
I've pasted a VBA procedure that helps turn an ordinary filespec into
the source string needed for the query.

On Tue, 19 Jun 2007 22:08:00 -0700, TerryC

Thanks very much for any help

Hi All,

I have a need to import tab delimited text files into my DB (first row has
field names).
The files are similar in layout BUT not the same (unfortunately).
I can set up an import spec for a specific layout and all works fine.

Linking to the text file without a spec doesn't seem to be any better.

What I want is a way to map the fields I want in the text file to my table
fields.
The text file field names (I am interested in) are consistent but not always
in the same column. Is there are way to iterate through the first text file
row and build an import spec using VBA?
Importing without defining a spec bombs out as Access defaults to comma
seperated. And even if I overcome that I really want to import all fields as
text and preserve the leading zero(s) in some fields.

[snip]

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function