Strange Character that should be a return

[scottian]

Im trying to import a text file into Acces 97. when i open the file in notepad the file looks fine cintains a numer of records, however when i try to import into the database the wizard only shows one line of text with a black square where the a new row begins, any ideas how to solve.

I have quite a few files to import so i dont want to have open each one and "save as"


"My God! It's full of stars...

 

[RoyVidar]

I think the text file either contains a carriage return or line feed, but not the combination of them which is necessary for it to be displayed in a form control, and perhaps also for it to be recognized by the wizard. Would it by any chance originate from Unix/Mac?

Here's a short snippet that might help if my assumptions are correct:

[tt]dim fs as object
dim txtIn as object
dim txtOut as object
dim strFile as string
set fs = createobject("scripting.filesystemobject")
set txtIn = fs.opentextfile("c:\yourfile.txt",1) ' for reading
set txtOut = fs.createtextfile("c:\newfile.txt",true) ' overwrite
strfile = txt.readall
strfile = replace(strfile,vbcr, vbcrlf)
' or alternatively
' strfile = replace(strfile,vblf, vbcrlf)
txtOut.write strfile
txtIn.close
txtOut.close
set txtIn=nothing
set txtOut=nothing
set fs=nothing[/tt]

Couple of notes
- the Replace function doesn't exist in a97, use RickSpr's replacement functions from faq705-4342
- if memory serves me right, I think the scripting libraries aren't usually distributed with Office 97, but having IE 5.0 or higher would normally have installed it
- if you wan't to process the file trhough code, perhaps reading line by line (strLine = txtIn.ReadLine would give you one line, in the loop, just ensure you've got a criterion like this: do while not txtIn.AtEndOfStream)
- as usual, typed not tested

Roy-Vidar

 

[Craig0201]

Roy's solution is likely to be slow. I would do similar but in a query after import.

Craig

 

[scottian]

thats the problem, whilst importing i get import error unparsable record.

"My God! It's full of stars...

 

[earthandfire]

I had a similar problem a while ago which I solved using a RichText control in Delphi. I simply read the file into the control and wrote it out from the control, it changed the linefeed (which had been used as the row terminator) to carriage return + linefeed. Presumably the MS RichText control would enable you to do the same thing.

Worth a try?

 

[scottian]

Roy,

Ive tried this code and get the error-

Vriable noy defined. which it then highlights "txt" in the line

strFile = txt.readall

==============================================
Function RemoveUnkownCharacter()
Dim fs As Object
Dim txtIn As Object
Dim txtOut As Object

Dim strFile As String
Set fs = CreateObject("scripting.filesystemobject")
Set txtIn = fs.opentextfile("C:\Pn100011.csv", 1) ' for reading
Set txtOut = fs.createtextfile("C:\Pn100011.txt", True) ' overwrite
strFile = txt.readall
'MsgBox txtIn
'strFile = aCmdReplace(strFile, vbCr, vbCrLf)
' or alternatively
strFile = replace(strFile, vbLf, vbCrLf)
txtOut.write strFile
txtIn.Close
txtOut.Close
Set txtIn = Nothing
Set txtOut = Nothing
Set fs = Nothing
End Function
==============================================

sorry to badger about this, but im completely lost

"My God! It's full of stars...

 

[RoyVidar]

Such happens when typing -> txtIn

Lets see if that's then only typo;-)

Roy-Vidar

 

[HarleyQuinn]

I imagine that's a typo from Roy and is meant to read txtIn.ReadAll.

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Oops, sorry Roy. Your post wasn't there when I replyed. Honest

Harleyquinn

---------------------------------
For tsunami relief donations

 

[scottian]

Okay, we have moved on a little, the change has fixed the original error. However i now get the error
Compile error:
expected array

also i changed
Replace in the line
strFile = replace(strFile, vbCr, vbCrLf)

with
strFile = acCmdReplace(strFile, vbCr, vbCrLf)

really appreciate your efforts

"My God! It's full of stars...

 

[HarleyQuinn]

If you leave it as Replace() it won't be expecting an array...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[scottian]

HarleyQuin,

I get sub or function not defined. I checked the object browser which only returned the acCmdReplace

"My God! It's full of stars...

 

[RoyVidar]

See the first note in my first reply.

Roy-Vidar

 

[scottian]

ive checked your first note and that was what i changed in Roys code, but i still the error still get the error. When i check the debug window it states that variable not defined.

"My God! It's full of stars...

 

[RoyVidar]

So you have copied RickSpr's code into a separate module (or similar code to be used in stead of the Replace function that doesn't exist in a97), compiled, and then you get a variable not defined in some code line - which variable is not defined, which line?

Roy-Vidar

 

[scottian]

Roy,

Im still playing with this, but not getting far.
as you can from the code below iv isolated the replace function to try to see if thats the only error. however i now get the error

run time error '62'
input past end of file


'=================================================
Function RemoveUnkownCharacter()
Dim fs As Object
Dim txtIn As Object
Dim txtOut As Object

Dim strFile As String
Set fs = CreateObject("scripting.filesystemobject")
Set txtIn = fs.opentextfile("\\PEMS\Pn100282.txt", 1) ' for reading
Set txtOut = fs.createtextfile("\\PEMS\Pn100282.csv", True) ' overwrite
strFile = txtIn.ReadAll
'strFile = Replace(strFile, vbLf, vbCrLf)
txtOut.write strFile
txtIn.Close
txtOut.Close
Set txtIn = Nothing
Set txtOut = Nothing
Set fs = Nothing
End Function
'=================================================

"My God! It's full of stars...

 

[earthandfire]

You could try using standard VBA io commands. Input Line # should read up to CR, LF or CRLF. Print # automatically appends CRLF.

 

[scottian]

can you elaborate on this?
im not a master of VBA, or an apprentice for that matter

"My God! It's full of stars...

 

[RoyVidar]

Then try line by line, would probably not need the replace function, as reading a line through filesystemobject methods would normally give one line regardless of whether the end of line mark is vblf, vbcr or vbcrlf. Try with or without:

[tt]do while not txtIn.atendofstream
strFile = txt.readline
strFile = replace(strFile, vbLf, "") ' not sure it's needed
' strFile = replace(strFile, vbCr, "") ' not sure it's needed
txtOut.writeline strFile
loop[/tt]

Roy-Vidar

 

[PHV]

You may try this:
If txtIn.AtEndOfStream Then
strFile = ""
Else
strFile = txtIn.ReadAll
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244