Alter field lengths in delimited txt file

[chris-uk-lad]

Hi all,

I have a delimited text file that i need to make some alterations to so my plan is to import / alter / export.

The way i want to edit is by changing the lengths of the columns. So for the second column i want to truncate start 22 (length 45) to 26 characters by removing start 44 for a length of 21.

Column value example:

08765653452WK433424_________________
(__ representing spaces in that value that i want to remove). I know theres the find / replace method in Access but i dont want to remove all spaces, just restrict the cell value.

Not sure whether to use VBA or a Query on this one, or the method behind each :x. Many Thanks for any assistance.

 

[MrQ]

You can do this with an update query. Have a look for left, mid and right in the help.

Q

 

[chris-uk-lad]

Hi, not able to do that, my mistake though, its actually a Fixed Width file, so some of the fields are mushed together without a space making it difficult to export into access . I do know the length of each field though so need to somehow import in based on these lengths, then change to new lengths, in esence, whether query or macro.

 

[namliam]

Why would you be looking to alter a file's layout?? That sounds like there is a design issue someplace...

I think your best bet would be to do this in VBA only!
The general quick offhand untested idea would be:

    Dim strLine As String
    Dim manLine As String
    Open "C:\SomeFile.txt" For Input As #1
    Open "C:\SomeOtherFile.txt" For Output As #2
    Line Input #1, strLine
    Do While Not EOF(1)
        ' Manipulate line
        Write #2, manLine
        Line Input #1, strLine
    Loop
    Close #1
    Close #2

Good luck

 

[chris-uk-lad]

your right it is a design issue, its an old table export that doesnt fit with my new table but i need the data.

As for the code, it doesnt actually write anything, but i see what you mean however it would just write the exact same infomation would it not?

Say the first part of the line was 2 length, second was 8, third was 5, is there a way using the above method to record first length, write as new length in new txt doc, so for example "Jim" could be "Jim " and repeat for each line?

your help appreciated as always

 

[namliam]

The code I provided was only to show the methodology, you have to fill in the blanks.

' Manipulate Line
This would contain something like:
manLine = left(strLine,3) & mid(strLine,5,6) & mid(strline,20,99)

To skip the 4th character and the 11th thru 20 characters...
You can make it much more maintainable like:
manLine = ""
manLine = manline & left(strLine,3)
manLine = manline & mid(strLine,5,6)
manLine = manline & mid(strline,20,99)

And offcourse way more complex with trims finds/replace right/left instr whatever you need to 'chop up' your text file.

Tho if your ultimate goal is to import this file, why not simply import it? Sending it directly into a table instead of back out to a file?

 

[chris-uk-lad]

i cant import the fixed width file as i cant break it into seperate fields within the import tool as the values sometimes overlap / are different lengths. That or import everything into a single column .

That and i also see the different lengths on each line may affect the code you offered as again some values are shown without spaces between each, so "mushed"

 

[namliam]

There MUST be some fixed stuff going on that you can put into code, VBA is EXTREEMLY plyable and moldable for importing files.
Just a matter of you using your brain putting together a bunch of "if then else"/select case etc. to filter out what to do when and stripping your information out.

Sure it aint easy... but doable. Unfortunatly no one can do it but you

So, Good luck, as it looks like you will need it.

 

[chris-uk-lad]

Think i might be onto something by bypassing the import.

where you have:
manLine = Left(strLine, 3) & Mid(strLine, 5, 6) & Mid(strLine, 20, 99)

i assume (strLine, 5, 6) is the start and finish of that value. So if i used..

manLine = Mid(strLine, 23, 28)

Would that truncate start 23 (length 50) to 28 characters by removing start 51 for a length of 22? If so will solve my problem (am on my friends comp atm so theorising before test lol)

 

[chris-uk-lad]

well my theory seems accurate, it does define the length of the field for writing , only annoying aspect is that the entire line needs to be mapped out instead of just specifying one value (start position to end position) otherwise it only writes the one value.

Unless you know of a way to do this would save time.

 

[namliam]

Yes you are right...

mid(strLine,23,28) will take 28 characters from position 23.
Offcourse if you want the first 50 characters then skip 22 and the rest of the line, this would be:
Left(strLine,50) & mid(strLine,72,999)
Or something simular...

A lot is possible just try to be flexible and creative

 

[chris-uk-lad]

Kinda have it working though some issues..

        If start = "DJ" Then
            manLine = Left(strLine, 23) & Mid(strLine, 23, 51) & Mid(strLine, 73, 101) & Mid(strLine, 123, 151) & Mid(strLine, 173, 201) & Mid(strLine, 202)
            Write #2, manLine
        Else
            Write #2, strLine
        End If
    Line Input #1, strLine

This is working however each line starts with "" and adds several instances of "" when comparing with old file, not sure why.

Also, the last line is repeated on the next line, and Mid(strLine, 173, 201) seems to be repeated at the end.

Any thoughts?

Really ahppy with the help your giving me so far

 

[namliam]

for the " try replacing Write with Print, one of the two adds " and I allways forget which one does that.... and I hate it! Two different commands to do the same thing a little differently

I have no clue why lines would be (partialy) duplicated, also be carefull about putting to much on one line... to many Mid() & Mid() on one line makes it hard to see what goes on.

Also this
Left(strLine, 23) & Mid(strLine, 23, 51)
Just takes the first 51 characters doesnt it?? Why not take left(strLine ,51) ???

 

[chris-uk-lad]

just to add, is there anyway to insert a blank in one of the mid points? instead or just adjusting the length

 

[namliam]

What do you mean??
Inserting one space is as simple as & " " &
if you want to insert multiple spaces maybe: string(5,"0") (Offcourse the 0 is there for the example, can be replaced by any character.

 

[chris-uk-lad]

More troubles im just gonna lay my cards down.

Option Compare Database
Option Explicit
Public Function Textfile()
 
    Dim strLine As String
    Dim manLine As String
    Dim start As String
    Dim smallStart As String
    Open "C:\Uni\Flash1\Old.txt" For Input As #1
    Open "C:\Uni\Flash1\New.txt" For Output As #2
    Line Input #1, strLine
    Do While Not EOF(1)
 
        start = Left(strLine, 2)
 
        If start = "DJ" Then
            manLine = Left(strLine, 22) & Mid(strLine, 23, 51) & Mid(strLine, 73, 101) & Mid(strLine, 123, 151) & Mid(strLine, 173, 201)
                    'Truncate start 23 (length 50) to 28 characters by removing start 51 for a length of 22
                    'Truncate start 73 (length 50) to 28 characters by removing start 101 for a length of 22
                    'Truncate start 123 (length 50) to 28 characters by removing start 151 for a length of 22
                    'Truncate start 173 (length 50) to 28 characters by removing start 201 for a length of 22
            Print #2, manLine
        Else
            Print #2, strLine
        End If
    Line Input #1, strLine
    Loop
    Close #1
    Close #2
End Function

The truncate comments are what ive been given that the code needs to achieve with the text file (of which i assume the values im using are correct).
I am attatching 3 files, 'Old' which is the original record, 'New' which is the result from this code (incorrect due to repeating values and length differences) and 'Wish' which is what i want it to achieve. I hope you can help me spot the problem, tearing my hair out a lil

 

[namliam]

*UGH* wait I see the problem now....
Mid works like this:
Mid(Field,Start,#ofCharacters)

So what you are saying with this
Mid(strLine, 23, 51)
is take from position 23 on the next 51 characters, where you want the next 28.... up to character #51...
Mid(strLine, 23, 28)

Get what I mean?

 

[chris-uk-lad]

yup works perfect!! my mis-understanding of the values ^^; i cna start to process this along all my fields.

Other than the truncate i also have what i mentioned before about blank

Insert “ ” start 115 (length 2)

Not sure how to include this in the mid or what command to use out of the mid.

 

[namliam]

A quick example:
Left(strLine, 22) & "namliam's space" & Mid(strLine, 23, 51)

Will insert this on every line... replace anything for "namliam's space" i.e. " " for one space or ";" for a semicolumn
or "namliam is a fruit cake" for all I care....

 

[chris-uk-lad]

hmmm fruitcake lol

everythings great except one tiny lil thing

the last line in the txt doc:

T00476900001584475 00000020928 00063615669 00000000000 00002398321

is not written, just ignored.