Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Looking to Insert Text File into DB Table with extra columns

Posted on 2003-10-22
6
Medium Priority
?
732 Views
Last Modified: 2013-12-24
Hello,

I won't bother posting my lame attempts at solving this, it will probably just slow things down.  

Its just a regular tab-delimited text file - it looks basically like this:

MODEL    UPC    ISBN
A15    111111    233333
B37    234556    234233

etc.

And the table (currently in Access 2000, moving to SQL 2000 before it goes live probably) has extra columns b/c some text files will have more or less columns.  So its something like:

MODEL   MANUFACTURER   UPC   ISBN   PRICE  (etc)

What I'm trying to do is take the text file and insert all the records into the table, with the fields of course matching up.  What is the best way to do this?  The text files will be replaced on a regular basis, and so I don't want to set it up as a datasource if that's an option.

Thanks,
John
0
Comment
Question by:FogLlama
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Accepted Solution

by:
kjuliff earned 300 total points
ID: 9599474
Just read in the file into a variable using CFFILE.   You can then parse the contents of the variable by searching for the delimiters and Line Feeds.

The outer loop will process each line.
The inner loop will process each field, putting values into variables corresponding to the column names.
At the end of the loop, do an insert.
0
 
LVL 17

Assisted Solution

by:anandkp
anandkp earned 200 total points
ID: 9601008
text.txt
M, 25, akp
F, 20, aish

code:
<cfset targetfile = "#GetDirectoryFromPath(CGI.Path_Translated)#text.txt">

<cfoutput>
  <cffile action="READ" file = "#targetfile#" variable="testVar">
  <cfloop index="rc" list="#testVar#" delimiters="#Chr(10)#">
  <cfset myRecord = listtoarray(rc,",")>
 
  <cfloop index="i" from="1" to="#arraylen(myRecord)#">
     <cfset family = #myRecord[1]#>
      <cfset category = #myRecord[2]#>
      <cfset prod = #myRecord[3]#>
      #myrecord[i]#
     
   </cfloop>      
   <br>
  </cfloop>
</cfoutput>

K'Rgds
Anand
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 9605893
Will your rows have fillers if there is no data.

for ex

MODEL-3425,PRODUCT, UPCNUM,,500.00

so ISBN is blank here since it has a UPC.  would that be the format?

CJ
0
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 

Author Comment

by:FogLlama
ID: 9606824
kjuliff & anandkp,

Great stuff, thanks.

CJ,

No, no fillers for empty data.


I think I am close to a solution, I'll add another comment if I don't have any luck, but so far making some progress.

Thanks,
John
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 9606844
If there are no fillers how do you know what value corresponds to what column?

CJ
0
 

Author Comment

by:FogLlama
ID: 9609359
everyone,

OK, its working now just great.  thanks for helping!

John
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question