Solved

Parse File into Database

Posted on 2011-09-29
5
252 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function 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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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