Processing multiple textr files usiing dataadapter

The Devil is likely to be in the detail.  Can you please show your actual code rather than this pseudo version?

Roger

Roger,

as requested, please find below the main subroutine that is called for each file to be processed.

    Sub LoadSource(ByVal fName As String)

        Me.Cursor = Cursors.WaitCursor

        'Set up a schema.ini file for the file being processed
        SetupSemiColonSchemaIni(fName)

        'Dimension various objects
        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter
        Dim fiMyFile As IO.FileInfo = New IO.FileInfo(fName)
        Dim spath As String = My.Computer.FileSystem.GetFileInfo(fName).DirectoryName
        Dim iCol As Integer
        Dim sConnectionString As String

        'Check to see if the file has already been loaded to the database
        If CheckIfFileIsLoaded(fiMyFile.Name) Then
            txtSourceFile.Text = ""
            Exit Sub
        End If

        'check to ee if the schema ini file exists, if not create it
        If Not My.Computer.FileSystem.FileExists(spath & "\Schema.ini") Then
            'rbcomma = radio button on the form
            If rbComma.Checked Then
                SetupCommaSchemaIni(fName)
            Else
                SetupSemiColonSchemaIni(fName)
            End If
        End If

        AddTextToRTB("=======NEW FILE========")
        AddTextToRTB("Loading file " & fiMyFile.Name)

        'Get Header Value and resolve connection string
        If Me.chkHeaderRow.Checked Then
            sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fiMyFile.DirectoryName & ";Extended Properties=""Text;HDR=Yes"""
        Else
            sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fiMyFile.DirectoryName & ";Extended Properties=""Text;HDR=No;FMT=Delimited(;)"""
        End If

        'Set up the connection object
        Dim cConn As New OleDbConnection(sConnectionString)

        Try
            'Open the connection to the file.
            cConn.Open()
        Catch ex As OleDbException

            Select Case ex.ErrorCode
                Case -2147467259            'File already opened
                    Dim msg As String = "The selected file is already opened in another application." & vbLf
                    msg = msg & "Close the file in the other application and try again."
                    MessageBox.Show(msg, "File Open Error", MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
                Case Else
                    MessageBox.Show(ex.ErrorCode & " : " & ex.Message)
                    Exit Sub
            End Select

        End Try

        Try
            'Update the user
            AddTextToRTB("Selecting Data from " & fiMyFile.Name)

            'create the SQL select statement
            Dim sSQL As String = "SELECT * FROM [" & fiMyFile.Name & "]" & vbLf
            sSQL = sSQL & "Where TransDate >= '20051001'"

            'create the selection command
            Dim objCmdSelect As New OleDbCommand

            With objCmdSelect
                .Connection = cConn
                .CommandType = CommandType.Text
                .CommandText = sSQL
            End With

            'handle the dataadapter

            da.SelectCommand = objCmdSelect
            da.Fill(ds)                                                      <<=== ERROR OCCURS HERE 'Required parameters not set'
                                                                                            Error happens on the second run through

            'set the DGV datasource
            DGV.DataSource = ds.Tables(0).DefaultView

            'close the connection
            cConn.Close()
            cConn.Dispose()

            'dispose of the select command
            objCmdSelect.Dispose()
            objCmdSelect = Nothing

            'force garbage collection
            GC.Collect()

            'do  some other events
            Application.DoEvents()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Data Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
            Exit Sub
        End Try

        Try
            'Check each column and delete empty columns
            AddTextToRTB("Validating file " & fiMyFile.Name)

            'Get row/column counts
            Dim iColCount As Integer = ds.Tables(0).Columns.Count - 1
            Dim iRowCount As Integer = ds.Tables(0).Rows.Count - 1

            'update the UI
            txtRowCount.Text = iRowCount
            txtColumnCount.Text = iColCount
            Me.Refresh()

            'File must have 14 columns at this point
            'Source / Sub_System / ClientCode / Sap_Co / LocalSKUCode / TransDate / Volume / UoM
            'Trading partner / Sales Org / Dist Channel / Sales Territory / APO_Market / Plant

            If iColCount <> 13 Then         '13 as col count starts at zero
                txtColumnCount.Text = iColCount
                Me.Refresh()
                Dim msg As String = "Incorrect number of Colunms. Expecting 14:" & vbLf
                msg = msg & "Please try again."
                MessageBox.Show(msg, "Column Count Error", MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
                'clear the DGV
                DGV.DataSource = Nothing
                Exit Sub
            End If

            AddTextToRTB("Resolving headers for " & fiMyFile.Name)
            'If no header rows now add the column headers
            If Not Me.chkHeaderRow.Checked Then
                'Now set header text for the columns
                Me.DGV.Columns(0).HeaderText = "Source"
                Me.DGV.Columns(1).HeaderText = "Sub_System"
                Me.DGV.Columns(2).HeaderText = "ClientCode"
                Me.DGV.Columns(3).HeaderText = "SAP_Co"
                Me.DGV.Columns(4).HeaderText = "LSKU_Code"
                Me.DGV.Columns(5).HeaderText = "TransDate"
                Me.DGV.Columns(6).HeaderText = "Volume"
                Me.DGV.Columns(7).HeaderText = "UoM"
                Me.DGV.Columns(8).HeaderText = "TradingPartner"
                Me.DGV.Columns(9).HeaderText = "SalesOrg"
                Me.DGV.Columns(10).HeaderText = "Distribution"
                Me.DGV.Columns(11).HeaderText = "SalesTerritory"
                Me.DGV.Columns(12).HeaderText = "APO_Mkt"
                Me.DGV.Columns(13).HeaderText = "Plant"
            End If

            'Autosize the columns
            For iCol = 0 To DGV.ColumnCount - 1
                Me.DGV.Columns(iCol).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            Next

            'set some specific numeric formats
            With DGV.Columns(DGVCols.Volume)
                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                .DefaultCellStyle.Format = "N4"
            End With

            'finished with schema ini file so delete it
            My.Computer.FileSystem.DeleteFile(fiMyFile.DirectoryName & "\schema.ini")

            'Get the system code from the DGV
            txtGroupPrefix.Text = GetSysCode(DGV.Item(DGVCols.Source, 1).Value)

            '            LoadSource = True

            'Process each row of data in preapration for database loading
            ProcessData(shortFileName, ds.Tables(0))

            'Check for mapping errors
            If CInt(txtLSKUErrors.Text) = 0 And CInt(txtMktErrors.Text) = 0 And CInt(txtEntityErrors.Text) = 0 Then
                'if we have no errors, load to the database
                LoadDataToDb(fName)
            Else
                If CInt(txtLSKUErrors.Text) <> 0 Then
                    AddTextToRTB("Failed to Load File due to LSKU Mapping errors")
                End If
                If CInt(txtMktErrors.Text) <> 0 Then
                    AddTextToRTB("Failed to Load File due to Market Mapping errors")
                End If
                If CInt(txtEntityErrors.Text) <> 0 Then
                    AddTextToRTB("Failed to Load File due to Entity Mapping errors")
                End If
                AddTextToRTB("Please resolve errors prior to attempting reload.")
            End If

            'Reset things
            txtLSKUErrors.Text = 0
            Me.txtMktErrors.Text = 0
            Me.txtEntityErrors.Text = 0
            DGV.DataSource = Nothing
            ds.Clear()
            ds.Reset()
            ds.Dispose()
            ds = Nothing
            da.Dispose()
            da = Nothing
            DGV.DataSource = Nothing

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Processing Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
            Exit Sub

        End Try

        Me.Cursor = Cursors.Default

    End Sub


Regards,


Alan

On a fairly superficial reading of the code, the most likely candidate to me looks like this bit.

        If Not My.Computer.FileSystem.FileExists(spath & "\Schema.ini") Then
            'rbcomma = radio button on the form
            If rbComma.Checked Then
                SetupCommaSchemaIni(fName)
            Else
                SetupSemiColonSchemaIni(fName)
            End If
        End If

On the first run, there will (I assume) be no Schema.ini for a file in the spath.  So one will be created for fName.  But, on the second run (with spath the same) there will be a Schema.ini so Setup[...]SchemaIni will not run.  This might lead to the dataadapter trying to read one file with a Schema.ini created for another.

As I don't know what your Setup[...]SchemaIni contains, it has to be a guess.  But, given that dataadapter is created afresh each time, I find it difficult to see what else could be being carried over from the first run to the second.  If that's not it, I (or someone) will dig a bit deeper.

Roger

Hi Roger,

the Schema.ini file is also deleted after every run through and recreated for each new file.  There was a timing issue here as the creation of the schema.ini file takes some time and it is possible that it is misreading the schema. I will look into this and report back if I change anything.

Regards,


Alan

VBTurbo,

Thanks for your post. I will try out your solution, but also want to see what I have got wrong in the original approach before implementing the final approach.

Regards,


Alan

Yes that would serve your best interest to get it as intended.

Also sure off that Roger is fully capeable to solve your problem.-')
I made my post between your question asked and Roger's reply so i didn't see his request for code.

Hope you'll get it solved

vbturbo

>>
            'finished with schema ini file so delete it
            My.Computer.FileSystem.DeleteFile(fiMyFile.DirectoryName & "\schema.ini")
<<

Missed that.  Well, I did say "superficial" ;-).

Thanks vbturbo for your confidence, but I'm not sure it's justified.  I've looked in a bit more detail at the code now and cannot see anything.  Given that the objects concerned all appear to be created within the sub it is difficult to see how/why - even if they were not expressly disposed of, which they are - something should go wrong only on a second run.

Not recognising the error message, I've just done a Google on 'Required parameters not set' and found nothing relating to VB.NET or to dataadapters.  I wonder if, temporarily, changing the Catch MessageBox ex.Message to ex.ToString might give more of a clue?

Roger

Roger,

changing the exception output as suggested gives the following error message

System.Data.OleDb.OleDbException: No value given for one or more required parameters.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at CompEngine_Despatches_Upload.frmMain.LoadSource(String fName, Int32 iFileNo, Int32 iFileCount) in C:\Projects\CompEngine_V2\CompEngine\Form1.vb:line 996

Regards,


Alan

Hi Alan

On top of my head

Dim sSQL As String = "SELECT * FROM [" & fiMyFile.Name & "]" & vbLf

Could it be that (fiMyFile.Name) is missing the full path to the file?

For eliminating that bug possibility , you could try write/insert the full path instead e.g   c:\temp\fiMyFile.txt
Just to make certain the file and data is collected.

And /or .... do the same for the datasource
Data Source=" & fiMyFile.DirectoryName &

as it seems from your : "exception output"  that there is no file/data to select from when trying to fill the dataset with data,


           'handle the dataadapter

            da.SelectCommand = objCmdSelect
            da.Fill(ds)                                                      <<=== ERROR OCCURS HERE 'Required parameters not set'
                                                                                            Error happens on the second run through

vbturbo

Hi vbturbo,

I have tried your suggestion, but the app now bombs out in the SQL statement.  If I revert back, the code works for the first iteration of the loop, but fails on the subsequent attempts.

Regards,


Alan

Try these settings

I've shorten your Sql statement a bit ,just to make sure that the dataset get's populated.
And i removed the brackets because the data adapter wount except these , "this is off my own ealier experience"

Dim sSQL As String = "SELECT * FROM '" & fiMyFile & "'"



1)
Dim fiMyFile as string = fiMyFile.txt

