Importing from a CSV

[mtairhead]

It's funny: I thought, since you all answer my questions so often, I'd "give back to the community" by answering a few questions from others. So, when I clicked on sbayeta's "First Access App. Need Advice" (http://www.access-programmers.co.uk/forums/showthread.php?t=69171), I thought I was really going to be able to help - Piece of cake. Wow. Three paragraphs later, Mr. Clip-It and I were trying to decipher her Access-Jargon.

So...That's why I don't give back to the community.... But you can help give to me, by answering this challenge.

I have a text database. I'd call it a "tab-delimitated" or "Comma separated" but it's not, exactly. It's more of a "comma separated, return aided" database. But that's just my name for it.

I attached a sample, with five entries. As you can see, the records would be comma separated, if it wasn't for that pesky return in the middle of the word and the rest of the definition. Does anyone have any ideas on how to import? If not, any ideas on how to replace every single {enter} with a null value in MS Textpad? Wouldn't that solve the problem? Don't you wish I could? Don’t I wish I could?

Thanks

 

[ByteMyzer]

The following code will open up a file, read it into a temporary string variable, replace all single-{enter}s in the string with spaces, and all double-{enter}s with single-{enter}s, and write the modified data to a new file:

Public Sub fileConvert(inFile As String, outFile As String)

If Len(Dir(inFile, vbNormal)) = 0 Then MsgBox "File Not Found": Exit Sub

Dim lBuffer As String

Open inFile For Input Shared As #1
    Do While Not EOF(1)
        lBuffer = lBuffer & Input(1, #1)
    Loop
Close #1

lBuffer = Replace(Replace(Replace(lBuffer, vbCrLf & vbCrLf, Chr(255)), vbCrLf, " "), Chr(255), vbCrLf)

If Len(Dir(outFile, vbNormal)) > 0 Then Kill outFile

Open outFile For Output As #1
    Print #1, lBuffer;
Close #1

End Sub

' The following is included for use in Access 97, which does not have a built-in [b]Replace[/b] function:

Public Function Replace(sIn As String, sFind As String, _
      sReplace As String, Optional nStart As Long = 1, _
      Optional nCount As Long = -1) As String

    Dim nC As Long, nIndex As Integer, nPos As Integer, sOut As String
    nIndex = nStart
    sOut = sIn
    nPos = InStr(nIndex, sOut, sFind, vbBinaryCompare)
    If nPos = 0 Then GoTo EndFn:
    Do
        nC = nC + 1
        nIndex = nPos + Len(sReplace)
        sOut = Left(sOut, nPos - 1) & sReplace & _
           Mid(sOut, nPos + Len(sFind))
        If nCount <> -1 And nC >= nCount Then Exit Do
        nPos = InStr(nIndex, sOut, sFind, vbBinaryCompare)
    Loop While nPos > 0
EndFn:
    Replace = sOut
End Function

For example, to read from a file named oldfile.txt and write the modified data to a file named newfile.txt, call the sub thus:

Call fileConvert("oldfile.txt", "newfile.txt")

 

[mtairhead]

Great!

I think this will work. I began the process, and it seemed to be working, but I had to cut it short. It was taking forever. I have to run this on about 25 files, and each are 2-5 MB large, so I think I'll set them up to run this evening, and through the night.

Thanks! I'll let you know the results!

 

[ByteMyzer]

Ooh, with files THAT size, you might wish to try this modified code:

Public Sub fileConvert(inFile As String, outFile As String)

If Len(Dir(inFile, vbNormal)) = 0 Then MsgBox "File Not Found": Exit Sub

Dim lBuffer As String[b], lInput As String[/b]

Open inFile For Input Shared As #1
    Do While Not EOF(1)
        [b]Line Input #1, lInput
        lBuffer = lBuffer & lInput & vbCrLf[/b]
    Loop
Close #1

lBuffer = Replace(Replace(Replace(lBuffer, vbCrLf & vbCrLf, Chr(255)), vbCrLf, " "), Chr(255), vbCrLf)

If Len(Dir(outFile, vbNormal)) > 0 Then Kill outFile

Open outFile For Output As #1
    Print #1, lBuffer;
Close #1

End Sub

' The following is included for use in Access 97, which does not have a built-in Replace function:

Public Function Replace(sIn As String, sFind As String, _
      sReplace As String, Optional nStart As Long = 1, _
      Optional nCount As Long = -1) As String

    Dim nC As Long, nIndex As [b]Long[/b], nPos As [b]Long[/b], sOut As String
    nIndex = nStart
    sOut = sIn
    nPos = InStr(nIndex, sOut, sFind, vbBinaryCompare)
    If nPos = 0 Then GoTo EndFn:
    Do
        nC = nC + 1
        nIndex = nPos + Len(sReplace)
        sOut = Left(sOut, nPos - 1) & sReplace & _
           Mid(sOut, nPos + Len(sFind))
        If nCount <> -1 And nC >= nCount Then Exit Do
        nPos = InStr(nIndex, sOut, sFind, vbBinaryCompare)
    Loop While nPos > 0
EndFn:
    Replace = sOut
End Function

 

[mtairhead]

Aha!

I was about to come back and tell you of my failures. I put in your original code, and got an overflow error. I will implement your new code tonight, and see how my poor 1.6GHZ computer handles. It should be quite entertaining.

I don't know why, but I get pleasure out of making my computer work 14 hours a day...Or maybe I just get pleasure out of making MS Office work 14 hours a day. Especially while I'm watching tv, drinking a cold glass of lemonade.

 

[ByteMyzer]

Yep, with those filesizes, it can definitely make a difference, which is why I changed the variant types in the Replace function from Integer to Long.

I also have a modification to the fileConvert sub itself which should help even more on speed (modified code is bold-faced):

Public Sub fileConvert(inFile As String, outFile As String)

If Len(Dir(inFile, vbNormal)) = 0 Then MsgBox "File Not Found": Exit Sub

Dim lBuffer As String, lInput As String

Open inFile For Input Shared As #1
    [b]lBuffer = Input(LOF(1), #1)[/b]
Close #1

lBuffer = Replace(Replace(Replace(lBuffer, vbCrLf & vbCrLf, Chr(255)), vbCrLf, " "), Chr(255), vbCrLf)

If Len(Dir(outFile, vbNormal)) > 0 Then Kill outFile

Open outFile For Output As #1
    Print #1, lBuffer;
Close #1

End Sub

 

[mtairhead]

Great!

Well, I've been emailing, word-processing, power-pointing, and web searching all day, and your code has been working steadily through it. So far, 21 of the 24 files have been successfully converted.

As always, this forum has solved yet another problem for me. What ever did I do before I knew how to use MS Access?