Importing Long Text String

  • Thread starter Thread starter Chris A.
  • Start date Start date
C

Chris A.

I'm trying to import a large .txt file and the data is
fixed width. The problem is, there is no carriage return
at the end of each data row which is 182 character long.
It's all just one long string and it's huge.

How can I import this data?? Is there a way to force the
import to start a new import row in the table after a
certain length (182 characters)? I also want to skip the
first 182 characters which is the header information.

Thanks.

-Chris
 
You could use VBA code to open the text file and read each character, one at
a time, keeping a counter running for a 182-character loop. Use subloops
within that are based on the fixed width values and concatenate those
characters into a string that represents the value, then write it to a table
as you go. Not overly difficult, but a bit tedious.

To get you started, somthing like this, perhaps (assumes two fields: first
is 100 characters long; second is 82 characters long) -- (also assumes that
both fields in table are formatted as text; if this isn't true, you likely
will need to cast the string value as the correct data format as part of
writing to the table):

'***Not tested***
Dim strChr As String, strValue1 As String, strValue2 As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intRecord As Integer, intField1 As Integer, intField2 As Integer
Dim blnSkip As Boolean

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)

Open "C:\MyFolder\TextFileName.txt" As #1 For Input

blnSkip = True

Do While EOF(1) = False
For intRecord = 1 To 182
strValue1 = ""
strValue2 = ""
For intField1 = 1 To 100
Input #1, strChr
strValue1 = strValue1 & strChr
next intField1
For intField2 = 1 To 82
Input #1, strChr
strValue2 = strValue2 & strChr
next intField1
If blnSkip = False Then
' first record already skipped, so append this record
rst.AddNew
rst.Fields(0).Value = strValue1
' as example, if first field in table is a double-precision number,
' rst.Fields(0).Value = CDbl(strValue1)
rst.Fields(1).Value = strValue2
rst.Update
Else
' skip the first record (headers)
blnSkip = False
End If
Next intRecord
 
Oops...forgot to close the stuff at the end...here is a corrected version:

'***Not tested***
Dim strChr As String, strValue1 As String, strValue2 As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intRecord As Integer, intField1 As Integer, intField2 As Integer
Dim blnSkip As Boolean

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)

Open "C:\MyFolder\TextFileName.txt" As #1 For Input

blnSkip = True

Do While EOF(1) = False
For intRecord = 1 To 182
strValue1 = ""
strValue2 = ""
For intField1 = 1 To 100
Input #1, strChr
strValue1 = strValue1 & strChr
next intField1
For intField2 = 1 To 82
Input #1, strChr
strValue2 = strValue2 & strChr
next intField1
If blnSkip = False Then
' first record already skipped, so append this record
rst.AddNew
rst.Fields(0).Value = strValue1
' as example, if first field in table is a double-precision number,
' rst.Fields(0).Value = CDbl(strValue1)
rst.Fields(1).Value = strValue2
rst.Update
Else
' skip the first record (headers)
blnSkip = False
End If
Next intRecord
Close #1
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Hi Chris,

Alternatively, you can use a VBScript script like this to read the input
file record by record and write each record as a line in a new file:

Dim fso 'FileSystemObject
Dim fIn 'input file
Dim fOut 'output file
Dim strLine

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso.OpenTextFile("C:\temp\datafile", 1)
Set fOut = fso.CreateTextFile("C:\temp\textfile.txt", True)

Do Until fIn.AtEndOfStream
strLine = fIn.Read(182)
fOut.WriteLine strLine
Loop

fIn.Close
fOut.Close

The new file can then be imported as a fixed-width file using Access's
text import wizard.
 
Back
Top