if not working the try alter
2)
Dim fiMyFile as string = fiMyFile


----------------------------------------------------------------------------
If Me.chkHeaderRow.Checked Then
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="c:\temp\fiMyFile.txt";Extended Properties=""Text;HDR=Yes"""
        Else
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="c:\temp\fiMyFile.txt";Extended Properties=""Text;HDR=No;FMT=Delimited(;)"""
End If


Hope this helps

Alan

"No value given for one or more required parameters" is an error message I do recognise.  But it only appears to confirm my original thought that the problem is with the schema.ini file.  I've been doing some testing with your code and the only way I can reproduce that specific error message is by the schema.ini not including the TransDate column.

If you haven't already done so I think you need, temporarily, to revise your code so that you can inspect the schema.ini file that is actually used on each pass.  Doing a debug write of a streamreader's .ReadToEnd on the newly created file would be one way.

I don't think that it contributes to the current problem but there is an oddity in your code on this.  Right at the start of your LoadSource sub you have these lines

        'Set up a schema.ini file for the file being processed
        SetupSemiColonSchemaIni(fName)

Then, a dozen lines later, you have these lines

        'check to ee if the schema ini file exists, if not create it
        If Not My.Computer.FileSystem.FileExists(spath & "\Schema.ini") Then
            'rbcomma = radio button on the form
            If rbComma.Checked Then
                SetupCommaSchemaIni(fName)
            Else
                SetupSemiColonSchemaIni(fName)
            End If
        End If

