A large part of my job is taking data in disparate forms and integrating it into Dynamics. The data is frequently in text file - some comma delimited, some fixed length, some 'variable fixed length'. By that I mean that the data has different formats inside the file, the format is usually determined by the first few characters in the line. For instance, we might have a file like this:
OH20110813002351SMITH 0001251
OD00001BIG HAMMER 11.99
OH20110813002351JONES 0001252
OD00001BIG WRENCH 13.99
The lines that start with 'OH' are Order Header lines, the line format would be:
| Character |
Field |
Type |
| 1-2 |
Line Type |
String |
| 3-10 |
Date |
Date |
| 11-16 |
Customer Number |
String |
| 17-25 |
Customer Name |
String |
| 26-35 |
Order Number |
String |
Our OL (Order Line) data is similar:
| Character |
Field |
Type |
| 1-2 |
Line Type |
String |
| 2-7 |
Quantity |
Int16 |
| 8-20 |
Item Number |
String |
| 21-28 |
Price |
Double |
So, how do we read that into VB and get it sent to Dynamics? In the past I've build complicated parsing routines, and used LINQ. I'm not fond of LINQ, nor am I fond of complicated routines. Enter in TextFieldParser
The TextFieldParser class is part of the Microsoft.VisualBasic.FileIO and is designed to handle all three of our file types, but the variable type is by far the harded to deal with. The code below explains how to use this object in your code