Help needed to clarify a point on importing text data files...

[Robert McGregor]

Hi there,

I'm trying to import data from text files that have comma
delimitation.

No real problem with the field separators, it is the line ending that
is causing the problem.

The files I have only have a line feed at the end of each line (ASCII
= x0A). Access does not recognise this and so it assumes that I only
have one line of text!!!

What can I do to get round this... I would rather not have to eit each
file prior to loading to the database...

Thanks,

Rob.

[Brendan Reynolds]

Here's my 'Lf2CrLf' routine. It's application-specific and will need some
modifications, but the basic principle is just to read in one character at a
time, change any vbLf characters to vbCrLf, and write it out again to a new
file. The original code killed the old file when it was finished (after
first creating a backup). To protect anyone who might cut and paste the code
without realising that it was going to delete the file, I've commented out
that line.

Sub Lf2CrLf(strInputFile As String, strOutputFile As String)

Const conProcName As String * 7 = "LF2CRLF"

On Error GoTo Err_Routine

Dim strFileName As String
Dim strBackupFile As String
Dim intInputFile As Integer
Dim intOutPutFile As Integer
Dim intBackupFile As Integer
Dim strChar As String

If Right(strInputFile, 10) = "abcde*.txt" Then
strFileName = Dir(strInputFile)
strInputFile = Left(strInputFile, Len(strInputFile) - 10) &
strFileName
End If

strBackupFile = Left(strOutputFile, Len(strOutputFile) - 4)
strBackupFile = strBackupFile & Format(Now(), "yyyymmddhhnnss") & ".bak"

intInputFile = FreeFile
Open strInputFile For Input As intInputFile

intOutPutFile = FreeFile
Open strOutputFile For Output As intOutPutFile

intBackupFile = FreeFile
Open strBackupFile For Output As intBackupFile

Do Until EOF(intInputFile)
strChar = Input(1, intInputFile)
If strChar = vbLf Then
strChar = vbCrLf
End If
Print #intOutPutFile, strChar;
Print #intBackupFile, strChar;
Loop
Close

'This line deletes the old file. Think carefully before uncommenting it!
'Kill strInputFile

Exit_Routine:
On Error Resume Next
Close
Exit Sub

Err_Routine:
Select Case Err
Case 53
'File not found.
Resume Exit_Routine
Case 76
'Path not found.
Resume Exit_Routine
Case Else
Beep
MsgBox "Error Number: " & Err.Number & "@" _
& Err.Description & "@" _
& "Project: " & Err.Source & ". Procedure: " & conProcName, _
vbOKOnly + vbInformation, _
"Unexpected Error"
End Select
Resume Exit_Routine

End Sub

--
Brendan Reynolds
bren...@indigo.ie

Robert McGregor <rmcg...@totalise.co.uk> wrote in message
news:922949a9.01072...@posting.google.com...

[Dylan]

You could process the files first.

sub ProcessFile(fn as string)
dim fnum as int
dim strByt as string

fnum=freefile
strByt=space(1)
open fn for binary as fnum
open "proc_" & fn for binary as fnum+1
do while not(eof(fnum))
get fnum, strByt
if strByt=chr(10) then
put fnum+1,chr(13)
end if
put fnum+1,strByt
loop
close fnum
close fnum+1
end sub

This is a poor example made up on the fly to process a text file and replace
chr(10) with chr(13)&chr(10), but it would need a bit of finessing.

--
Dylan Tusler
(Australian MCSD Working on a P266 with a 10" monitor)

"Robert McGregor" <rmcg...@totalise.co.uk> wrote in message
news:922949a9.01072...@posting.google.com...

[Bunk]

Why do Access developers always bear the weight of the world on their
shoulders? See if you can get text files that don't have a line feed code,
or get the data in Excel (or even an Access table).

[Brendan Reynolds]

If you're dealing with a responsive source, yes. When you're dealing with
corporate IS, on the other hand, writing your own conversion routine is
usually faster and less likely to give you a headache.
--
Brendan Reynolds
bren...@indigo.ie
http://www11.ewebcity.com/brenreyn

"Bunk" <NB2...@onebox.com> wrote in message
news:tlrak7j...@news.supernews.com...

[Robert McGregor]

Thanks all for your responses.

Just so you are all aware, the source is from my Internet Banking
company and so I have little chance of getting them to change the
output format but I will write to them and ask...

The code examples are great and I've had a really helpful response
from everyone so just wanted to say thanks to all who helped.

BTW: It is interesting to note that Excel will open the files and see
the end of line properly, as will Word. Surely Microsloth can;t be
stupid enough to use different mechanisms for identifying the purpose
of particular characters in different MS applications?

Just a thought... perhaps there should be a configurable import util
that allows the choice of line end character...

Ah well thanks again everyone.

Rob.

"Brendan Reynolds" <bren...@indigo.ie> wrote in message news:<L7l77.19277$Fk7.1...@news.indigo.ie>...

[Brendan Reynolds]

If Excel can read the file, Robert, then another alternative is to open the
file in Excel and then import or link to the Excel file from Access.

--
Brendan Reynolds
bren...@indigo.ie

Robert McGregor <rmcg...@totalise.co.uk> wrote in message

news:922949a9.0107...@posting.google.com...