Wierd results when importing txt file with header.

[EDN Admin]

Hello everyone!
Having a wierd problem when importing my txt file into mdb file using the code below. What happens is when I attempt to import the file sometimes it will not go over to the mdb file. Sometimes it will. No errors show up. If I try to narrow down the file
to locate a field that may be causing the problem I can narrow it down sometimes to just one line of text. However if the text file contains the header and just that one line of text, it works. My file has 49 fields and a couple of the fields are in brackets
because of restrictions. This seems to happen more often with larger files but also happens with smaller ones as well. Any help would be greatly appreciated and also help keep me from going bald prematurely!
<span style="font-size:x-small <font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Private<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Sub<span style="font-size:x-small btnDownLoadPLU_Click(<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small ByVal<span style="font-size:x-small
sender <span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small System.Object,
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small ByVal<span style="font-size:x-small e
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small System.EventArgs)
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Handles<span style="font-size:x-small btnDownLoadPLU.Click<font size="2


</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small AccessConn
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<span style="font-size:x-small System.Data.OleDb.OleDbConnection(<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=CLUProSAM_SPS-5X0_PLU_V14.mdb"<span style="font-size:x-small )<font size="2


</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small NoF<font size="2


</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small Ret<font size="2


</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small ofd
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<span style="font-size:x-small OpenFileDialog<font size="2

TableFileName = DataPath &
</font>

<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "SAM_SPS-5X0_PLU_V14.mdb"
<span style="font-size:x-small
ClickButton = 2
<font size="2
ofd.Filter =
</font>
<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "Text files (*.txt)|*.txt|csv files (*.csv)|*.csv"
<span style="font-size:x-small <font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small If<span style="font-size:x-small ofd.ShowDialog = Windows.Forms.DialogResult.OK
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Then
<span style="font-size:x-small <font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small fullpath
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small String<span style="font-size:x-small = ofd.FileName

strPath = ofd.FileName
<font size="2

</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Me<span style="font-size:x-small .txtFullPLUFILEPath.Text = fullpath<font size="2


</font>

<span style="color:#008000; font-size:x-small <span style="color:#008000; font-size:x-small Extract the file name
<span style="font-size:x-small <font size="2
strFile = Mid(strPath, InStrRev(strPath,
</font>
<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small ""<span style="font-size:x-small ) + 1)<font size="2


</font>

<span style="color:#008000; font-size:x-small <span style="color:#008000; font-size:x-small Separate out the path and put in my record
<span style="font-size:x-small
FilePath = LSet(strPath, Len(strPath) - Len(strFile) - 1)
<font size="2

</font>
<span style="color:#008000; font-size:x-small <span style="color:#008000; font-size:x-small Put in the file name
<span style="font-size:x-small <font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Me<span style="font-size:x-small .txtPLUFileName.Text = strFile<font size="2

FileName = fullpath.Replace(ofd.SafeFileName,
</font>

<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small ""<span style="font-size:x-small )<font size="2


</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Me<span style="font-size:x-small .txtPLUFilePath.Text = FilePath<font size="2


</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small ElseIf<span style="font-size:x-small ofd.ShowDialog = Windows.Forms.DialogResult.Cancel
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Then
<span style="font-size:x-small <font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End
<span style="font-size:x-small <font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small If
<span style="font-size:x-small
AccessConn.Open()
<font size="2

</font>
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small cmd
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<span style="font-size:x-small System.Data.OleDb.OleDbCommand(<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "DELETE
* FROM [SFTABLE] Where [Number] > 0"<span style="font-size:x-small , AccessConn)

txbxDelCnt.Text = cmd.ExecuteNonQuery()
<font size="2

</font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small AccessCommand
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<span style="font-size:x-small System.Data.OleDb.OleDbCommand(<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "INSERT
INTO [SFTable] ([Number], PLUCODE, DESCRIPT, GROUP1, STSGRP, [COUNT], RECIPETBL, INACTIVE, PRESET, OVERRIDE, PRICE1, PRCLVL1, PRICE2, PRCLVL2, PRICE3, PRCLVL3, PRICE4, PRCLVL4, PRICE5, PRCLVL5, PRCCHG, NFPLU, PRINTNV, MNM, PRDMIXGRP, STOCKLINK, MODQTY, FUNCTLINK,
DTMOD, DTDEL, DTADDED, USERGRP1, USEGRP1DS, USERGRP2, USEGRP2DS, USERGRP3, USEGRP3DS, STOCK, MINSTOCK, ONSALE, SALEPRICE, CHANGED, COSTFIELD, EXTRAB, BARCDESC, DTSTKMOD, STKMODIF, STKMODTYPE, CROSSLINK) SELECT [Number], PLUCode, Description, Group1, StatusGroup,
[Count], RecipeTable, Inactive, Preset, Override, Price1, PriceLevel1, Price2, PriceLevel2, Price3, PriceLevel3, Price4, PriceLevel4, Price5, PriceLevel5, PriceChange, NotFoundPlu, PrintNVImage, MixMatchTbl, PrdMixGroup, StockLink, ModifierQty, FunctionLink,
DtModified, DtDeleted, DtAdded, UserGroup1, UserGroup1Desc, UserGroup2, UserGroup2Desc, UserGroup3, UserGroup3Desc, Stock, MinStock, OnSale, SalePrice, Changed, CostField, ExtraBoolean, BarcodeDesc, DtStockModified, StockModifier, StockModifierType, CrossLink
FROM [Text;DATABASE="<span style="font-size:x-small & FilePath &
<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "].["<span style="font-size:x-small & strFile &
<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "]"<span style="font-size:x-small , AccessConn)

txbxSendCnt.Text = AccessCommand.ExecuteNonQuery()
AccessConn.Close()

<hr class="sig Charles

View the full article