I've spent several weeks trying to get a 442,000 row spreadsheet into mysql
and everything I tried bombed.
About a week ago I found some code that almost worked as it read the
spreadsheet and created a SQL insert statement, but it only read about
2/3rds of the records.
Got through debugging it today and now I can process all of the records so
I'm going to post it here if it will help anybody.
I tried to find the original link so that I could give credit to the person
that developed it (I had to make a few changes for it to work on that big of
a table), but I couldn't find the link after looking at a lot of pages.
Anyway, it works good now.
Once you get it into the sql format, you simply enter SOURCE
c:/sumcd/sql/lca_import.sql AFTER first selecting the appropriate database
You have to do it this way because no matter how many times you try to
adjust the file sizes in PhpMyAdmin, it just doesn't seem to want to work
and always errors out.
**start of program**
*PARAMETERS mdbf
mdbf= "lca_import"
mdbffile = mdbf + ".dbf"
* where you want mysqldump files to go
mexportfile = "c:\sumcd\sql\" + mdbf + ".sql"
* where dbf files are
SET DEFAULT TO c:\sumcd
IF !FILE(mdbffile)
messagebox("not a valid file")
CANCEL
ENDIF
set century on
set hours to 24
mjunk = 0
*
* open/create file to hold sql commands
* target is a linux box so only lf "chr(10)" at end of line
*
*delete sql dump file if exists (important)
DELETE FILE &mexportfile
IF FILE(mexportfile) && Does file exist?
mimporthandle = FOPEN(mexportfile,11) && If so, open write only
ELSE
mimporthandle = FCREATE(mexportfile) && If not, create it
ENDIF
IF mimporthandle < 1
messagebox("could not open output file")
CANCEL
ENDIF
use &mdbf
set safety off
*remove deleted records
pack
go top
*
* setup array to hold field attributes
*
mfield_cnt = AFIELDS(marray) && Create array
*
* Create text to build create table commands
*
mtext = "DROP TABLE IF EXISTS `" + mdbf + "`;" + chr(10)
mjunk = FWRITE(mimporthandle, mtext)
mtext = "CREATE TABLE IF NOT EXISTS `" + mdbf + "`(" + chr(10)
mjunk = FWRITE(mimporthandle, mtext)
FOR nCount = 1 TO mfield_cnt
mtext = ""
mtext = "`" + marray(nCount,1) + "` " && field name
DO CASE
CASE marray(nCount,2) = "C"
mtext = mtext + "varchar(" + alltrim(str(marray(nCount,3))) + ") "
CASE INLIST(marray(nCount,2), 'N', 'F', 'B')
mtext = mtext + "decimal(" + alltrim(str(marray(nCount,3))) + "," +
alltrim(str(marray(nCount,4))) + ") "
CASE marray(nCount,2) = "D"
mtext = mtext + "date "
CASE marray(nCount,2) = "L"
mtext = mtext + "tinyint(1) "
CASE marray(nCount,2) = "I"
mtext = mtext + "int(" + alltrim(str(marray(nCount,3))) + ") "
CASE marray(nCount,2) = "T"
mtext = mtext + "datetime "
CASE marray(nCount,2) = "M"
mtext = mtext + "text "
OTHERWISE
mtext = mtext + "unknown data type "
ENDCASE
if !marray(nCount, 5)
mtext = mtext + "NOT NULL "
endif
if nCount < mfield_cnt
mtext = mtext + "default '" + marray(nCount,9) + "'," + chr(10)
else
mtext = mtext + "default '" + marray(nCount,9) + "'" + chr(10)
endif
mjunk = FWRITE(mimporthandle, mtext)
ENDFOR
*
* final line for create table section
*
mtext = ")ENGINE=MyISAM COMMENT='" + mdbf + "';" + chr(10) + chr(10)
mjunk = FWRITE(mimporthandle, mtext)
go top
*
* start data loading section
*
do while !eof()
*
*beginning of line
*
mtext = "INSERT INTO " + mdbf + " VALUES ("
FOR nCount = 1 TO mfield_cnt
*
*build values
*
if nCount > 1 && don't put a comma in the first time
mtext = mtext + ", "
endif
DO CASE
CASE marray(nCount,2) = "C" && characters
if !isnull(&marray(nCount,1))
mtext = mtext + '"' + strtran(alltrim(&marray(nCount,1)), '"', '"') + '"'
else
mtext = mtext + '"NULL"'
endif
CASE INLIST(marray(nCount,2), 'N', 'F', 'B') && numeric,float,double
if !isnull(&marray(nCount,1))
mlen = marray(nCount,3)
mdec = marray(nCount,4)
mtext = mtext + "'" + alltrim(str(&marray(nCount,1),mlen,mdec)) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "I" && Integer
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + alltrim(str(&marray(nCount,1))) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "D" && date
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + substr(dtos(&marray(nCount,1)), 1, 4)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 5, 2)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 7, 2) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "T" && date time
if !isnull(&marray(nCount,1))
mtext = mtext + "'" + substr(dtos(&marray(nCount,1)), 1, 4)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 5, 2)
mtext = mtext + "-" + substr(dtos(&marray(nCount,1)), 7, 2)
mtext = mtext + " " + substr(ttoc(&marray(nCount,1)), 12, 8) + "'"
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "L" && logical
if !isnull(&marray(nCount,1))
if (&marray(nCount,1))
mtext = mtext + "'1'"
else
mtext = mtext + "'0'"
endif
else
mtext = mtext + "'NULL'"
endif
CASE marray(nCount,2) = "M" && memo
if !isnull(&marray(nCount,1))
if memlines(&marray(nCount,1)) = 0
mtext = mtext + "''"
else
STORE MEMLINES(&marray(nCount,1)) TO gnNumLines
mtext = mtext + "'"
FOR gnCount = 1 TO gnNumLines
mtext = mtext + strtran(alltrim(MLINE(&marray(nCount,1), gnCount)), "'",
"\'")
NEXT
mtext = mtext + "'"
endif
else
mtext = mtext + "'NULL'"
endif
OTHERWISE
mtext = mtext + "unknown data type "
ENDCASE
if isnull(mtext)
set step on
endif
*
*end of data load line
*
ENDFOR
mtext = mtext + ");" + chr(10)
mjunk = FWRITE(mimporthandle, mtext)
skip
enddo
use
fclose(mimporthandle)
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
