How to import tab delimited file into Access

Discussion in 'Classic ASP' started by bamodeo, Dec 28, 2003.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. bamodeo

    bamodeo

    I have a proprietary system from which I can export data into a tab delimited file. I want to create an Active Server Page where the users can select and upload the file, have the data loaded into a table in a Microsoft Access database and finally insert and/or update records into the master table.

    I already have the portion written that allows the user to upload the file (using ASPUpload). I also have the code to insert and/or update the master table. I just can't figure out how to load the contents of the tab delimited into a table in Access. Yes, I know how to do it manually, but I need help with code to do this automatically. I can either have the table already there or import the data into a new table--whichever is easier to code.

    OR...can the tab delimited file be opened as a data source? If so, how is that done? I don't need to store the data from the tab delimited file, I just need to update the master table in the Access database.

    Thanks in advance for any help.
     
    bamodeo, Dec 28, 2003
    #1
  2. Bruce

    Bruce DiscountASP.NET Staff

    I believe you can open a text file as a data source. see http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForTextFiles


    As an alternative (not the prettiest way), you can open the file using file class and read it line by line.

    quote:Originally posted by bamodeo

    I have a proprietary system from which I can export data into a tab delimited file. I want to create an Active Server Page where the users can select and upload the file, have the data loaded into a table in a Microsoft Access database and finally insert and/or update records into the master table.

    I already have the portion written that allows the user to upload the file (using ASPUpload). I also have the code to insert and/or update the master table. I just can't figure out how to load the contents of the tab delimited into a table in Access. Yes, I know how to do it manually, but I need help with code to do this automatically. I can either have the table already there or import the data into a new table--whichever is easier to code.

    OR...can the tab delimited file be opened as a data source? If so, how is that done? I don't need to store the data from the tab delimited file, I just need to update the master table in the Access database.

    Thanks in advance for any help.
    </blockquote id="quote"></font id="quote">
     
    Bruce, Dec 28, 2003
    #2
  3. bamodeo

    bamodeo

    Bruce, thanks very much for the help! I was able to open the file as a recordset. I know many of the expert coders out there wouldn't need this, but because I am sure there are others (like me) that would benefit, here is the code:

    <%
    Option explicit

    ' This program opens a tab delimited file and displays the contents in a table.
    ' In order to process the file, a Schema.ini file must exist in the same directory
    ' as the data file. The details about what is required in the Schema.ini file
    ' can be found at:
    ' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
    '
    ' The data file for this example is "data1.txt," has three columns and has column
    ' headers. The Schema.ini file is as follows:
    ' [data1.txt]
    ' Format=TabDelimited
    ' ColNameHeader=True
    ' MaxScanRows=0
    ' CharacterSet=ANSI


    Dim oConn, oRs, myvalue, count, field

    ' Create database objects

    Set oConn = server.CreateObject("ADODB.Connection")
    Set oRS = createObject("ADODB.Recordset")

    ' Open connection and create recordset

    oConn.Open _
    "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=c:\InetPub\wwwroot\tf\;" & _
    "Extensions=asc,csv,tab,txt"

    oRs.Open "Select * From data1.txt", oConn

    ' Initialize table and header row
    response.write("<html><table border=1 width = ""100%"">")
    response.write("<tr bgcolor=""#C0C0C0"">")

    ' Write field names

    for each field in oRS.fields
    response.write("<td> *" & field.name & "* </td>")
    next
    Response.Write("</tr>")

    ' Write data records, alternating the background color of each row

    Do While Not oRS.eof

    count = count + 1
    if count mod 2 = 0 then
    response.write("<tr bgcolor=""#CCCCFF"">")
    else
    response.write("<tr bgcolor=""#F3F4F8"">")
    end if

    for each field in oRS.fields
    if ( isnull(field.value) or isempty(field.value) ) then
    myvalue = ""
    else
    myvalue = field.value
    end if
    response.write ( "<td>" & myvalue & "</td>")
    next

    Response.Write("</tr>")
    oRS.movenext

    Loop

    ' Close table, display number of records and close HTML

    Response.Write("</table>")
    Response.Write("
    Total records=" & CStr(count) & "
    ")
    Response.Write("</body></html>")

    %>

    Now that I can read the data, I can update my master table. Thanks again for all of your help!


    quote:Originally posted by bruce

    I believe you can open a text file as a data source. see http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForTextFiles


    As an alternative (not the prettiest way), you can open the file using file class and read it line by line.

    quote:Originally posted by bamodeo

    I have a proprietary system from which I can export data into a tab delimited file. I want to create an Active Server Page where the users can select and upload the file, have the data loaded into a table in a Microsoft Access database and finally insert and/or update records into the master table.

    I already have the portion written that allows the user to upload the file (using ASPUpload). I also have the code to insert and/or update the master table. I just can't figure out how to load the contents of the tab delimited into a table in Access. Yes, I know how to do it manually, but I need help with code to do this automatically. I can either have the table already there or import the data into a new table--whichever is easier to code.

    OR...can the tab delimited file be opened as a data source? If so, how is that done? I don't need to store the data from the tab delimited file, I just need to update the master table in the Access database.

    Thanks in advance for any help.
    </blockquote id="quote"></font id="quote">
    </blockquote id="quote"></font id="quote">
     
    bamodeo, Dec 29, 2003
    #3
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page