The code within that If will never fire, as the relevant (semicolon) Schema.ini will - errors excepted - always have already have been created by the earlier lines.  As I say, given that (as I understand it) we are here in a semicolon only scenario, I do not see this as being the cause of this particular problem.  But it's something you might like to look at.

Roger

Hi Roger,

I have performed the following activities:-

1) removed the additional code highlighted
2) performed the debug write on the schema.ini file
       - this revealed an issue where the schema.ini was being appended with data on the second run through, so this is now sorted out.
3) amended the connectionstring property slightly.

The results of the above changes are:

Debug printout from the schema ini files + connection string & sql used for file 1

================================
Schema.ini for PTS_20060206_DESPATCHES.csv
================================
[PTS_20060206_DESPATCHES.csv]
Format=Delimited(;)
ColNameHeader = False
CharacterSet=ANSI
col1=Source char width 255
col2=Sub_System char width 255
col3=ClientCode char width 255
col4=Sap_Co char width 255
col5=LSKU_Code char width 255
col6=TransDate char width 255
col7=Volume double
col8=UoM char width 255
col9=TradingPartner char width 255
col10=SalesOrg char width 255
col11=Distribution char width 255
col12=SalesTerritory char width 255
col13=APO_Mkt char width 255
col14=Plant char width 255

connectionstring                 = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CompEngine;Extended Properties="Text;HDR=No;FMT=Delimited;";
SQL                                  = SELECT * FROM PTS_20060206_DESPATCHES.csv



File 2 debug information

================================
Schema.ini for PTA_20060220_DESPATCHES.csv
================================
[PTA_20060220_DESPATCHES.csv]
Format=Delimited(;)
ColNameHeader = False
CharacterSet=ANSI
col1=Source char width 255
col2=Sub_System char width 255
col3=ClientCode char width 255
col4=Sap_Co char width 255
col5=LSKU_Code char width 255
col6=TransDate char width 255
col7=Volume double
col8=UoM char width 255
col9=TradingPartner char width 255
col10=SalesOrg char width 255
col11=Distribution char width 255
col12=SalesTerritory char width 255
col13=APO_Mkt char width 255
col14=Plant char width 255

connectionstring = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CompEngine;Extended Properties="Text;HDR=No;FMT=Delimited;";

sSQL = SELECT * FROM PTA_20060220_DESPATCHES.csv


The application now passes the previous error point (da.fill(ds)) on the second run through, so that is a step forward, but now the correct number of columns (14) is not recognised!
So I think this is another schema issue or connectionstring issue.

Regards,


Alan

vbturbo,

I have included your changes (removed the where clause, removed the square brackets) as part of the above exercise.

As stated in the previous post, the dataadapter fill issue seems to be resolved, but now the datatable has only one column instead of the 14 expected per the schema file.

Regards,


Alan

Alan

I'm not sure about this, but I suggest you try changing your connection string so that ExtendedProperties= is confined to "Text;".  That is, get rid of "HDR=No;FMT=Delimited;".

My thinking here is that, given the existence of a shema.ini that additional information is (a) not necessary and (b) may be confusing the Driver and (c) in particular, as a default, tells the driver to expect a comma-, not a semicolon-, delimited file.

I haven't tested on that point, but rather than me having to put more dummy data to test with, it's something you could check fairly easily with your existing setup.

Roger

I just thought that to Roger

if he sees the collum data like this


Out of curiosity

Are that collum populated like this?


"Source" ----as for the dgview Columns(1).
somedata
somedata
somedata
somedata
"Sub_System"--as for the dgview Columns(2).
somedata
somedata
somedata
somedata
"ClientCode"---"as for the dgview Columns(3).
somedata
somedata
somedata
somedata

and so on

else you can write the dataset to a txt file just after its populated to see wheater this is true
dataset1.WritetxtFile("c:\filename.txt")

If yes ,In that case you have a delimiter problem somewhere

vbturbo

Hi vbturbo,

The file is being interpreted as you state, however I am pulling all the columns in as follows:-

Col1 = Source
Col2 = Sub_System

etc

The changes suggested by Roger result in the following:-

================================
Schema.ini for PTW_20060305_Despatches.csv
================================
[PTW_20060305_Despatches.csv]
Format=Delimited(;)
ColNameHeader = False
CharacterSet=ANSI
col1=Source char width 255
col2=Sub_System char width 255
col3=ClientCode char width 255
col4=Sap_Co char width 255
col5=LSKU_Code char width 255
col6=TransDate char width 255
col7=Volume double
col8=UoM char width 255
col9=TradingPartner char width 255
col10=SalesOrg char width 255
col11=Distribution char width 255
col12=SalesTerritory char width 255
col13=APO_Mkt char width 255
col14=Plant char width 255

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Alan Barnard\My Documents\Downloads\CompEngine\;Extended Properties=Text

SELECT * FROM PTW_20060305_Despatches.csv

I have amended the connection string to remove the other extended properties as suggested ( I have also tried text;) with the same result that the ds ends up only recognising the first column in the file.

I have output the ds schema to XML as follows:-

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="PTA" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

which seems to indicate a schema issue recogising the delimiter used (;).

Export the dataset context seems the bear this out

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <PTA>PTA</PTA>
  </Table>
  <Table>
    <PTA>PTA</PTA>
  </Table>
  <Table>
    <PTA>PTA</PTA>
  </Table>
  <Table>
    <PTA>PTA</PTA>
  </Table>

which repeats for each row in the ds.

So the issue now seems to be why the semi colon field separator is not being recognised.

Regards,


Alan

Alan

Can you please post a few sample lines of one of your files?  If you're concerned about confidentiality then substititute "*" (or whatever, or a mixture) for the real characters occasionally, sufficiently to hide anything you're bothered about, before posting.  But make sure that the length of fields and their spacing and any separators are left as they are.  It's the the layout and the delimiting that I'm most interested to see.

I need to test as, at the moment, I'm utterly foxed.

One other thing you might be able to test at your end.  Can you check that the character that LOOKS like a semi-colon in your source files is in fact Chr(59)?  I assume it must be, or the whole line of the file would come out as one record.  But I'm thrashing about and I've seen that sort of problem with other characters.  And it's the sort of thing that can get altered just by the process of copying and pasting into a post here.

Roger

Hi Roger,

the data below is from one of the files.

PTW;;001;0162;000000000000700186;20060213;10.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00151;20060213;245.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00251;20060213;30.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00257;20060213;430.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00351;20060213;335.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00457;20060213;270.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00557;20060213;150.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00657;20060213;10.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B10260;20060213;90.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B30257;20060213;265.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B30357;20060213;245.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B30457;20060213;5.000;TSD;0000;0162;01;PL;;4361

I am qually baffled now as the dataadapter is filling for each file, but the issue is simply now that each file is not being split into fields on the basis of the semi-colon.

I am going to adapt the file and see if it works with comma's.

Thanks for your continuing assistance.

Regards,


Alan

Alan

I've just tested that data with your schema.ini, both copied and pasted "as is" into my files.  All came out OK.  Although I had also copied and pasted your code, I had commented out various bits of it, and substituted my own names.  So - if there is a logical explanation - the problem must lie somewhere in the names or code my test wasn't using.  I have to go out now, but one vague bell that is ringing relates to the sue of .csv file names.  So, try changing the .csv extention of your input file names to .txt.

I'll look in again when I'm back - probably a couple of hours.

Roger

Sorry but had to go for a while.

Just wondering

how is it that the csv file is delimited with semi-colon and not comma's - according this "old link" it should be commas
-------------------------------------------------------------------------------------------------------------------------------------------
CSV Delimited Fields in the file are separated by commas (note that there should not be a space between the comma and the start of the next field name or value):
LastName,FirstName

Myer,Ken
 Format = CSVDelimited
------------------------------------------------------------------------------------------------------------------------------------------
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

allthough there should be a chance off succes if you are able to replace the semi-colon with commas


vbturbo

Hi vbturbo,

The CSV files are extracts from source systems where he delimiter has been specified as a semi-colon though the files are called CSV!

Hi vbturbo,

I have tried changing the delimiter to a comma and this works, so an approach would be to pre-process the files changing the semi-colon's into comma's then processing as necessary from there.  However, the use of the schema.ini file should allow me to use any delimiter I require and certainly this has worked up to today.

In summary the situation is that I could process a single semi-colon separated file with no problem, but cound not process > 1 file without the dataadapter falling over.
Now the data adapter doesn't fall over, but I am only getting one field instead of the 14 expected.

Luckily I saved on old version that worked for the one file so I am going to substitute the old connection string to see if I can get that to work.

Thanks for your continued support.

Regards,


Alan

Here's what I've got.

This file, called C:\Test\AlanCSV.csv

PTW;;001;0162;000000000000700186;20060213;10.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00151;20060213;245.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00251;20060213;30.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00257;20060213;430.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00351;20060213;335.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00457;20060213;270.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00557;20060213;150.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B00657;20060213;10.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B10260;20060213;90.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B30257;20060213;265.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B30357;20060213;245.000;TSD;0000;0162;01;PL;;4361
PTW;;001;0162;B30457;20060213;5.000;TSD;0000;0162;01;PL;;4361

This file, called C:\Test\Schema.ini

[AlanCsv.csv]
Format=Delimited(;)
ColNameHeader = False
CharacterSet=ANSI
col1=Source char width 255
col2=Sub_System char width 255
col3=ClientCode char width 255
col4=Sap_Co char width 255
col5=LSKU_Code char width 255
col6=TransDate char width 255
col7=Volume double
col8=UoM char width 255
col9=TradingPartner char width 255
col10=SalesOrg char width 255
col11=Distribution char width 255
col12=SalesTerritory char width 255
col13=APO_Mkt char width 255
col14=Plant char width 255

One form (Form2) with one button and one datagridview (DGV).  This code

