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

x
?
Solved

Parse File into Database

Posted on 2011-09-29
5
Medium Priority
?
255 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
[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
  • 2
  • 2
5 Comments
 
LVL 52

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 52

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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
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 …
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

715 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