Low Values - import problems

thebadger

New Member
Joined
Mar 8, 2004
Messages
22
I am attempting to import a .txt file which has been exported from a mainframe dataset.

I am able to read the data and parse it correctly and in most cases I can then insert the details onto access tables.

I am having problems with some rows however - vba reports an error saying that the length of the data to be inserted is too big for the field. As I know the lengths are correct the problem is to do with the data. When I open the .txt file in notepad I can see just spaces however in textpad I can see a black line - attempts to copy/cut this item fail - reports that Null values cannot be copied.

I think the problem is that the mainframe file contains 'low value' hex 00. This for some reason is causing access to think that the item to be inserted is too big.

I was wondering can I use the replace command to change these 'characters' to spaces - if so does anyone know what I would be searching for because if I use " " then it will take forewer. The files has over 9000 rows with strings up to 10000 bytes and there are only a few examples of these 'characters'
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

thebadger

New Member
Joined
Mar 8, 2004
Messages
22
Answer

Can't seem to be able to do it with VBA. Managed to get round it by using a text editor that allowed find and replace for Hex
 

tkarpf

New Member
Joined
Nov 10, 2005
Messages
3
I'm afraid I only have part of the solution, but it should work.

What you need to do is read through your file in chunks and feed it through this function. You're probably going to want to pass vLeaveCRLF as TRUE.

I don't have an example of the code that calls this. I vaguely remember that I opened the file for input as Binary, but I can't find my sample code.

Anyway, this will clear the junk out of the pieces of file that you pass to it. Just write the resulting file out to a new file.



Function StripLowValues(ByVal sInputString As String, Optional vLeaveCRLF As Variant) As String

'Converts Low Value characters (chr(0) to chr(31)) to spaces within a string
Dim iPos As Integer
Dim icount As Integer
Dim sCharToFind

If IsMissing(vLeaveCRLF) Then
'also convert CarriageReturn and LineFeed
For icount = 0 To 31
sCharToFind = Chr(icount)
iPos = InStr(sInputString, sCharToFind)
While iPos > 0
sInputString = Left(sInputString, iPos - 1) & " " & Mid(sInputString, iPos + 1)
iPos = InStr(sInputString, sCharToFind)
Wend
Next icount
Else
'keep CarriageReturn and LineFeed characters in place
For icount = 0 To 31
'We want to keep CarriageReturn and LineFeed in place
If icount = 10 Or icount = 13 Then
icount = icount + 1
End If
sCharToFind = Chr(icount)
'This is probably not the best way of doing this, but it works.
iPos = InStr(sInputString, sCharToFind)
While iPos > 0
sInputString = Left(sInputString, iPos - 1) & " " & Mid(sInputString, iPos + 1)
iPos = InStr(sInputString, sCharToFind)
Wend
Next icount
End If

StripLowValues = sInputString

End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top