Thread: [HELP]Importing a to a table from txt file

ok i have a text file of every known plant on the planet and its incorrectly formated and i need to formate it and import it to a table Q_Q.

Ive tried using macros and replace all macros but this SHIT IS TAKING FOREVER

ex im still doing the aa-an's (5000 plants) and ive been at this for hours

the total list contains over 85000 plants
each line needs to have
1. all " replaced with '
so ex

Code:

"TOHI","","Tournefortia hirsutissima","USA (FL), USA+ (PR, VI)","Dicot","Magnoliophyta","Perennial","Vine, Shrub","","","","","","","","","",""

becomes

Code:

'TOHI','','Tournefortia hirsutissima','USA (FL), USA+ (PR, VI)','Dicot','Magnoliophyta','Perennial','Vine, Shrub','','','','','','','','','',''

2. then ',' needs to get replaced with ', '

Code:

'TOHI', '', 'Tournefortia hirsutissima', 'USA (FL), USA+ (PR, VI)', 'Dicot', 'Magnoliophyta', 'Perennial', 'Vine, Shrub', '', '', '', '', '', '', '', '', '', ''

3.then before the symbol name

Code:

insert into recs.plants (AcceptedSymbol, SynonymSymbol, ScientificName, StateandProvince, Category, Division, Duration, GrowthHabit, ActiveGrowthPeriod, Lifespan, AdaptedtoCoarseTexturedSoils, AdaptedtoFineTexturedSoils, AdaptedtoMediumTexturedSoils, DroughtTolerance, MoistureUse, RootDepthMinimuminches, ShadeTolerance, TemperatureMinimumF)values (

needs to get inserted
5.then after the entire thing

Code:

);

needs to be insterted

ive been using a g15 macro for steps 4 and 5 but its going at about 1000 lines a minute with will still take me a few hours to do since i have to break the entire list apart and then move it to a new file and then do all this shit then put it into a query...

for the love of god does anyone know a better way

or have a 16gb machine they could use to quickly do a replace all for me?

btw any plant experts out there that can help me with some of these terms, i need to implement this database into a dynamic lisp simulationor im making....?



.... I HATE PLANTS Q_Q

Its part of a research project im doing for the college, im making a environment/climate simulation program that can generate and simulate all types of environments while keeping track of growth, development and interaction of all organisms (plant and animal) in its current environment...

Through a sophisticated technique the program selects proper organisms for a environment by a number of characteristics (favorable temperature, shade%, water etc) and places them into the env.

ex you want a simple winter/frozen env, this makes sure you wont find any cacti around XD.


IN the final end, this could be used and implemented into a variety of environments (been saying that alot lately) like computer games, (generates random maps for example) or even used by nasa or something.

What are you using for all this? For starters that might helps.

I'm not sure if you'll be able to get much help though, might have to just do it all the way you're doing it. Let's start with the program you doing all this in though and go from there.

Ive tried all the obvious like notepad (goes by each instance), wordpad (replaces every instance), and visual studio 2008 (can do about 5000 lines at a time)

still though the time it takes to add the proper insert into line and add the ); at the end is whats taking the longest.


Heres a few more examples if anyone wants to try it out in a program :3

Code:

