Solved

MYSQL stored procedure import fixed width text file

Posted on 2013-06-17
5
766 Views
Last Modified: 2013-06-18
I am looking to import a large dataset from the IRS located here:
http://www.irs.gov/uac/SOI-Tax-Stats-Exempt-Organizations-Business-Master-File-Extract-(EO-BMF)
at the bottom of the page are file regional files, here is one of them:
http://www.irs.gov/file_source/pub/irs-soi/eo1.zip

The file is an ascii file with fixed width columns as defined here:
Position(s)      Contents

  1   9      Employer Identification Number (EIN).
 10   79      Primary Name of Organization.
 80   114      In Care of Name.
115   149      Street Address.
150   171      City.
172   173      State.
174   183      Zip Code.
184 - 187      Group Exemption Number.
188   189      Subsection Code.
190            Affiliation Code.
191   194      Classification Code(s).
195   200      Ruling Date.
201            Deductibility Code.
202   203      Foundation Code.
204   212      Activity Codes.
213            Organization Code.
214   215      Exempt Organization Status Code (New)
216 - 221      The Advance Ruling process is obsolete as of July 2008. These positions will be blank or zeroed out. For further information, please check the URL below: http://www.irs.gov/charities/charitable/article/0,,id=185568,00.html:
222 - 227      Tax Period.
228            Asset Code.
229            Income Code.
230 - 231      Filing Requirement Code.
232            PF Filing Requirement Code. (New)
233 - 235      Blanks.
236 - 237      Accounting Period.
238 - 250      Asset Amount.
251 - 263      Income Amount.
264      If the Income Amount is negative this contains a negative sign.
265 - 277      Form 990 Revenue Amount
278            If Revenue Amount is negative this contains a negative sign.
279 - 282      National Taxonomy of Exempt Entities (NTEE) Code.
283 - 317      Sort Name (Secondary Name Line).
318            0A - Line Feed

This data is updated monthly and I would like to have a stored procedure that I can run to grab the file and import it to a table with the appropriate columns without having to redefine all the column widths each time.

I am both a novice at stored procedures and have never used Load Data InFile (which is what the other forums suggested).

Could someone show me the template of what the stored procedure should look like so that I can see how to define the different column widths (just show a few and I can figure out the rest).  

What I imagine doing is uploading the files to the server then having a script on my server run that will call the stored procedure with a parameter of the file name to import from (there will be 4 files in total that I will be needing to import).  Since all the files have the identical column width structure and just have different file names that should allow me to repeat this processes as often as needed.
0
Comment
Question by:yoshcomputers
  • 3
5 Comments
 

Author Comment

by:yoshcomputers
ID: 39254431
Here is the SQL that works...and does the fixed width that I need.  I just now want to store this a as a store procedure where I can just change the filename.

LOAD DATA LOCAL INFILE 'C:/Users/Yosh/Downloads/eo1/eo1.lst' INTO TABLE a_irs
(@var1)
SET
irs_ein=SUBSTR(@var1,1,9),
irs_name=SUBSTR(@var1,10,70),
irs_address=SUBSTR(@var1,115,35),
irs_city=SUBSTR(@var1,150,22),
irs_state=SUBSTR(@var1,172,2),
irs_zipcode=SUBSTR(@var1,174,10),
irs_groupexemptionnumber=SUBSTR(@var1,184,4),
irs_subsectioncode=SUBSTR(@var1,188,2),
irs_affiliationcode=SUBSTR(@var1,190,1),
irs_classificationcode=SUBSTR(@var1,191,4),
irs_exemptstatuscode=SUBSTR(@var1,214,2),
irs_nteecode=SUBSTR(@var1,279,4),
irs_sortname=SUBSTR(@var1,283,35);
0
 

Author Comment

by:yoshcomputers
ID: 39254704
I found out in Mysql you cannot have LOAD DATA INFILE in a stored procedure
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255563
this (brief) page discusses a workaround that appears relevant
http://kedar.nitty-witty.com/blog/using-load-data-infile-with-stored-procedure-workaround-mysql
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39256305
As you already found out you CAN'T place the LOAD statement in a stored procedure. However you can place it in a script file, i.e. load_file.sql, and then have a job, either cron task on Linux or Sheduled Task on WIndows, to execute the script file. You can do that directly using MySQL if you can execute the script on the server or you can have a script in VBscrip, JavaScript or whatever language you want to execute it. The simplest way is to use MySQL directly and the syntax is simple:

mysql -h "server-name" -u "user" "-pXXXXXXXX" "database-name" < "load_file.sql"

Open in new window

0
 

Author Closing Comment

by:yoshcomputers
ID: 39256486
I appreciate the example, though it did leave something to the imagination to fill is some details on implementation.  It certainly got me on the right direction.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now