Solved

reading a text file and inserting into database

Posted on 2013-06-20
6
561 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
Question by:myselfrandhawa
  • 3
  • 2
6 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
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 15

Author Comment

by:myselfrandhawa
Comment Utility
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_
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 28

Expert Comment

by:Pravin Asar
Comment Utility
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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_
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now