Solved

reading a text file and inserting into database

Posted on 2013-06-20
6
604 Views
Last Modified: 2013-07-02
What is the best possible way to read the text file which has around 10000 records and each column is separated by tab or comma and has 10 columns for every single row

what is the best possible way to do it in coldfusion
0
Comment
  • 3
  • 2
6 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39263719
The fastest way is letting your db handle the import. It's much faster than using list functions and inserting via cfloop.

* BULK INSERT - MS SQL
* LOAD DATA - MySQL

[Edit]

MS SQL Example
BULK INSERT YourTable
   FROM 'c:\some\path\yourImportFile.txt'
   WITH
     (
        FIELDTERMINATOR ='\t',
        ROWTERMINATOR = '\r\n',
      );

MySQL Example
LOAD DATA INFILE 'c:\some\path\yourImportFile.txt'
   INTO TABLE YourTable
   FIELDS TERMINATED BY '\t'
   LINES TERMINATED BY '\r\n'
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 39264917
but what if i needs through a way in coldfusion, i know that will far less slower but right now that may serve the purpose
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39265765
Both can be used from ColdFusion.  It's no different than any sql command.

If you must parse it manually, there's plenty of examples on parsing CSV files.  The logic is exactly the same for tab delimited files.

* Treat it as a list delimited by new chr(10) chr(13)
* As you loop, split each line into columns using list functions, delimiter "," or chr(9)
* Insert the values into the db

http://www.experts-exchange.com/searchResults.jsp?searchTerms=ColdFusion+loop+CSV&searchType=10
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 28

Expert Comment

by:Pravin Asar
ID: 39266827
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 39266865
Thanks Guys, I know parsing of text files but i was acquiring info as to how we can do better in coldfusion like fast processing rather than depending upon list
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39267076
Well like I said above, the fastest method is let the db do the importing.  Both commands can be used in a cfquery.  I do it all the time.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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