Importing a Tab Delimited File

[ironhide1975]

Does anyone know of a quick walk through or sample (besides Microsoft's page) of how ASP can read a tab delimited file (Classic ASP). I have a tab delimited file, first line is headers, second and on is data, that I need to write a ASP page that will import it into SQL server.

I have tried to import this with SQL manually and it gives me an error saying there is spaces at the end of the first field. Can anyone point me to a place where ASP can just read all the fields of the file and place them in a SQL table?

Any help is greatily appreciated.

 

[DreXor]

see : faq333-3802

just about exactly half way down the page shows how to make a text file based ADO recordset.

it's a little bit of a headache.. doing field types, lengths, formatting, which fields to include, and to exclude, fields that need a name translation from point a to point b

if you're looking for even more insight into the matter i have a poorly documented "virtual" MTS system written up in asp2.0 . it's a beast, but, can take most any (formatted) file input, and run it to a given DB i'd have to dig for it, but if so desired i could wrap it up in a zip pack to tinker with.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[ironhide1975]

If I'm correct there are several ways to get to this text file. Part of the problem is there are no quotes, only tabs.

I have on page from code I grabbed elsewhere that loads the file, however the problem lies in that it grabs the entire first line as the header.

<table width="750" border="0" cellspacing="2" cellpadding="0" height="506">
				<tr>
					<td colspan="2" valign="top" width="746">
						<div align="center">
	<%
	csv_to_read= "Com_Active.txt"
	set fso = createobject("scripting.filesystemobject")
	set act = fso.opentextfile(server.mappath(csv_to_read))
	
	imported_text = act.readline
	'Read the first line of the csv, typically these are colum headings
	
	imported_text = replace(imported_text,chr(13),",")
	'Change the line breaks to commas to delimit through-out
	
	'imported_text = replace(imported_text,chr(34),"")
	'Remove al quotes (If your csv has quotes other than to seperate text
	'You may want to remove this modifier to the imported text
	
	split_text=split(imported_text,",")
	'Split the top line by comma
	
	num_imported=ubound(split_text)+1
	'Count the number of splits and add one for the last element
	
	total_imported_text = act.readall
	'Read the rest of the csv
	
	total_imported_text = replace(total_imported_text,chr(13),",")
	'Change the line breaks to commas to delimit through-out
	
	'total_imported_text = replace(total_imported_text,chr(34),"")
	'Remove al quotes (If your csv has quotes other than to seperate text
	'You may want to remove this modifier to the imported text
	
	total_split_text=split(total_imported_text,",")
	'Split the file up by comma
	
	total_num_imported=ubound(total_split_text)
	'Count the number of splits 
	'This will be the numer of cells in the table
	%>
	<table width="100%" border = "1">
		<tr>
			<%
			for table = 0 to num_imported -1
			'This will create a table cell for each column in the csv
			' (-1 is used because arrays begin with 0)
			%>
			<td class="info" width="<% response.write 100/(num_imported) 'make the cell widths even %>%">
			<b><%= split_text(count) %></b>
			</td>
			<% 
			count=count+1
			next 
			%>
		</tr>
		<tr>
			<%
			'Reset the counter
			count=0
			' This will determine how many rows are in the csv
			for tablea = 0 to (total_num_imported/ (num_imported)-1)
			%>
			<%
			for table = 0 to num_imported -1
			'This will create a table cell for each column in the csv
			' (-1 is used because arrays begin with 0)
			%><td class="eleven" width="<%= 100/(num_imported) %>%">
			<%= total_split_text(count) 
			%>
			</td>
			<% 
			count=count+1
			next ' end of the observation 
			%>
		</tr>
	<% next 'end of the csv %>
	</table>

So when this loads, it shows the first full line as an item and them proceeds to just make each item a seperate line. Am I missing something here?

I also tried this

Set Conn3 = Server.CreateObject("ADODB.Connection")
										Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\20041031_Res_Weekly.txt;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

to which I got the error

'c:\20041031_Res_Weekly.txt' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

/listings3.asp, line 33

 

[Genimuse]

The driver you'd use for a text file DSN is "Microsoft Text Driver" -- maybe that would work for you, though it may only work with DSNs.

 

[ironhide1975]

sooo? what do I change?

 

[DreXor]

ODBC Driver for Text
oConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\somepath\;" & _
"Extensions=asc,csv,tab,txt"
Then specify the filename in the SQL statement:

oRs.Open "Select * From customer.csv", _
oConn, adOpenStatic, adLockReadOnly, adCmdText
Note: If you are using a Tab delimited file, then make sure you create a schema.ini file, and include the "Format=TabDelimited" option.

For more information, see: Text File Driver Programming Considerations

To view Microsoft KB articles related to Microsoft Text Driver, click here

from :

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm

that should help

and the issue with your source above that needs changing, is that you supply the path to the housing folder, not to the file itself, since the filename itself is used in the select statement.

updated :

Set Conn3 = Server.CreateObject("ADODB.Connection")
                                        Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Set TabRS = Conn3.execute("select * from [20041031_Res_Weekly.txt]")

which may or may not work, i typically use the text drivers not OLEDB, the above code is just "formatting" corrections.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

forgot to add, schema.ini 's are not completely necessary, without them most of the time you'll get field1, field2, field3 etc as the fieldnames and handled as all text. which is easy to get around without having to have the headache of setting up a schema for different imports.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[ironhide1975]

Set Conn3 = Server.CreateObject("ADODB.Connection")
                    Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Set TabRS = Conn3.execute("select * from [20041031_Res_Weekly.txt]")

Ok so I did this and I believe I have successfully connected to the text file. Now how do I parse it?

 

[DreXor]

just handle it like a recordset, example if you want to report the data you have in it to an html table use :

Response.Write "<table><tr>" 'starting an output table
If Not RS.EOF AND Not RS.BOF Then
    Dim Counter
    Counter = 1 ' If the recordset is not EOF or BOF then it's at least got one record for the counter
    Response.Write "<td>#</td>" ' This makes the record count column head
    For each Field in RS.Fields
        Response.write "<td>" & Field.Name & "</td>" ' This makes column headers for the Field Names
    Next
    Response.write "</tr>"
    RS.MoveFirst ' Places the Record pointer to the First record, just in case any prior record navigation had taken place
    Do while Not RS.EOF
        Response.Write "<tr>"
        Response.Write "<td>" & Counter & "</td>" ' This Gives Record Number
        For each Field in RS.Fields
            Response.write "<td>" & RS(Field.Name) & "</td>" ' Remember the Value in the recordset parens can be a variable, in this case an array value with the field name.
        Next
        RS.MoveNext ' Advances the Recordset pointer
        Counter = Counter + 1 ' tally off another record count
        Response.Write "</tr>"
    Loop
Else
    Response.write "<td>Empty RecordSet</td>" ' Just in Case the Recordset has no Records
End If
Response.Write "</tr></table>" 'Closing out the table

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[ironhide1975]

Ok I finally got a chance to follow up on this. I was able to get this to ready the tab delimited file, but it only seperates the rows vertically, how do I tell it to seperate items horizontally.

example this one file has NUM_LONG_LAT_DATE_REPORT at the top, and I need it to seperate the data in each row 5 times.