Imports System.Data.OleDb
Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LoadSource2("C:\Test\AlanCSV.csv")
        MsgBox("Done One")
        DGV.DataSource = Nothing
        MsgBox("Cleared")
        LoadSource2("C:\Test\AlanCSV.csv")

    End Sub

    Private Sub LoadSource2(ByVal fname As String)

        Me.Cursor = Cursors.WaitCursor

        'Dimension various objects
        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter
        Dim fiMyFile As IO.FileInfo = New IO.FileInfo(fname)
        Dim spath As String = My.Computer.FileSystem.GetFileInfo(fname).DirectoryName
        Dim sConnectionString As String

        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fiMyFile.DirectoryName & ";Extended Properties=""Text;HDR=No;FMT=Delimited(;)"""

        'Set up the connection object
        Dim cConn As New OleDbConnection(sConnectionString)

        Try
            'Open the connection to the file.
            cConn.Open()
        Catch ex As OleDbException

            Select Case ex.ErrorCode
                Case -2147467259            'File already opened
                    Dim msg As String = "The selected file is already opened in another application." & vbLf
                    msg = msg & "Close the file in the other application and try again."
                    MessageBox.Show(msg, "File Open Error", MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
                Case Else
                    MessageBox.Show(ex.ErrorCode & " : " & ex.Message)
                    Exit Sub
            End Select

        End Try

        Try

            'create the SQL select statement
            Dim sSQL As String = "SELECT * FROM [" & fiMyFile.Name & "]" & vbLf
            sSQL = sSQL & "Where TransDate >= '20051001'"


            'create the selection command
            Dim objCmdSelect As New OleDbCommand

            With objCmdSelect
                .Connection = cConn
                .CommandType = CommandType.Text
                .CommandText = sSQL
            End With

            'handle the dataadapter

            da.SelectCommand = objCmdSelect
            da.Fill(ds)                                                    

            'set the DGV datasource
            DGV.DataSource = ds.Tables(0).DefaultView

            'close the connection
            cConn.Close()
            cConn.Dispose()

            'dispose of the select command
            objCmdSelect.Dispose()
            objCmdSelect = Nothing

            'force garbage collection
            GC.Collect()

            'do  some other events
            Application.DoEvents()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Data Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
            Debug.WriteLine(ex.Message)
            Exit Sub
        End Try


        Me.Cursor = Cursors.Default

    End Sub
End Class

All the code in that is yours - I have just "hardwired" some of the optional settings and got rid of the procedures which were used to implement them and the references to those procedures.  I have tried it (as shown) with and without the 'HDR=No;FMT=Delimited(;)' in the connection string.  I have tried it with the file extensions .txt as well as .csv.  (Having now had a chance to check back, I think it was .tsv rather than .csv extensions that produced the problem I vaguely remembered.  See this https://www.experts-exchange.com/questions/21875001/Parse-Comma-Delimited-File.html)

For me, it works as it stands, and none of those variations has broken it in any way.  Try it, and see if it works that way for you.

If it doesn't, then the error that you get in it and where you get it will give more of a clue as to what needs investigating further,  If it does work for you then, in my view, the problem is not intrinsic in this code.  It must either lie in some system problem - e.g. corrupted Driver, but in that case I would not expect anything to happen - or somewhere else in the code.  

What do you want to do?  There are workarounds - e.g. changing the delimiters from semicolons to commas before processing, or reading the input files with a streamreader and doing your own splitting.  But if the problem does lie elsewhere in the code, it may then resurface in another way.

If you want help to track down if it is in any other part of the code then the only thing I can suggest is that you zip up the whole project and post it at http://www.ee-stuff.com/ and then post the url back here.  It will perhaps be necessary for me to cobble something together to "dummy" the output to the database, but I reckon, so far as input is concerned, there is already sufficient sample data for testing purposes.

Roger

Hi Roger

I have now read your recommend link to Alan and followed all the sub links in that regard.
And It really takes one to a higher level off comprehension on how to approach aspects of problem solving and optimizing.
It's also hard not to get impressed by the amount of contributions you have, to solve different problem senario's.

Thanks for letting one in on how good stuff is made and nut's are cracked.

Jens

Hi All,

sorry for the silence today, been travelling around.  I will try the code Roger and report back.

Many thanks,


Alan

Hi Roger,

tried the code as specified and the 'No value given for one or more required parameters' error returns at the da.fill(ds) line on the first run through.

I am going to load the code onto a new machine tomorrow and try it there and in the interim look at changing the delimiter to a comma (most of this code is already written).

But I definately want to pursue this through to the end.

Regards,


Alan

Hi All,

some further thoughts on the issues in this question:-

1) The 'No Value given for one or more required parameters' only occurs when the SQL used to read the source file has the Where clause in it.  This leads me to belive that the field name 'TransDate' is not being recognised.

2) If point 1 is correct, this means that the provider is not using the generated schema.ini file to read the file contents into the datatable.  Any ideas why this would not happen?

3) In previous versions when processing only one file, I noticed that I had to build the schema.ini file twice prior to accessing the data in order for it to work. I also build in a delay to ensure that any file locks were released prior to accessing the file with the dataadapter.

4) As the schema.ini is not being used (so it seems) the app runs into further issues where it seems to arbitrarily strip leading zeros from some fields.

Questions:

a) Any idea why the schema.ini is being ignored?
b) Do I have to use OLEDB data adpater?

regards,


Alan

Hi Alan

You should consider that you might have a system problem. Have you tested the app on a different machine?
This would eliminate further investigation on your questions.(If Rogers assumption is correct,  since he gets app to run flawless) and he is rarely wrong!

As Roger states ,
---------------------------------------------
For me, it works as it stands, and none of those variations has broken it in any way.  Try it, and see if it works that way for you.

If it doesn't, then the error that you get in it and where you get it will give more of a clue as to what needs investigating further,  If it does work for you then, in my view, the problem is not intrinsic in this code.  It must either lie in some system problem - e.g. corrupted Driver, but in that case I would not expect anything to happen - or somewhere else in the code.  
---------------------------------------------

But as an option you could consider revise your approach with datastorage (read/write) and adapt another type off solution: xml perhaps?
Wich alternatively also supports schema definitions and datamobility (easy to transfer data from A to B)

But i find it strange that Roger gets the code to run , and you not
Until that is clerifyed, why then change something that fullfils your requirements.

But you could as suggested try upload the the solution to  http://www.ee-stuff.com/ then you'll have some certainty and second opinions regarding an system issue

Jens

Alan

On the OleDb Adapter question.  I know there is an ODBC Driver for text files, but I've no real experience with ODBC so can neither give you details or offer help with the coding.

On what is wrong using the OleDb driver, I still don't know.  On your thoughts on that front, whilst I broadly agree with your thinking, I would want more evidence before reaching my own conclusion.

For instance, when you tried my code on your machine and it failed, did you do so using the test files that I had used and included in the post - changing the directory in the ref in the Button1_Click sub?  It is only if you did it that way, rather than using the data and schema.ini already on your machine, that I can be sure that our tests are "in synch".

I agree that it looks like it's not reading the schema.ini - except that I cannot explain the results that you were getting on the occasions, earlier on, when it worked but only picked up the first field.  I say this because I have now tested "Delimited(;)" as part of the extended properties.  I had never previously attempted to use any delimiter other than a comma without a schema.ini and so had assumed that approach would work.  But, for me, it doesn't.  The implication of that is that, when it did work but only picked up one field it must have been recognising the semicolon as a delimiter, and it could only have done that by reading a schema.ini.

