two problems when importing MSSQL to filePro

[Richard Hane]

Wally,
 
You the man!  I don't know why I didn't think of that.  Maybe because I am rapidly approaching 65.
 
I set the variable to 30,* then checked for E-1, E-2 and E-3, set the appropriate formulas and bingo the value are correct.
 
Once again,
Thanks Wally.
 
Rick Hane
 
 

________________________________
 From: Wally Turnbull <wally at tbull.com>
To: Richard Hane <yoresoft at sbcglobal.net> 
Sent: Wednesday, May 8, 2013 2:57 PM
Subject: Re: two problems when importing MSSQL to filePro
  

Rick,

There has to be a more natural solution but I think you might be able to import that field as text rather than a number and parse it.  

Look for the letter E indicating scientific notation using CO (contains) or other methods.  If it contains scientific notation then separate the numerical part, pick up the value after the dash and manipulate the numerical part.

Wally


On May 8, 2013, at 3:34 PM, Richard Hane wrote:


Problem number 1 solved.  For future reference for anyone doing low level import of MSSQL dates, their format is yyyy-mm-dd.
A simple set of mid strings solved the problem.  Dates now work.

Problem 2 is still out there.  It is called a 'Scientific Notation'.  Only about 3% of the 18,000 BOMs have this problem.  If I could ID them I could do a work around.  The problem is how to do this.

Help,

Rick Hane


----- Forwarded Message -----
From: Richard Hane <yoresoft at sbcglobal.net>
To: fplist <filepro-list at lists.celestial.com> 
Cc: Rick Hane <rhane at deluxestitcher.com> 
Sent: Wednesday, May 8, 2013 10:11 AM
Subject: two problems when importing MSSQL to filePro


Hopefully, there are others our there who have imported data from MSSQL to filePro odbc.

If you remember I posted a couple of the fpodbc processes I wrote to import data from our ERP system (written in MSSQL).  This has now grown to over 15 processes that run each night as a scheduled task.

Today I have found a couple of problems that I need help on.

1. I do not seem to be able to get a date mm/dd/yyyy to come over.  It leaves the field blank.  Problem may be that the varaible I use is set to 10,mdyy/.  I don't recall the MSSQL date format to try and work around this problem.

2. This one is a little harder.  I am importing our Bill of Materials (BOM) file.  No problem with any of the data except in the qty used  field. From what I can see by looking at the raw SQL data, the factors I have a problem with would look like 6.2499999999999 E-2.  If the factor is .0624999999999 with out the E-2 then no problem.  It appears the E-2 means move the decimal left two places.  When you do an odbc import in to Excel the value are correct.  Obviously Excel knows how to handle this.  Do we have anything that can handle this.

One other small problem.  When testing a process I sometimes have to run it twice as the first time processing says it can not make the ODBC connection.  Start it again and all is fine.  I see no reason why.

Thank you in advance
Rick Hane
Controller
Deluxe Stitcher Company Inc
ISP Stitching Products
http://www.deluxestitcher.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130508/b8a850db/attachment.html 
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130508/9ef8510f/attachment.html 
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130508/d20630c1/attachment.html