The simplest way to insert tab delimitedvfile into a mysql table?

[dv8er88]

I have a tab the limited flat file (.txt)

If I open it in Excel I see it goes from row A to AU.
I have set up a table in mySQL with row A to AU.

I have successfully used PHPmyadmin's insert data from file feature and specified the delimiter as /t.

It imported the data fine. But I would like to know a way for my customer to be able to do this by running a cold fusion script and not having to use PHPmyadmin. Is there a simple way once I have the text file on the server.

Thanks guys

 

[imstillatwork]

build a form for them to upload the txt file.

from there you can open the file, loop through it's rows, and insert data to the DB.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[dv8er88]

Okay sorry for being such an amateur but I have a few questions.

What you mean by open the file.

Don't I need to specify the room name that I want to insert the data from in the text file.

I mean how do I loop through the rows in a tab delimited file.
Are there row names?

And don't I need to specify the type of delimiter somewhere. Like \t

Is there a cold fusion tag that does this?

Do you have a small sample of code.

Thanks so much for your help.

 

[imstillatwork]

Okay sorry for being such an amateur but I have a few questions.

:: OK.

What you mean by open the file.

:: Use cffile to get the file contents

Don't I need to specify the room name that I want to insert the data from in the text file.

:: ??? Do what ???

I mean how do I loop through the rows in a tab delimited file.

:: with the carriage return or new line character #chr(13)# I think...

Are there row names?

:: No

And don't I need to specify the type of delimiter somewhere. Like \t

:: #chr(13)# for rows, #chr(9)# is tab I believe

Is there a cold fusion tag that does this?

:: maybe, but you should know the code.

Do you have a small sample of code.

:: Um.... probably won't work, but here is the idea...

<!--- get tab delimited file... --->
<cffile action="READ" variable="tabFile" file="c:\yourpath\tabfile.txt">

<!--- how many rows? --->
<cfset rowCount = listLen(tabFile,chr(13))>

<cfoutput>
<cfloop from="1" to="#rowCount#" index="i">
	<!--- set current row to simple variable --->
	<cfset thisRow = listGetAt(tabFile,i,chr(13))>
	Row #i# Data as a string (#thisRow#)<br/>
	<!--- now loop the row --->
	<cfloop from="1" to="#listLen(thisRow,chr(9))#" index="r">
		&nbsp; &nbsp; Field #r# = #listGetAt(thisRow,r,chr(9))#<br/>
	</cfloop>
	<hr>
</cfloop>

</cfoutput>

Thanks so much for your help

:: Yup.

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

actually, if your just going to do an insert, make it even simpler.

<!--- get tab delimited file... --->
<cffile action="READ" variable="tabFile" file="c:\yourpath\tabfile.txt">

<!--- how many rows? --->
<cfset rowCount = listLen(tabFile,chr(13))>

<cfloop from="1" to="#rowCount#" index="i">
	<!--- set current row to simple variable --->
	<cfset thisRow = listGetAt(tabFile,i,chr(13))>
	
	<!--- make a query... --->
	<cfquery datasource="">
		INSERT INTO table(field1,field2,field3)VALUES(
			<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#listGetAt(thisRow,1,chr(9))#">
			,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#listGetAt(thisRow,2,chr(9))#">
			,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#listGetAt(thisRow,3,chr(9))#">
		)
	</cfquery>
</cfloop>

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!