Importing information from Flat File into Excel Spreadsheets
Hi,
I will like to know whether it is possible to read the characters in a flat file and then
use it to populate the excel file 1 by 1.
For example
Flat file information
gghhhhhhh aaabbbbbbbbbb cccccc
cciiiiiii ccccccdddddddddd 323333
first line first 2 characters gg go to the cell a1
first line next 7 characters go to the cell b1
first line next 3 characters aaa goes to cell c1
first line next 10 characters bbbbbbbbbb goes to d1
first line last 6 characers cccccc goes to e1
second line first 2 characters cc goes to a2
second line next 7 characters goes to b2
second line next 6 characters goes to c2
second line next character dddddddddd goes to d2
second line next characters 323333 goes to e2
Note that the flat file contains no delimiters.
Regards
Peck Chun Hua
Peck Chun Hua,
Based on your post, you would like to read the characters in a flat file and populated to Excel. I would like to provide you the suggestions as follows:
1. Please try to use String.Split Method (String[], StringSplitOptions) to make the delimiters and store the splitted sub string in the string array. The following code snippet shows you the way:
Code Block
' This example demonstrates the String() methods that use
' the StringSplitOptions enumeration.
Imports System
Class Sample
Public Shared Sub Main()
Dim s1 As String = ",ONE,,TWO,,,THREE,,"
Dim s2 As String = "[stop]" & _
"ONE[stop][stop]" & _
"TWO[stop][stop][stop]" & _
"THREE[stop][stop]"
Dim charSeparators() As Char = {","c}
Dim stringSeparators() As String = {"[stop]"}
Dim result() As String
'
' Split a string delimited by characters.
'
Console.WriteLine("1) Split a string delimited by characters:" & vbCrLf)
Console.ReadLine()
End Sub 'Main
' Display the array of separated strings.
Public Shared Sub Show(ByVal entries() As String)
Console.WriteLine("The return value contains these {0} elements:", entries.Length)
Dim entry As String
For Each entry In entries
Console.Write("<{0}>", entry)
Next entry
Console.Write(vbCrLf & vbCrLf)
End Sub 'Show
End Class 'Sample
2. Then you can try to create the Excel and import the string in each of the cells according to your demand. The following KB article can help you on the issue:
How to automate Excel from Visual Basic .NET to fill or to obtain data in a range by using arrays
Hope that can help you.
Peck Chun Hua wrote: