Read/Parse a fixed length file

[Guest]

Is there an easy way to read a fixed length file that will be parsed and it’s
data inserted into a DB table?

Currently, the fixed length file contains 61 columns, and I am defining
variables in my code to store those values. I am also defining the variables
that specifies the size and position of the columns in the file so I can loop
though the columns and rows correctly. Below is a code snippet to give you
an idea of what I am doing. I am open to better way of do this.

public override void FileReader()
{
// read from the file
StreamReader oFs;
string sTemp;
string[] aData;
Hashtable oRow;
string sFile;
bool bAddRec = !CumulativeResults;
string sFileNumber;
string sAccountInfo = string.Empty;
Int32 iCounter = 0;

// define fields, positions, and lengths
string sNumber;
Int32 iNumberLen = 10;
const Int32 coniNumberPos = 0;

string sCustomerNumber;
Int32 iCustomerNumberLen = 30;
const Int32 coniCustomerNumberPos = 1;

string sPhone1;
Int32 iPhone1Len = 10;
const Int32 coniPhone1Pos = 2;

sFile = WorkingPath;

if (File.Exists(sFile))
{
oFs = File.OpenText(sFile);

sTemp = oFs.ReadLine();
while (sTemp != string.Empty)
{

// parse the line
iStartPos = 0;
sNumber = sTemp.Substring(iStartPos, iNumberLen).Trim;
iStartPos += iNumberLen;

sCustomerNumber = sTemp.Substring(iStartPos, iCustomerNumberLen).Trim;
iStartPos += iCustomerNumberLen;

sPhone1 = sTemp.Substring(iStartPos, iPhone1Len).Trim;
iStartPos += iPhone1Len;

// completed parsing the line
oRow = new Hashtable();
if (!Information.IsNumeric(sNumber)) {
sNumber = INVALID_ACCOUNT_ID.ToString;
}
oRow.Add("FileNumber".ToLower, sNumber);

// Account
oRow.Add("Account".ToLower, sCustomerNumber);

// HomePhone
sPhone1 = CleanPhoneNumber(sPhone1);
oRow.Add("HomePhone".ToLower, sPhone1);

// increment our row counter...
iCounter += 1;
base.DataCol.Add(oRow);
}
sTemp = oFs.ReadLine();
}
}

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Is there an easy way to read a fixed length file that will be parsed and
it's
data inserted into a DB table?

Is the file a text file? Do you have any separator char?
What is the final DB?

For the moment I think it's a better idea to hold the offset of the
different fields in an array. in this way you do not need 61 const.
and maybe instead of using a hashtable you use an array, you know it will be
61 elements, so you can refer to them using an index.


If the DB is SQL server you could use a DTS/SSIS package instead.

 

[zacks]

Is there an easy way to read a fixed length file that will be parsed and it's
data inserted into a DB table?

Currently, the fixed length file contains 61 columns, and I am defining
variables in my code to store those values. I am also defining the variables
that specifies the size and position of the columns in the file so I can loop
though the columns and rows correctly. Below is a code snippet to give you
an idea of what I am doing. I am open to better way of do this.