"APERA","","Apera","USA (AZ, CA, CO, CT, DE, DC, HI, ID, IL, IN, KY, ME, MD, MA, MI, MO, MT, NV, NJ, NM, NY, OH, OR, PA, TX, UT, VT, WA, WI, WY), CAN (BC, ON)","Monocot","Magnoliophyta","","","","","","","","","","","",""
"APIN","","Apera interrupta","USA (AZ, CA, CO, HI, ID, IL, IN, ME, MA, MI, MO, MT, NV, NJ, NM, NY, OH, OR, UT, WA, WI, WY), CAN (BC, ON)","Monocot","Magnoliophyta","Annual","Graminoid","","","","","","","","","",""
"APSP","","Apera spica-venti","USA (CA, CT, DE, DC, ID, KY, ME, MD, MA, MI, MO, NY, OH, OR, PA, TX, VT, WA), CAN (ON)","Monocot","Magnoliophyta","Annual","Graminoid","","","","","","","","","",""
"APHAN4","","Aphanes","USA (AL, AR, CA, DE, DC, FL, GA, ID, KY, LA, MD, MS, MO, NJ, NY, NC, OK, OR, SC, TN, TX, VA, WA), CAN (BC, NS)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"APAR2","","Aphanes arvensis","USA (CA, DE, ID, NJ, OR, SC, TN, WA), CAN (BC, NS)","Dicot","Magnoliophyta","Annual","Forb/herb","","","","","","","","","",""
"APMI","","Aphanes microcarpa","USA (AL, AR, DE, DC, FL, GA, KY, LA, MD, MS, MO, NY, NC, OK, SC, TN, TX, VA), CAN (BC)","Dicot","Magnoliophyta","Annual","Forb/herb","","","","","","","","","",""
"APIOS","","Apios","USA (AL, AR, CO, CT, DE, DC, FL, GA, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, NE, NH, NJ, NY, NC, ND, OH, OK, PA, RI, SC, SD, TN, TX, VT, VA, WV, WI), CAN (NB, NS, ON, PE, QC)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"APAM","","Apios americana","USA (AL, AR, CO, CT, DE, DC, FL, GA, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, NE, NH, NJ, NY, NC, ND, OH, OK, PA, RI, SC, SD, TN, TX, VT, VA, WV, WI), CAN (NB, NS, ON, PE, QC)","Dicot","Magnoliophyta","Perennial","Vine, Forb/herb","Spring and Summer","Moderate","Yes","Yes","Yes","Low","Medium","8","Tolerant","-23"
"APIUM","","Apium","USA (AZ, CA, CT, FL, ID, IL, LA, MA, MS, MO, NV, NJ, NM, NY, NC, OH, OK, OR, PA, SC, SD, TN, TX, UT, WA, WV), CAN (NS)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"APGR2","","Apium graveolens","USA (AZ, CA, CT, FL, ID, IL, LA, MA, MS, MO, NV, NJ, NM, NY, NC, OH, OK, OR, PA, SC, SD, TN, TX, UT, WA, WV), CAN (NS)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"APGRD","","Apium graveolens var. dulce","USA (AZ, CA, CT, FL, ID, IL, LA, MA, MS, MO, NV, NJ, NM, NY, NC, OH, OK, OR, PA, SC, SD, TN, TX, UT, WA, WV), CAN (NS)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"APLEC","","Aplectrum","USA (AL, AZ, AR, CT, DE, DC, GA, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MS, MO, NJ, NY, NC, OH, OK, PA, SC, TN, VT, VA, WV, WI), CAN (ON, QC)","Monocot","Magnoliophyta","","","","","","","","","","","",""
"APHY","","Aplectrum hyemale","USA (AL, AZ, AR, CT, DE, DC, GA, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MS, MO, NJ, NY, NC, OH, OK, PA, SC, TN, VT, VA, WV, WI), CAN (ON, QC)","Monocot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"APOCY","","Apocynum","USA (AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NF, NT, NS, ON, PE, QC, SK, YT)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"APAN2","","Apocynum androsaemifolium","USA (AL, AK, AZ, AR, CA, CO, CT, DE, DC, GA, ID, IL, IN, IA, KY, ME, MD, MA, MI, MN, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NF, NT, NS, ON, PE, QC, SK, YT)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"APCA","","Apocynum cannabinum","USA (AL, AZ, AR, CA, CO, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NF, NT, NS, ON, QC, SK)","Dicot","Magnoliophyta","Perennial","Forb/herb","Spring and Summer","Moderate","Yes","Yes","Yes","Medium","High","12","Intermediate","-43"
"APFL","","Apocynum ×floribundum","USA (AZ, AR, CA, CO, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OR, PA, RI, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NF, NS, ON, QC, SK)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"AQUIL","","Aquilegia","USA (AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NF, NT, NS, NU, ON, PE, QC, SK, YT)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"AQBR","","Aquilegia brevistyla","USA (AK, MN, MT, SD, WY), CAN (AB, BC, MB, NT, NU, ON, SK, YT)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"AQCA","","Aquilegia canadensis","USA (AL, AR, CT, DE, DC, FL, GA, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MS, MO, NE, NH, NJ, NY, NC, ND, OH, OK, PA, RI, SC, SD, TN, TX, VT, VA, WV, WI), CAN (MB, NB, ON, QC, SK)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"AQFO","","Aquilegia formosa","USA (AK, CA, ID, MT, NV, OR, UT, WA, WY), CAN (AB, BC, YT)","Dicot","Magnoliophyta","Perennial","Forb/herb","Spring and Summer","Short","Yes","No","Yes","Medium","Medium","6","Tolerant","-23"
"AQVU","","Aquilegia vulgaris","USA (CT, GA, IL, IN, IA, KY, ME, MD, MA, MI, NH, NJ, NY, NC, OH, PA, RI, VT, WA, WV, WI), CAN (MB, NB, NF, NS, ON, PE, QC)","Dicot","Magnoliophyta","Perennial","Forb/herb","","","","","","","","","",""
"ARABI","","Arabidopsis","USA (AL, AR, CA, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NH, NJ, NY, NC, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI), CAN (BC, LB, MB, NF, NT, NU, ON, QC, SK), DEN (GL), FRA (SPM)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"ARAR32","","Arabidopsis arenicola","CAN (BC, LB, MB, NF, NT, NU, ON, QC, SK), DEN (GL)","Dicot","Magnoliophyta","Annual","Forb/herb","","","","","","","","","",""
"ARTH","","Arabidopsis thaliana","USA (AL, AR, CA, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NH, NJ, NY, NC, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI), CAN (BC, ON, QC), FRA (SPM)","Dicot","Magnoliophyta","Annual","Forb/herb","","","","","","","","","",""
"ARABI2","","Arabis","USA (AL, AK, AZ, AR, CA, CO, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, LB, MB, NB, NF, NT, NS, NU, ON, QC, SK, YT), DEN (GL)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"ARAL8","","Arabis alpina","USA (KY, ME, MI, NY, TN, VA, WI), CAN (LB, MB, NB, NF, NT, NU, ON, QC, YT), DEN (GL)","Dicot","Magnoliophyta","Perennial","Forb/herb, Subshrub","","","","","","","","","",""
"ARCA","","Arabis canadensis","USA (AL, AR, CT, DE, DC, FL, GA, IL, IN, IA, KS, KY, LA, MD, MA, MI, MN, MS, MO, NE, NH, NJ, NY, NC, ND, OH, OK, PA, RI, SC, SD, TN, TX, VT, VA, WV, WI), CAN (ON, QC)","Dicot","Magnoliophyta","Biennial","Forb/herb","","","","","","","","","",""
"ARDI2","","Arabis ×divaricarpa","USA (AK, CA, CO, ID, IA, ME, MI, MN, MT, NE, NV, NH, NM, NY, ND, OH, OR, SD, UT, VT, WA, WI, WY), CAN (AB, BC, MB, NB, NT, NS, ON, QC, SK, YT)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","Spring and Summer","Short","Yes","No","Yes","High","Low","8","Tolerant","-28"
"ARDR","","Arabis drummondii","USA (AK, AZ, CA, CO, CT, DE, ID, IL, IN, IA, ME, MA, MI, MN, MT, NE, NV, NH, NJ, NM, NY, ND, OH, OR, RI, SD, UT, VT, WA, WI, WY), CAN (AB, BC, LB, MB, NB, NF, NT, NS, ON, QC, SK, YT)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","Spring and Summer","Short","Yes","No","Yes","Low","Medium","6","Intermediate","-38"
"ARGL","","Arabis glabra","USA (AK, AZ, AR, CA, CO, CT, DE, GA, ID, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OR, PA, RI, SD, TN, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NT, NS, ON, QC, SK, YT)","Dicot","Magnoliophyta","Annual, Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHI","","Arabis hirsuta","USA (AK, AZ, AR, CA, CO, CT, GA, ID, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OR, PA, SD, TN, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NT, NS, ON, QC, SK, YT)","Dicot","Magnoliophyta","Annual, Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHIG","","Arabis hirsuta var. glabrata","USA (CA, CO, ID, IL, NV, NM, OR, UT, WA, WY), CAN (AB, BC, YT)","Dicot","Magnoliophyta","Annual, Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHIH","","Arabis hirsuta var. hirsuta","USA (CA, CO, NV), CAN (BC, SK)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHIP","","Arabis hirsuta var. pycnocarpa","USA (AK, AZ, AR, CA, CO, CT, GA, ID, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OR, PA, SD, TN, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, MB, NB, NT, NS, ON, QC, SK, YT)","Dicot","Magnoliophyta","Annual, Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHO2","","Arabis holboellii","USA (AK, AZ, CA, CO, ID, MI, MN, MT, NE, NV, NM, ND, OR, SD, UT, WA, WY), CAN (AB, BC, MB, NT, ON, QC, SK, YT), DEN (GL)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","Spring and Summer","Short","Yes","No","Yes","Medium","Medium","6","Intermediate","-33"
"ARHOC","","Arabis holboellii var. collinsii","USA (AK, ID, MT, NE, ND, SD, WA, WY), CAN (AB, BC, MB, NT, ON, QC, SK, YT)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHOP3","","Arabis holboellii var. pinetorum","USA (AK, AZ, CA, CO, ID, MT, NE, NV, NM, ND, OR, SD, UT, WA, WY), CAN (AB, BC, MB, NT, SK, YT)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARHOR","","Arabis holboellii var. retrofracta","USA (AK, CA, CO, ID, MI, MN, MT, NV, NM, OR, UT, WA, WY), CAN (AB, BC, MB, NT, ON, QC, SK, YT), DEN (GL)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARLA","","Arabis laevigata","USA (AL, AR, CO, CT, DE, DC, GA, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MS, MO, NH, NJ, NY, NC, OH, OK, PA, RI, SC, SD, TN, VT, VA, WV, WI), CAN (ON, QC)","Dicot","Magnoliophyta","Biennial","Forb/herb","","","","","","","","","",""
"ARLAB","","Arabis laevigata var. burkii","USA (KY, MD, NC, PA, TN, VA, WV)","Dicot","Magnoliophyta","Biennial","Forb/herb","","","","","","","","","",""
"ARLAL","","Arabis laevigata var. laevigata","USA (AL, AR, CO, CT, DE, DC, GA, IL, IN, IA, KS, KY, ME, MD, MA, MI, MN, MS, MO, NH, NJ, NY, NC, OH, OK, PA, RI, SC, SD, TN, VT, VA, WV, WI), CAN (ON, QC)","Dicot","Magnoliophyta","Biennial","Forb/herb","","","","","","","","","",""
"ARLY2","","Arabis lyrata","USA (AK, CT, DE, DC, GA, IL, IN, IA, KY, MD, MA, MI, MN, MS, MO, MT, NJ, NY, NC, OH, OK, PA, TN, VT, VA, WV, WI), CAN (AB, BC, MB, NT, ON, QC, SK)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","","","","","","","","","",""
"ARSH2","","Arabis shortii","USA (AL, AR, DC, IL, IN, IA, KS, KY, MD, MI, MN, MO, NE, NY, OH, OK, PA, SD, TN, VA, WV, WI), CAN (ON)","Dicot","Magnoliophyta","Biennial, Perennial","Forb/herb","","","","","","","","","",""
RHY","","Arachis hypogaea","USA (AL, CT, DE, DC, FL, GA, KS, KY, LA, MA, MS, MO, NJ, NC, PA, SC, TN, TX, UT, VA, WV), USA+ (PR, VI)","Dicot","Magnoliophyta","Annual, Perennial","Forb/herb","","","","","","","","","",""
"ARALI","","Aralia","USA (AL, AZ, AR, CA, CO, CT, DE, DC, FL, GA, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY), CAN (AB, BC, LB, MB, NB, NF, NT, NS, ON, PE, QC, SK, YT), FRA (SPM)","Dicot","Magnoliophyta","","","","","","","","","","","",""
"ARHI2","","Aralia hispida","USA (CT, IL, IN, ME, MD, MA, MI, MN, NH, NJ, NY, NC, OH, PA, RI, VT, VA, WV, WI), CAN (LB, MB, NB, NF, NS, ON, PE, QC, SK)","Dicot","Magnoliophyta","Perennial","Subshrub, Forb/herb","","","","","","","","","",""

Originally Posted by draco7891

Where is this table that you're ultimately inserting into? Access? SQL? C?

It might be faster to split the master file into the data sections, and read those each into their own table; you can do that just by loading the split-up file then looping through. Then write up a master insert into the master table (recs.plants by the look of it), using pointers to the data in the individual tables.

I'll bet you could split the master easily by running it first through Excel and then exporting the individual columns as .csv files.

Draco

wow am i stupid, ya just imported it into excell and imported into the Sqldb.

THANK YOU SO MUCH!!!!!!!!!!!! I would have been at that for hours! YOU DA MAN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!