Import a .txt file into a .xlsx file

Question

I am working on a script that converts a .txt file (without restriction) into an Excel spreadsheet. My problem arises when I need to pull out data that can be between 5-10 characters and there are several data sets on each line.

Each line can have the following number of characters in each field, and each line has five fields:

10 char 10 char 10 char 17 char 10 char 523452 D918 20120418 1FD7X2XTACEB8963820120606 523874 L9117244 20120409 3C6TDT5H0CG12130200000000 535581 G700 20120507 5GYFUD CT 00000000 

I basically need to pull 10,10,10,17,10 and put them in my own cell in a row in Excel. I can pull out the cells the way I do now, but this is based on delimiting the space, and this causes a problem when the fields do not occupy the entire amount of space, and I get an Excel sheet with empty cells in it.

Answer 1

You can use String.Substring (tag read by C# ):

 using System; using System.IO; class Test { public static void Main() { try { // Create an instance of StreamReader to read from a file. // The using statement also closes the StreamReader. using (StreamReader sr = new StreamReader("TestFile.txt")) { String line; // Read and display lines from the file until the end of // the file is reached. while ((line = sr.ReadLine()) != null) { String Chunk1 = line.Substring( 0, 10); // First 10 String Chunk2 = line.Substring(10, 10); // Second 10 String Chunk3 = line.Substring(20, 10); // Third 10 String Chunk4 = line.Substring(30, 17); // Now 17 String Chunk5 = line.Substring(47); // Remainder (correction: Chunk2 --> Chunk5) Console.WriteLine("Chunks 1: {0} 2: {1} 3: {2} 4: {3} 5: {4})", Chunk1, Chunk2, Chunk3, Chunk4, Chunk5); } Console.ReadLine(); } } catch (Exception e) { // Let the user know what went wrong. Console.WriteLine("The file could not be read:"); Console.WriteLine(e.Message); } } } 

Answer 2

No code is required if the import is from Excel (DATA, Get External Data, From Text, Fixed width):

Answer 3

You can use Mid () to get a specific part of the string. If the line is in currentLine , you can extract such fields as follows:

 Dim fields(5) fields(1) = Mid(currentLine, 1, 10) fields(2) = Mid(currentLine, 11, 10) fields(3) = Mid(currentLine, 21, 10) 

etc.