CODEWITHSUNDEEP
vbacsvexceltext-files
Rob
Rob

Reputation: 361

Change position on multiple lines in a textfile

I got this CSV file with 18000+ rows and 7 columns. When I save it to .txt from Excel and open in notepad it shows like this:

10012   CITY    10012   CITY    RIKSDAGEN   CITY lokal  10

But I want to change it so lets say: the first CITY starts at position 6 (right after the 2 in 10012) and the second 10012 on a fixed position (lets say 26) etc.

I figured I need to swich the 'tab' spaces with regular spaces but with 18000+ rows.. yea. I don't know if this could be done in vba or if I a program?

EDIT: The positions I want is 6,26,31,51,81

10012CITY   10012CITY   RIKSDAGEN   CITY lokal  10
1    6      26   31     51          81

Upvotes: 0

Views: 137

Answers (2)

Jüri Ruut
Jüri Ruut

Reputation: 2530

One possibility is to prepare data in Excel itself:

A2:H2: String positions (1;6;26..)
A3:H3: Your data

I3: =A3&REPT(" ";B$2-A$2-LEN(A3))
The formula can be copied to the right and down.

The result will be cells with added spaces, which can be concatenated into a single cell (=I3&J3...&P3) with spaces in needed locations . The contents of the concatenated cell(s) can be copy-pasted directly to Notepad.

Example spreadsheet: http://www.bumpclub.ee/~jyri_r/Excel/Getting_fixed_length_csv_file.xls

Upvotes: 1

Ram Rajamony
Ram Rajamony

Reputation: 1723

One thing thats not clear is if your fields are all known to not exceed certain lengths. For instance, is the first field (10012) always 5 characters or less in length? Assuming that it is, the easiest way to do this would be by first saving the file as a CSV file. Then you can use awk to format it as you desire. Lets call the saved file foo.csv. Then, you would execute:

gawk '{printf "%Ms%Ps%Qs%Rs%Ts%Us%Vs%Ws\n", $1, $2, $3, $4, $5, $6, $7, $8}' foo.csv

In the above command, you need to substitute appropriate numbers for M,P,Q,R,T,U,V,W. Since (if?) the first field is known to not exceed 5 characters, you would set M to be 5. Since "CITY" has four characters and you want the second "10012" to start at position 20, you need "CITY" to take up 15 spaces and also be left-justified in this 15-space field. Therefore, you would set P to be -15. The "-" in the "-15" ensures that "CITY" starts at the left of that field. Proceed until you're done with all of the fields.

It would probably be easier to get the field numbers right if you write it down on a piece of paper that has columns drawn.

EDIT: awk '{printf "%-5s%-20s%-5s%-20s%-30s%s %s %s\n", $1, $2, $3, $4, $5, $6, $7, $8}' foo.csv

You haven't specified the placement of the last few fields, but the above should give you enough information to fix that.

Upvotes: 0

Related Questions