Fixed width import

[LeAnn]

Hi, I have a challenging project that I need some assistance with. I'm using
Access 2003. I need to import a fixed width text file (example below) and
re-export it and make it match the same format. In the original file,
CONTRACT# is always the same. The reason I need to do is to match the unit
numbers to the appropriate client that I track in my database and export unit
numbers into separate client export files. I’m not sure how to begin with
the import – we can deal with the export later. Since each line has different
column widths, I’d expect to have to read each line, evaluate what type of
line it is and import them into different tables. Do I want the Line
identifiers imports? The spaces are considered separate columns – I don’t
know if that is typical or not but not sure how to handle those. I
appreciate any assistance. LeAnn


FH:CONTRACT# ABC 03/08/07:00:25:42 F N CLIENT NAME

BH:BATCHNUMBER
UI:UNITNUMBER1 UNITNUMBER1
TR:TEST1 1 73 73
TR:TEST2 1 NR NR
TR:TEST3 1 NT NT
TR:TEST4 1 NR NR
TR:TEST5 1 NR NR
TR:TEST6 1 R R
TR:TEST7 1 NR NR
UI:UNITNUMBER2 UNITNUMBER2
TR:TEST1 1 06 06
TR:TEST2 1 NR NR
TR:TEST3 1 NR NR
TR:TEST4 1 R R
TR:TEST5 1 NR NR
TR:TEST6 1 NR NR
TR:TEST7 1 NR NR
UI:UNITNUMBER3 UNITNUMBER3
TR:TEST1 1 51 51
TR:TEST2 1 NR NR
TR:TEST3 1 R R
TR:TEST4 1 NR NR
TR:TEST5 1 NR NR
TR:TEST6 1 NR NR
TR:TEST7 1 NR NR
BT:000026
FT:000027

 

[John Nurick]

Hi LeAnn,

If you're short of time but have a budget, consider using specialist
software such as TextPipe from DataMystic or Monarch from Datawatch.
These are designed for this sort of task; I'm sure Monarch could
handle it, less sure about whether TextPipe is up to the job, though
it's been updated since I last looked at it.

Otherwise, it's a matter of writing code. The general idea would be:

Initialise:
'open the text file
Dim InFile As Long
InFile = FreeFile()
Open "D:\Folder\File.txt" For Input As InFile

'open a recordset into your table

Do Until EoF(InFile)
'Read a line from the file into a string variable.
Line Input #InFile, TheLine

'Look at the first two characters to see what sort of a line it is.

Empty line: do nothing.
FH: split the line into its components and store the ones
you need in variables.
BH: store the batch number.
UK: store the unit number

TR: split the line into its components and store them in variables.
Append a new record to the recordset.
Assign the values of the various variables (contract#, batch
number, test number, test results) to the corresponding
fields in the recordset.
Update the new record

BT: ??
FT: ??

You'll see that the contract number and batch number variables always
contain the values read from the beginning of the file, while the unit
number gets updated with each new UI line and the test informatoin
with each new TR line.

Good luck: and post back here if you need more.

 

[LeAnn]

Thanks for your quick response, John. Funny you should mention Monarch - my
co-worker is a whiz with it. I'll check with him, however I'm fairly
comfortable coding it with a little help.

My biggest question at this point is - how to split/parse each line? Should
I just use Left and Mid functions?

 

[Dale Fye]

Leann,

I would strip off the first two characters, as John mentions, to determine
the type of line, then use Mid to get everything to the right of the colon.
Then, if the format is always the same, you could use the split function to
parse the text, with a space as the text delimeter. Then, just set your
variables to the appropriate elements of the Array that is developed by using
split.

Dale

 

[LeAnn]

Thanks Dale. I ended up using Mid function to parse out each field into my
variables. Now I have a strange issue. If you notice the time stamp in the
header, it has an extra colon between the date and time. Since I don't care
whether it is a date or text I created a text field to import it into. My
variable is a text variable and populates ok but when I try to insert it into
my table I get a Syntax error. If I remove the time stamp field from the
statement, it inserts fine. Here's my insert statement. Why do you think
I'm getting an error on this?

strSQL = "INSERT INTO tblBHeader(CID, CCode, TimeStamp, RType, OD,
ImportTime)" _
& " Values('" & strCID & "','" & strCCode & "','" & strTimeStamp & "','"
& strRType & "','" _
& strOD & "',#" & dteNow & "#)"

 

[John Nurick]

Hi LeAnn,

I can't see how the extra colon can be an problem if you're importing
the timestamp value into a text field. But if there's a comma or
apostrophe (or maybe a quote) in one of the timestamp values it will
screw up the syntax of the SQL statement.

Do you get the syntax error on the first record you try to insert or
part way through? One way to check would be to add something like this
immediately before the line first line of your snippet below:

Debug.Print strCID, strCCode, strTimeStamp
strSQL = "INSERT INTO...

If you look at the Immediate pane after the procedure halts with the
syntax error you may spot the problem.

 

[LeAnn]

Hi John,

I did try that and even pasted the resulting statement into a new query
window which generated the same error. The syntax looks as expected for a
text value. What I ended up doing was parsing the date and time into 2
different fields. I now have the import complete. Thanks for your input.
I might need more when I get ready to do the re-extract.

 

[LeAnn]

Hi John,

Not sure if this post will be noticed but I have one question related to my
fixed width export procedure. Everything works fine for the most part,
however I need to export each field as the orginal width. I used the
following statement which works for one field but not another:

Dim strUnit as String * 15,
Dim strClientName as String * 40

The unit is consistently exporting with 15 characters (adding spaces if
value is less) but the Client name isn't adding the extra spaces at the end.

I'll post this as a new post if I don't get a response in the near future.
Thanks

 

[LeAnn]

Let me amend this. debug.print len(strClientName) shows that strClientName
is 40 characters but the extra spaces are not in the output file. Any ideas?

 

[John Nurick]

Hi LeAnn,

I don't know what's happening here, but I don't have much experience
with fixed length strings in VB/A. Normally I'd use ordinary string
variables and explicitly pad them to length, e.g.

Dim S As String

S = "Hello"
'10-character field
S = Left(S & Space(10), 10)
'or
S = S & Space(10 - Len(S))
'or some such.

 

[LeAnn]

Ok I feel dumb. I was using my recordset field instead of my variable. I
like your method - works great. Thanks