For the record, Steve Swantz & Cayfordhad it right - I didn't have the rights to use the LOAD INFILE command. That is working perfectly now, but it doesn't error check for me; I've got the perl script working as well and that does check (at least that there's something in each column).
<br><br>I've discovered a new problem however; OpenOffice, when saving to csv (tab or comma separated) skips over blank entries. As I see it, there are two ways to solve this; somehow make it add blank entries to the tab delimited file (as "" maybe?) or to add a question mark to every blank cell and import that (which I would be ok with; it would make searching for fields that I haven't checked easier).
<br><br>So far, OO.org tutorials and web searches are coming up blank. But the MySQL end is working beautifully now, and the perl script sets things up just right! I modified it a couple times, to try and get the hang of what was going on; thanks a lot Steve.
<br><br> -jordan<br><br><div><span class="gmail_quote">On 5/9/06, <b class="gmail_sendername">Jack Ungerleider</b> <<a href="mailto:jack@jacku.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
jack@jacku.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On Tuesday 09 May 2006 8:36 pm, Jordan Peacock wrote:<br>> I have an OpenDocument spreadsheet file which I've been using as a very<br>> (very very) basic personal database, and I'm trying to expand some of the<br>> features (namely being able to bring up different sets of matching records
<br>> at my whim).<br>><br>> I've exported the data to a .csv text file but I'm at a loss as to how to<br>> parse the file to get it into the right format. I've been following<br>> <a href="http://www.webdevelopersnotes.com/tutorials/sql/mysql_course_inserting_data" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://www.webdevelopersnotes.com/tutorials/sql/mysql_course_inserting_data</a><br>>_in_mysql_tables.php3and the sample table works great. The author used a<br>> .dat text file with the<br>> data in this format;
<br>
><br>> INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks,<br>> email) values ("John", "Hagan", "Senior Programmer", 32, 4, 120000, 25000,<br>> " <a href="mailto:john_hagan@bignet.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
john_hagan@bignet.com</a>");<br>> INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks,<br>> email) values ("Ganesh", "Pillai", "Senior Programmer", 32, 4, 110000,
<br>> 20000, "<a href="mailto:g_pillai@bignet.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">g_pillai@bignet.com</a>");<br>><br>> For me to do the same (for thousand of records by hand) is ludicrous and
<br>> error-prone. But I'm not sure how I am to automate the process of
<br>> separating each record and then pulling it back together into a 'INSERT<br>> INTO data () values ();' format.<br>><br>> Any suggestions as to what to use?<br>><br>> -jordan
<br><br>Use your spreadsheet. If you've got the data organized correctly, and it<br>sounds like you do, write a cell formula that results in the INSERT INTO ...<br>lines you need. Clone the formula to all the rows. Select the results, copy
<br>and paste into your favorite GUI editor, save text file as import.sql. Then<br>issue the command:<br><br>mysql -u username -p database <import.sql<br><br>Substitute appropriate values for username and database. Enter the password at
<br>the prompt and viola a database. No muss, no fuss. And the best part is if<br>you screw up you drop and start all over again. I used to use this technique<br>all the time in web db app classes where the students started with data from
<br>some source that needed to be organized and then put into MySQL or<br>PostgreSQL.<br><br>Good Luck!<br>Jack<br><br>--<br>Jack Ungerleider<br><a href="mailto:jack@jacku.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
jack@jacku.com</a><br><a href="http://www.jacku.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://www.jacku.com</a><br><br>_______________________________________________<br>TCLUG Mailing List - Minneapolis/St. Paul, Minnesota<br><a href="mailto:tclug-list@mn-linux.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
tclug-list@mn-linux.org</a><br><a href="http://mailman.mn-linux.org/mailman/listinfo/tclug-list" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://mailman.mn-linux.org/mailman/listinfo/tclug-list</a><br></blockquote></div><br>