On the issue of whether the recently built schema.ini was locked or something when the Driver first tried to use it, that may depend on (a) the precise form of your SetupSemiColonSchemaIni(fName) sub and (b) security etc settings on your system.  But I have tried my code amended so that it actually writes the schema.ini before it uses it and deletes it afterwards, and that produces no problems.  As (I assume) control will not return from the schema.ini writing sub to the LoadSource sub until the file has been closed, I'm finding it difficult to see how that file could not then immediately be ready for use.

One other thing it might be worth checking.  There are settings for the Jet Drivers in the Registry: on my machine, this key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

Assuming it's the same in yours, if you highlight that node in RegEdit and do an export to a .reg file, you can then open that file with NotePad and copy its contents into a post here.  If there are differences then it might be worth considering what effects those might have.  But that would need research (I've only ever looked at the settings for  an Excel context, and even on that it was some time ago and I cannot remember all the details).  I'm not inclined to undertake it without knowing that it might be useful.  If there are no differences, it won't be.

Roger

Hi Roger / Jens,

I have tried loading the project onto a different machine with the same result.
I also get the 'no value' error using Roger's code on both machines.

Below is the registry key requested.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats\Text]
"Engine"="Text"
"ExportFilter"="Text Files (*.txt;*.csv;*.tab;*.asc)"
"ImportFilter"="Text Files (*.txt;*.csv;*.tab;*.asc)"
"CanLink"=hex:01
"OneTablePerFile"=hex:01
"IsamType"=dword:00000002
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"ResultTextImport"="Import data from the external file into the current database. Changing data in the current database will not change data in the external file."
"ResultTextLink"="Create a table in the current database that is linked to the external file.  Changing data in the current database will change data in the external file."
"ResultTextExport"="Export data from the current database into a text file.  This process will overwrite data if exported to an existing file."
"SupportsLongNames"=hex:01
"OutputFormat"="MS-DOS Text (*.txt)"
"ReportFormat"="MS-DOS Text"
"FormatFunction"="txt,SOA_RptToAscii,1,MS-DOS Text (*.txt)"

Forgot to tell you in all previous posts that I am using VS 2005 and SQL Server 2005.

I will change some of the data in the database then zip the whole app and send it through for you to look at it in all it's glory.

Regards,


Alan

Alan

That's identical to mine (except for a minor variation in the order of the keys) but it was

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

that I was most interested in, not

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats\Text

Roger

Hi Roger,

sorry, here is the correct information

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text]
"win32"="C:\\WINDOWS\\system32\\mstext40.dll"
"UseZeroMaxScanAs"="One"
"ImportMixedTypes"="Majority Type"
"DisabledExtensions"="!txt,csv,tab,asc,tmp,htm,html"
"CharacterSet"="ANSI"
"ImportFixedFormat"="RaggedEdge"
"Format"="CSVDelimited"
"Extensions"="txt,csv,tab,asc"
"FirstRowHasNames"=hex:01
"MaxScanRows"=dword:00000019
"ExportCurrencySymbols"=hex:01

Here the keys from my registry ,if it can help to some comparison

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text]
"win32"="C:\\WINDOWS\\System32\\mstext40.dll"
"UseZeroMaxScanAs"="One"
"ImportMixedTypes"="Majority Type"
"DisabledExtensions"="!txt,csv,tab,asc,tmp,htm,html"
"CharacterSet"="ANSI"
"ImportFixedFormat"="RaggedEdge"
"Format"="Delimited(;)"
"Extensions"="txt,csv,tab,asc"
"FirstRowHasNames"=hex:01
"MaxScanRows"=dword:00000019
"ExportCurrencySymbols"=hex:01

Jens

Well this might be an input

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

How Can I Modify the Registry?

Note   What happens if the registry is set to CSVDelimited, but you try to open a tab-delimited text file anyway? Well, because ADO can't determine the names of the fields (it's expecting to see names separated by commas), you'll typically get the error message, "Item cannot be found in the collection corresponding to the requested name or ordinal."

Jens

There's no difference between Alan's settings and mine, so that's not the problem.  Where our two have

"Format"="CSVDelimited"

Jens' has

"Format"="Delimited(;)"

so that might (???) account for any difference in test results at that end.  But it doesn't account for any differences in results between Alan and me.

I'll look forward to the upload.

Roger

if it is connection problem regarding including the schema.ini then i found this link regarding con-strings
according to Alans Question, i quote

2) If point 1 is correct, this means that the provider is not using the generated schema.ini file to read the file contents into the datatable.  Any ideas why this would not happen?

Connecting to a Text file using the JET OLE DB Provider:
http://www.codeproject.com/database/connectionstrings.asp

here is Alans connection string , also tested of you Roger
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fiMyFile.DirectoryName & ";Extended Properties=""Text;HDR=No;FMT=Delimited(;)"""

and here is the connectionstring found on the above link

strConnect =("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DatabasePath\\;"
        "Extended Properties=\"\"text;"
        "HDR=Yes;FMT=Delimited;\"\";");

