Improve company productivity with a Business Account.Sign Up

x
?
Solved

Parse File into Database

Posted on 2011-09-29
5
Medium Priority
?
259 Views
Last Modified: 2012-05-12
I need to read a file into a database with the following structure, years ago I learned how to do xml, and csv, but I can't define this file structure, so I'm floundering a little since I'm a bit out of practice.

filename exported_stocklevels.txt

Item_1 = {
    stock = true,
    on_hand = 0,
    back_order = 5,
    reorder = 2,

}
Item_2 = {
    stock = no,
    on_hand = 0,
    back_order = 3,
    reorder_point = 1,

I know how to update with cfupdate, etc, but I've always struggled with reading in data from files. especially when I can't control the file structure.
0
Comment
Question by:Cmedic_techs
  • 2
  • 2
5 Comments
 
LVL 53

Expert Comment

by:_agx_
ID: 36843937
Since it's not a standard format you'll probably have to parse it manually. So we need to know the precise format of the file. A few questions

- What version of CF - 8 or higher?
- Is that the exact format of the data file including new lines?
- Do you need to extract all of the values? If so are those all of the possible keys? (There's differences between item_1 and 2)
0
 
LVL 3

Expert Comment

by:sajayc
ID: 36845815
Hi,
Looks like you will need to use the 'find' and 'mid' functions to extract each value.  
To program the function, just imagine you are controlling a cursor.

Lets assume you open the text file and call it variable txtfile.
E.g
<cfset start = find("stock = ", txtfile, 0)>
Then you count the characters to extract the value.
<cfset end = find(",", txtfile, start + 8)>
<cfset value = mid(txtfile, start, end - start)>
This should get the first value, then you change the start postion to get the next value.
The code example above is a bit rough, I can help you further if you let me know about the reorder value below.

One thing I noticed is the
reorder = 2,
and
reorder_point = 1,

Are these consistent in the datafile or just an error in your example?

Hope this helps.
0
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 36855241
> use the 'find' and 'mid' functions

You'd be better off using a regex or even list functions. Otherwise even a difference of one space could break the script.  

Here is a rough example using list functions. Each row ie {....} is parsed into a structure dynamically.  So you can reference the values by name like #row.on_hand#, etc... and insert each row of values as you iterate.  Be sure to use structKeyExists if you think some of the keys may not always exist.


<cfsavecontent variable="content">
Item_1 = {
    stock = true,
    on_hand = 0,
    back_order = 5,
    reorder = 2
}
Item_2 = {
    stock = no,
    on_hand = 0,
    back_order = 3,
    reorder_point = 1 }
</cfsavecontent>

<!--- extract the item number before the "{"
<cfset entries = reMatchNoCase("Item_[0-9]+[^}]+}", content)>
<cfdump var="#entries#">
<cfloop array="#entries#" index="entry">
--->
<!--- grab each section up to the closing "}" characters --->
<cfloop list="#trim(content)#" index="entry" delimiters="}">
      <cfset row = {} />
      
      <!--- extract the item number before the "{" --->
      <cfset row.itemNum      = listFirst(listFirst(entry, "{"), "=")>

      <!--- extract everything between "{" and "}" --->
      <cfset attribs  = getToken(entry, 2, "{}")>
      <!--- split the string into pairs ie "stock=true", "on_hand=0" --->
      <cfset props   = listToArray(attribs, ",")>
      <!--- split the pairs into key name and value --->
      <cfloop array="#props#" index="pair">
            <cfset key   = trim(listFirst(pair, "="))>
            <cfset value = trim(listLast(pair, "="))>
            <cfset row[key] = value>
      </cfloop>
      
        <!--- debugging: show all values in this row --->
      <cfdump var="#row#">

         <!--- cfqueryparm omitted for clarity --->        
         <cfquery ...>
              INSERT INTO TableName ( itemNum, on_hand, stock, ...)
              VALUES ( '#row.itemNum#', '#row.on_hand#', '#row.stock#' )
         </cfquery>
</cfloop>      
      
0
 

Author Comment

by:Cmedic_techs
ID: 36891097
It was an error in my example.
the format is consistent. I also dropped the closing brace. my bad. thanks for all the replies, will finish looking them over in a little bit.
0
 

Author Closing Comment

by:Cmedic_techs
ID: 36891391
Thanks guys, I appreciate the quick help.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

608 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