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 ) - 05 Sep 2007 14:28 GMT
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.
SAL - 05 Sep 2007 19:28 GMT
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.
> Hi,
>
[quoted text clipped - 11 lines]
>
> If the DB is SQL server you could use a DTS/SSIS package instead.
Arne Vajhøj - 10 Sep 2007 01:56 GMT
>> 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?
If it used separators then it would not be fixed length ...
Arne
zacks@construction-imaging.com - 05 Sep 2007 14:39 GMT
> Is there an easy way to read a fixed length file that will be parsed and it's
> data inserted into a DB table?
[quoted text clipped - 74 lines]
>
> }
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@construction-imaging.com - 05 Sep 2007 15:19 GMT
On Sep 5, 9:39 am, za...@construction-imaging.com wrote:
> > Is there an easy way to read a fixed length file that will be parsed and it's
> > data inserted into a DB table?
[quoted text clipped - 79 lines]
> 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