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...

 

[earthandfire]

Try the following, call it with:

ConvertFile("c:\TheFile.ext","converted")

It should then create a properly line terminated file called "c:\TheFile.ext.converted"

Public Sub ConvertFile(source as string, newext as string)
Dim InFile As long
Dim OutFile As long
Dim s as string
  
  InFile = FreeFile
  Open source For Input Access Read As InFile
  OutFile = FreeFile
  Open source & "." & newext For Output Access Write As OutFile
  Do Until EOF(InFile)
    Line Input #InFile, s
    Print #OutFile, s
  Loop
  Close InFile
  Close OutFile

End Sub

Hope this helps

 

[earthandfire]

By the way, I know the above code works with Unix generated files because I've used it several times.

 

[scottian]

earthandfire,

I tried you method, it did read and write the file, however it didnt strip out the funny characters. or create new carriage returns.

Roy

I got a variable not defined in the line
strFile = txt.readline
pointing at the 'txt.' piece.

please dont give up on me.....

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

 

[RoyVidar]

Same typo as the first time.

Roy-Vidar

 

[earthandfire]

It appears that we don't know for sure what the spurious character is.

If the file is large split it using notepad, so that you have a file with just a handfull of lines.

To avoid file and path spacing issues copy this file to the root of drive C. and call Weird.txt

Start a Cmd session and at the prompt type:
debug c:\weird.txt

At the debug prompt (which is just a dash (-)) type:

d100 and press enter

The screen will be split into two panes, on left you have a block of hexadecimal values and on the right a textual display of the file (with a dot (.) for each unprintable character)

In the hex display values 0D and 0A represent CRLF. What you need to do is locate the offending character(s) and post their hex values. We should then be able to determine how best to rewrite one of the replace routines to strip them out and to ensure that lines are terminated correctly.

By the way, to view more of the file in debug, just type d <enter> at the prompt and to quit debug just type q <enter> at the prompt.

 

[HenryAnthony]

Not sure if this will help but, I had a similar problem importing a text file. In the Access error table I noticed that every 60th row could not be parsed. Turned out that the person extracting the text (from a program called Amisys) had a checkbox checked that put in a page break - that little square. I could open the file in Excel and see them. All they needed to do was uncheck the box and all was well.

Best regards,

Henry