text file and save to excel?

pakerly

New member
Joined
Nov 19, 2008
Messages
1
Programming Experience
Beginner
Hello,
I am really new to vb.net and programming in general. I am wondering if someone can show me how to do this.

I have a text file called for example... mytestfile.txt
it looks like this:

NUMBER....NAME.............................SECOND NAME
======= ==================== ====================
1250204 Food mart Food Mart 2

so the first field has 7 characters, than a space and the second field has 20, than a space and the third has 20.

I would like to get this type of file into excel into a CSV or something where it goes into A1, B1, C1...A2, B2, C2 etc.

So I am thinking this would have to read through the file, delete the first two lines, the headings..and than place a TAB or a COMA at line 8 and than another at line 21.

Go through line by line and do this...and than save it out as excel .xls. And since it has tabs in the right places it will go into the write cells in excel.

How would this be done?
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Using File.ReadAllLines(<file path>) you can read a file intoa string array of all the lines

Using myArray(<numerical index from 0>) = Nothing you can remove an element from the array (you must check if it is nothing later, and skip it)

Using a For Each line As String in <the array of lines> loop will retrieve every line in the array per loop

Using If line Is Nothing THen Continue will make the loop progress to the next entry in the array if the current one is not in memory

Using a Dim sb as New StringBuilder(<a string>) and sb.Chars(<numerical index>) = "<a char>"c allows you to edit characters at certain positions, sb.ToString() turns the stringbuilder contents back into a string (strings are not editable)

Using line.Substring() you can chop bits out of a string, and build them into another stringbuilder

Using File.WritellText(<a path>, <a string to write>) you can write a file to disk

You can find more info in MSDN, on all the classes I discuss here (File, String, StringBuilder)

Write some plain-english pseudocode before you start to help you understand the logic in a language you can already think in, then translate it into VB:

VB.NET:
'read the file into an array

'loop over the array looking at each line in turn

'make an editable version of the string in a builder

'insert or alter characters at certain positions so that they are commas splitting the fields

'write the edited line into another string builder

'loop back upntil all lines are done

'write the edited lines to disk


Good luck :) (and ask if you get stuck)
 
Last edited:
Top Bottom