locked
Help with CSV file import into access database RRS feed

  • Question

  • User-1341604935 posted
    Hey guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>I have a page setup that lets a user upload a CSV then the csv is then loaded into an access database table<o:p></o:p>Problem I'm having is a Colum in the csv as
    Appliance ID
    1641000001<o:p></o:p>
    Now when the Appliance ID'z are less than 199999999 the enter fine into the database, when they are more for example
    Appliance ID
    2903000187<o:p></o:p>
    They do not enter the table,<o:p></o:p>After investigating with a scheme.ini file, access on the import recognises the Colum as a "float" and floats don’t seem to allow numbers over 19999999, Furthermore it ignores my selected field type inside of access<o:p></o:p>

    I have the field type in access set to double, which i can manual type the larger number in, tho on the insert it recognises it as Float so ignores the larger numbers

    I guess on the import it only reads the first 10 lines and assumes "float" would be fine, than errors out on the larger numbers. is there anyways to force it on the sql side on the insert?

    here is my code:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim fileuploadage As String

    fileuploadage = Label1.Text

    Dim path As String = Server.MapPath("Data")

    Dim connect As String

    connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|db1.mdb"

    Dim conn As New OleDbConnection(connect)

    'Dim query As String = "INSERT INTO Person2 (FirstName, SecondName)" & _

    Dim query As String = "INSERT INTO testasset ([RECORD No], [APPLIANCE ID], [APPLIANCE NAME], SITE, LOCATION, [DATE], [TIME], [RETEST PERIOD (Mths)], [RETEST DATE], [USER ID], [TEST PRICE CODE], [TEST CODES], [FAIL REASON], [REPAIR CODE 1], [REPAIR UNIT 1], [REPAIR AMOUNT 1], [REPAIR CODE 2], [REPAIR UNIT 2], [REPAIR AMOUNT 2], [REPAIR CODE 3], [REPAIR AMOUNT 3], [REPAIR UNIT 3], VOLTAGE, [VISUAL PASS], [LEAD PASS], [CONTINUITY READING], [CONTINUITY LIMIT], [CONTINUITY OUTPUT], [CONTINUITY PASS], [INSULATION LIMIT], [INSULATION READING], [INSULATION OUTPUT], [INSULATION PASS], [LEAKAGE READING], [LEAKAGE LIMIT], [LEAKAGE OUTPUT], [LEAKAGE PASS], [TOUCH LEAKAGE READING], [TOUCH LEAKAGE LIMIT], [TOUCH LEAKAGE OUTPUT], [TOUCH LEAKAGE PASS], [SUB LEAKAGE READING], [SUB LEAKAGE LIMIT], [SUB LEAKAGE OUTPUT], [SUB LEAKAGE PASS], [FLASH READING], [FLASH LIMIT], [FLASH OUTPUT], [FLASH PASS], [LOAD READING], [LOAD PASS], [WORK ORDER #])" & _

    "SELECT * FROM [Text;DATABASE=" & path & ";HDR=yes].[" + fileuploadage + "]"

    Dim cmd As OleDbCommand = New OleDbCommand(query, conn)

    conn.Open()

    conn.GetSchema()

    cmd.ExecuteNonQuery()

    conn.Close()

    Response.Write(
    "Records Created Successfully")

    Button1.Visible = True

    End Sub

    Monday, December 22, 2008 5:59 AM

Answers