public override void FileReader()
{
// read from the file
StreamReader oFs;
string sTemp;
string[] aData;
Hashtable oRow;
string sFile;
bool bAddRec = !CumulativeResults;
string sFileNumber;
string sAccountInfo = string.Empty;
Int32 iCounter = 0;

// define fields, positions, and lengths
string sNumber;
Int32 iNumberLen = 10;
const Int32 coniNumberPos = 0;

string sCustomerNumber;
Int32 iCustomerNumberLen = 30;
const Int32 coniCustomerNumberPos = 1;

string sPhone1;
Int32 iPhone1Len = 10;
const Int32 coniPhone1Pos = 2;

sFile = WorkingPath;

if (File.Exists(sFile))
{
oFs = File.OpenText(sFile);

sTemp = oFs.ReadLine();
while (sTemp != string.Empty)
{

// parse the line
iStartPos = 0;
sNumber = sTemp.Substring(iStartPos, iNumberLen).Trim;
iStartPos += iNumberLen;

sCustomerNumber = sTemp.Substring(iStartPos, iCustomerNumberLen).Trim;
iStartPos += iCustomerNumberLen;

sPhone1 = sTemp.Substring(iStartPos, iPhone1Len).Trim;
iStartPos += iPhone1Len;

// completed parsing the line
oRow = new Hashtable();
if (!Information.IsNumeric(sNumber)) {
sNumber = INVALID_ACCOUNT_ID.ToString;
}
oRow.Add("FileNumber".ToLower, sNumber);

// Account
oRow.Add("Account".ToLower, sCustomerNumber);

// HomePhone
sPhone1 = CleanPhoneNumber(sPhone1);
oRow.Add("HomePhone".ToLower, sPhone1);

// increment our row counter...
iCounter += 1;
base.DataCol.Add(oRow);
}
sTemp = oFs.ReadLine();
}



}

You may want to consider using the Microsoft ODBC Text Driver. With a
proper Schema.INI you can read the file with a standard SQL-Style
Select statement and the names of the returned fields in the resultset
are defined by the Schema.INI file.

 

[zacks]

Is there an easy way to read a fixed length file that will be parsed and it's
data inserted into a DB table?

Currently, the fixed length file contains 61 columns, and I am defining
variables in my code to store those values. I am also defining the variables
that specifies the size and position of the columns in the file so I can loop
though the columns and rows correctly. Below is a code snippet to give you
an idea of what I am doing. I am open to better way of do this.

public override void FileReader()
{
// read from the file
StreamReader oFs;
string sTemp;
string[] aData;
Hashtable oRow;
string sFile;
bool bAddRec = !CumulativeResults;
string sFileNumber;
string sAccountInfo = string.Empty;
Int32 iCounter = 0;

// define fields, positions, and lengths
string sNumber;
Int32 iNumberLen = 10;
const Int32 coniNumberPos = 0;

string sCustomerNumber;
Int32 iCustomerNumberLen = 30;
const Int32 coniCustomerNumberPos = 1;

string sPhone1;
Int32 iPhone1Len = 10;
const Int32 coniPhone1Pos = 2;

sFile = WorkingPath;

if (File.Exists(sFile))
{
oFs = File.OpenText(sFile);

sTemp = oFs.ReadLine();
while (sTemp != string.Empty)
{

// parse the line
iStartPos = 0;
sNumber = sTemp.Substring(iStartPos, iNumberLen).Trim;
iStartPos += iNumberLen;

sCustomerNumber = sTemp.Substring(iStartPos, iCustomerNumberLen).Trim;
iStartPos += iCustomerNumberLen;

sPhone1 = sTemp.Substring(iStartPos, iPhone1Len).Trim;
iStartPos += iPhone1Len;

// completed parsing the line
oRow = new Hashtable();
if (!Information.IsNumeric(sNumber)) {
sNumber = INVALID_ACCOUNT_ID.ToString;
}
oRow.Add("FileNumber".ToLower, sNumber);

// Account
oRow.Add("Account".ToLower, sCustomerNumber);

// HomePhone
sPhone1 = CleanPhoneNumber(sPhone1);
oRow.Add("HomePhone".ToLower, sPhone1);

// increment our row counter...
iCounter += 1;
base.DataCol.Add(oRow);
}
sTemp = oFs.ReadLine();
}

}

You may want to consider using the Microsoft ODBC Text Driver. With a
proper Schema.INI you can read the file with a standard SQL-Style
Select statement and the names of the returned fields in the resultset
are defined by the Schema.INI file.

In case you decide to go this route, here is a link that might help:

http://msdn2.microsoft.com/en-us/library/ms709353.aspx

 

[Guest]

Hi Ignacio,

Yes, this is a text file we get to import date into our system, and the
users use a WinApp to select and import the file into the database.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Is the file a text file? Do you have any separator char?

If it used separators then it would not be fixed length ...

Arne