Using an imperfect text file as a table (1 Viewer)

[Steven Deetz]

Hello,

I have a client who can export their membership information into a comma delimited text file. However during the conversion process the first line of the file produces an inaccurate header for the file. For example:

..."I MemberTo Activity#1 Category#1 BusinessName"

should read

..."I","MemberTo","Activity#1","Category#1","BusinessName"

Every time they create a comma delimited text file with updated membership information I have to go into the text file and add the appropriate quotes and commas for their Access application to properly read the text file.

I would like to be able to create a procedure that would delete the first line of the comma delimited text file and then replace it with an updated version of the first line. I have manually deleted the first line and then used the old DOS command Copy MemberTextHeader.txt + MemberText.txt to combine a correct header with the text; however, I am at a loss for how to delete the first line of the text file.

Any ideas would be most appreciated. Thanks in advance!

 

[Shep]

If the file is not huge you should be able to assign it's entire contents to a string variable, search within that string for the first instance of vbCrLf, use VB string functions to cut everthing Left of it, and write the string back out to the text file, inserting your modified header in the process.

 

[ByteMyzer]

For example, the following code:

Public Sub cdlConvert(hdrFile As String, datFile As String)

    Dim hdrBuf As String
    Dim datBuf As String

    'Read in header string
    Open hdrFile For Input As #1
        hdrBuf = Input(LOF(1), #1)
    Close #1

    'Read in the data file, still with the incorrect header
    Open datFile For Input As #1
        datBuf = Input(LOF(1), #1)
    Close #1

    'Strip the incorrect header and substitute the correct one
    'from the header file
    datBuf = hdrBuf & Mid(datBuf, InStr(1, datBuf, vbCrLf) + 1, 1)

    'Delete the data file
    Kill datFile

    'Rewrite the data file, with the new header in place
    Open datFile For Output As #1
        Print #1, datBuf;
    Close #1

End Sub

If you call it with something like:
Call cdlConvert("C:\MemberTextHeader.txt","C:\MemberText.txt")

It will read in the header from C:\MemberTextHeader.txt, correct the header in C:\MemberText.txt and write it back to disk.

 

[Steven Deetz]

Thanks for ideas and code! I will try them out over the next few days and see if I can get it to work.

 

[Roxtar]

don't know how comfortable you are with C++ or other languages, but you could create a small program that would read in that first line and reformat it however you want and then re-build the text file. might be more trouble than it's worth though