• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

How to split a single line text file to multiple lines and export to excel with vb.net 2005

I am trying to read a file on the c: drive that has 1 single line of text. I need to split the line into multiple lines and exprot them into a excel spread sheet with each line being an individual record. I would like to use vb.net 2005 to do this because the original file will contain about 20,000 records. Listed below is a sample of the text in the file. I have placed x's where confidential information should go. The single line of text has multiple spaces in it and the original file had 4 lines per record. I was able to concantanate all the lines to gether and now I don't know how to split them as a individual line and have each line as a record in excel.

In my string the lines are separated by the tab character. There is also some text at the beginning of each record that can be used as a separator as well. (MATCH TYPE)


example of string:

MATCH TYPE: N   SUB NAME: xxxxxxxxxxxxxxxxxx    SSN CODE: V SSN:  xxx-xx-xxxx  MPI #: xxxxxxxxx PART: NP FIPS: 111 TRAN STATE: 21   RET NAME: xxxxxxxxxxxxxxxxxxx    MULTI SSN:             WORKER: 015C002  CLAIM TYPE: DISABILITY         RESP DATE: 10/14/2011        ADDL NAMES:                                                                                                                         SOCIAL SECURITY           xxxxxxxxxxxxxxxxxxxxxxxxx       BOWLING GREEN KY 42104                                                                                                                                                                                           MATCH TYPE: N   SUB NAME: xxxxxxxxxx          SSN CODE: V SSN:  xxx-xx-xxxxxxx      MPI #: xxxxxxxxxxx  PART: NP FIPS: 067 TRAN STATE: 21   RET NAME: xxxxxxxxxx          MULTI SSN:             WORKER: 034C035  CLAIM TYPE: AUXILIARY          RESP DATE: 10/14/2011        ADDL NAMES:                                                                                                                         SOCIAL SECURITY           xxxxxxxxxxxxxxxx DR         LEXINGTON KY xxxxxxxxxxxxx                                                                                                                                                                                           MATCH TYPE: N   SUB NAME: xxxxxxxxxxxxxxxx        SSN CODE: V SSN:  xxx-xx-xxxx  MPI #: xxxxxxxxx PART: NP FIPS: 047 TRAN STATE: 21   RET NAME: xxxxxxxxxxxx        MULTI SSN:             WORKER:          CLAIM TYPE: DISABILITY         RESP DATE: 10/14/2011        ADDL NAMES:                                                                                                                         SOCIAL SECURITY           xxxxxxxxxxxxxxxx             MADISON TN 371159906                                                                                                                                                                                            

0
samiam41
Asked:
samiam41
  • 4
1 Solution
 
Patrick MatthewsCommented:
Please post a sample input file, and a sample Excel file showing what the output should look like based on that input.
0
 
samiam41Author Commented:
I have resolved this issue myself. I first import the file into a text file and then used vb.net to replace the *'s with vbtab, from there I was able to separate each record and upload to excel and set each column where I was able to pull out unwanted columns and save the columns I needed. I then uploaded that data to an acess database and created a report from there. Thanks
0
 
samiam41Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for samiam41's comment http:/Q_27418898.html#37071292
Assisted answer: 500 points for matthewspatrick's comment http:/Q_27418898.html#37041764

for the following reason:

none
0
 
samiam41Author Commented:
I choose the wrong button I ment to chose accept as solution
0
 
samiam41Author Commented:
I figured out how to do what I needed by taking a few more steps then I had thought about before.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now