Delimited Or Fixed width? Spec's behaviour is strange.

[GPO]

Access 2000 SP3

Hi All,

As a preliminary step to importing a tab delimited text file, I decided I
wanted to have a look at the text in a more general sense (to check for
blank lines, non ASCII chars, commas instead of tabs etc).

I came up with the bright idea of importing each row of text into a single
memo field. To save an import spec for this, I set it to fixed width and set
the width to 32,000. In the import spec form (from the advanced... button of
the text import wizard), if you set file format to fixed width, the
delimiter combobox is disabled, which makes sense. This is where things got
wierd though. If the underlying field delimiter is {tab}(in the disabled
combo box) and you try and import a fixed width file with tabs in it using
DoCmd.TransferText, it still delimits on the tabs! If the underlying field
delimiter is comma and you try and import a fixed width file with tabs in it
using DoCmd.TransferText, it still delimits on the commas! Note that fixed
width has been selected and saved in the spec.

Has anyone else seen this before?

GPO

 

[Joe Fallon]

Never tried it.

Why not just import the file yourself and bypass the wizard?

Outline:
Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub

 

[GPO]

Thanks for this. I worked it out. I had the wrong argument (acImportDelim)
in the DoCmd.TransferText method and it was overriding the spec. Having said
that, your suggestion is eminently better anyway!

GPO

 

[Joe Fallon]

"Eminently".
I like that. <g>