Imported CSV formatting issues

[WelshyWizard]

Hi all,

I've got a datagrid on a windows form which is populated by a CSV file, using the following code:

        If OpenFileDialog1.ShowDialog(Me) = DialogResult.OK Then
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName
            Dim objConn As New OleDbConnection(sConnectionString)
            objConn.Open()
            Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, objConn)
            Dim objAdapter1 As New OleDbDataAdapter
            objAdapter1.SelectCommand = objCmdSelect
            objAdapter1.Fill(objDataset1, "test")
            DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
            objConn.Close()
        End If

I then insert the data shown in the datagrid into a SQL Server table, using the code shown below:

        SqlConnection1.Open()
        For Each dr As DataRow In objDataset1.Tables(0).Rows
            SqlCommand2.Parameters("@F1").Value = dr("Cont#")
            SqlCommand2.Parameters("@F2").Value = dr("Str#")
            SqlCommand2.Parameters("@F3").Value = dr("Modeller       ")
            SqlCommand2.Parameters("@F4").Value = dr("Created")
            SqlCommand2.Parameters("@F5").Value = dr("Mark")
            SqlCommand2.Parameters("@F6").Value = dr("Quantity")
            SqlCommand2.Parameters("@F7").Value = dr("Section")
            SqlCommand2.Parameters("@F8").Value = dr("Grade")
            SqlCommand2.Parameters("@F9").Value = dr("Length")
            SqlCommand2.Parameters("@F10").Value = dr("Weight")
            SqlCommand2.Parameters("@F11").Value = dr("Area")
            SqlCommand2.Parameters("@F12").Value = dr("NHG")
            SqlCommand2.Parameters("@F13").Value = dr("USER11")
            SqlCommand2.Parameters("@F15").Value = dr("USER2")
            SqlCommand2.Parameters("@F16").Value = dr("USER12")
            SqlCommand2.Parameters("@F17").Value = dr("NAME")
            SqlCommand2.ExecuteNonQuery()
        Next
        SqlConnection1.Close()

I have three problems here.

PROBLEMS
1) the data held in column #str shows in the datagrid as '2'. However, in the CSV file it shows as '002'. The value of '2' is also what is inserted into the SQL Datbase. How do I keep the leading zeros both on screen and in the insert?

2) although comma spereated, the headers in the csv file have spaces between them, so when referring to them in the insert code I also have to put the spaces in (see 'modeller'). Is there something I can do about this?

3) similar to problem 2, the data, although comma seperated, also has large spaces after it. This means that I have had to increase some of the fields sizes to accomodate this. How can I stop this from happening?

I am using Visual Studio 2003 and SQL Server 2000.

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....