Parse File into Database

Posted on 2011-09-29
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.
Question by:Cmedic_techs
  • 2
  • 2
LVL 52

Expert Comment

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)

Expert Comment

ID: 36845815
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.
<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,
reorder_point = 1,

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

Hope this helps.
LVL 52

Accepted Solution

_agx_ earned 500 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 }

<!--- 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>
        <!--- 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#' )

Author Comment

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.

Author Closing Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math ( While this method is not o…
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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