Solved

Parse File into Database

Posted on 2011-09-29
5
253 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 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 }
</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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …

738 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