There is a slightly difference  in the Extended Properties, but i dont know if that matters?

Jens

btw - also useing vs 2005

Jens

>>
There is a slightly difference  in the Extended Properties, but i dont know if that matters?
<<

I don't think so.  That code is, I think, C++.  It would resolve in VB.NET terms to

   strConnect ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabasePath\;Extended Properties=""text;HDR=Yes;FMT=Delimited;"""

So the only differences of substance would be the lower case 't' on "text" and that the ; after the delimiter was not enclosed in brackets.

I've just tried that, and get the same results as with Alan's original connection string.

Roger

Hi

I have uploaded the application files to the location. Unfortnately, the db size is over the 4 meg limit. the url is

https://filedb.experts-exchange.com/incoming/ee-stuff/1408-CompEngine_V2.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/1409-DB.zip


regards,


Alan

Well Roger it was just a thougt


what i meant is the Extended Properties="Text;"  Text is inside the quotes "" and i Allans the text definition is outside the quotes

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\TxtFiles;Extended Properties="Text;"';
ADODataSet.TableName := 'TxtFile#csv';


Hmm... Ill also look forward to the upload

Jens

Alan

Downloaded.  I'm tied up at the moment but will look at it as soon as possible.  But that might not be until this evening.

Roger

Hi Allan

Works fine here by me to - no problems with the semi-colon
All 14 collums are represented as expected.

Here is how ds is written out to a test.xml thoug i shorten it a bit.

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <Source>PTW</Source>
    <ClientCode>001</ClientCode>
    <Sap_Co>0162</Sap_Co>
    <LSKU_Code>000000000000700186</LSKU_Code>
    <TransDate>20060213</TransDate>
    <Volume>10000</Volume>
    <UoM>TSD</UoM>
    <TradingPartner>0000</TradingPartner>
    <SalesOrg>0162</SalesOrg>
    <Distribution>01</Distribution>
    <SalesTerritory>PL</SalesTerritory>
    <Plant>4361</Plant>
  </Table>
  </Table>
  <Table>
    <Source>PTW</Source>
    <ClientCode>001</ClientCode>
    <Sap_Co>0162</Sap_Co>
    <LSKU_Code>B30457</LSKU_Code>
    <TransDate>20060213</TransDate>
    <Volume>5000</Volume>
    <UoM>TSD</UoM>
    <TradingPartner>0000</TradingPartner>
    <SalesOrg>0162</SalesOrg>
    <Distribution>01</Distribution>
    <SalesTerritory>PL</SalesTerritory>
    <Plant>4361</Plant>
  </Table>
</NewDataSet>

Maybe the jet.engine driver with you is some how corrupted....

Jens

Also ill need a break for a couple off hours

Alan

Copied the 2 files as provided in previous above post into a C:\temp\    folder

C:\temp\PTW_20060305_Despatches.csv
and
C:\temp\Schema.ini

Made this call in the form load_event  

    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        LoadSource2("C:\temp\PTW_20060305_Despatches.csv")

    End Sub


No offence ,but do you have the csv file and the schema.ini in the same folder?

Jens

Alan

The good (???) news is, I'm getting the same results as you.  The bad news is, I've no idea - yet, anyway - why.

Roger

Hi all,

do you need the database files?  I have split them so they are below the 4 meg limit but for some reason I cannot load the second file. I will continue trying if you feel it is required.

Again, many thanks for your continued assistance.

Regards,


Alan

Alan

Re additional files - no.  I think I've found the problem.  I need to test possible solutions.  But I'll be back late nonight or tomorrow morning.

Roger

hmm very odd ! now i have adapted the same behaviour : the missing parameters

Roger

here is a similar issue

http://forums.microsoft.com/msdn/showpost.aspx?postid=553406&siteid=1

just wondering if it some sort of engine problem?

Jens

Roger / Jens,

Many thanks for your support and knowledge.  The issue is solved by Roger's last submission and I am currently processing 58 of the 500+ files I have to do!

I have split the points in an equitable way from my perspective, please advise if you disagree.

Many thanks for your stubborness in helping me solve this.

regards,


Alan

Alan

I think its fair to say that Roger solved(made it funtional) your problem , so in my opinion he is to be rewarded with the points
whatever ....you are hopefully able to get on

Though it is a weird in this sence ,that the csv file  is getting interpreted with the schema.ini  file ,the first time with Roger testing it out and ditto here
for then to get cranked up in the ole.db connection(string) or the data adapter somehow (dropping the parameters) ! since i didn't implemented/run the

Sub SetupSemiColonSchemaIni(ByVal fname As String)

when testing out the code, that is the file under this circumstance not have been modified or re - written ,to justify that it should be solely a text/stream writer issue.

Well thumbs up, Roger (you did it again)

Jens

Glad it worked.  I'm happy with the points as they are.  Thanks.

Here's the actual answer

https://www.experts-exchange.com/questions/22064517/StreamWriter-Write-or-WriteLine-a-discrepancy.html

Roger

This one i woun't forget...thanks -:')