How to replace a null column value in a fixed length text file

  • Thread starter Thread starter Rico
  • Start date Start date
R

Rico

Hello,

I have a text file that has a one character column in it. In some records,
this column has an "S" in it, or else it is blank. I'm trying to import
this file into an Access database using VB. The table that I'm importing to
uses this column as part of the primary key. Because of this, the records
that contain a blank in this column, aren't being imported.

My question is; is there an easy way to update this text file directly,
where if the column is blank (space) then update it to use a specific
character? Or, is there a way to import this file and ignore the blank?

Any help would be greatly appreciated.

Thanks!
Rick
 
Rico said:
I have a text file that has a one character column in it. In some records,
this column has an "S" in it, or else it is blank. I'm trying to import
this file into an Access database using VB. The table that I'm importing to
uses this column as part of the primary key. Because of this, the records
that contain a blank in this column, aren't being imported.

My question is; is there an easy way to update this text file directly,
where if the column is blank (space) then update it to use a specific
character? Or, is there a way to import this file and ignore the blank?
Click to expand...

If it's truly how you describe it, with one space then a Cr/Lf, you could do it
like:

Call WriteFile(NewFilename, Replace(ReadFile(OldFilename), " " & vbCrLf, "X" &
vbCrLf))

Public Function ReadFile(ByVal FileName As String) As String
Dim hFile As Long
On Error GoTo Hell
hFile = FreeFile
Open FileName For Binary As #hFile
ReadFile = Space$(LOF(hFile))
Get #hFile, , ReadFile
Close #hFile
Hell:
End Function

Public Function WriteFile(ByVal FileName As String, ByVal Text As String) As
Boolean
Dim hFile As Long
On Error GoTo Hell
hFile = FreeFile
Open FileName For Output As #hFile
Print #hFile, Text;
Close #hFile
Hell:
WriteFile = Not CBool(Err.Number)
End Function
 
Back
Top