Update: I'm talking to some folks at OOorg Forums http://www.oooforum.org/forum/viewtopic.phtml?t6302 Will keep mailinglist posted on attempts/solutions. -jordan On 5/10/06, Jordan Peacock <hewhocutsdown at gmail.com> wrote: > > 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). > > 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). > > 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. > > -jordan > > > On 5/9/06, Jack Ungerleider < jack at jacku.com> wrote: > > > > On Tuesday 09 May 2006 8:36 pm, Jordan Peacock wrote: > > > I have an OpenDocument spreadsheet file which I've been using as a > > very > > > (very very) basic personal database, and I'm trying to expand some of > > the > > > features (namely being able to bring up different sets of matching > > records > > > at my whim). > > > > > > I've exported the data to a .csv text file but I'm at a loss as to how > > to > > > parse the file to get it into the right format. I've been following > > > > > http://www.webdevelopersnotes.com/tutorials/sql/mysql_course_inserting_data > > >_in_mysql_tables.php3and the sample table works great. The author used > > a > > > .dat text file with the > > > data in this format; > > > > > > INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, > > perks, > > > email) values ("John", "Hagan", "Senior Programmer", 32, 4, 120000, > > 25000, > > > " john_hagan at bignet.com"); > > > INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, > > perks, > > > email) values ("Ganesh", "Pillai", "Senior Programmer", 32, 4, 110000, > > > > > 20000, "g_pillai at bignet.com"); > > > > > > For me to do the same (for thousand of records by hand) is ludicrous > > and > > > error-prone. But I'm not sure how I am to automate the process of > > > separating each record and then pulling it back together into a > > 'INSERT > > > INTO data () values ();' format. > > > > > > Any suggestions as to what to use? > > > > > > -jordan > > > > Use your spreadsheet. If you've got the data organized correctly, and it > > sounds like you do, write a cell formula that results in the INSERT INTO > > ... > > lines you need. Clone the formula to all the rows. Select the results, > > copy > > and paste into your favorite GUI editor, save text file as import.sql. > > Then > > issue the command: > > > > mysql -u username -p database <import.sql > > > > Substitute appropriate values for username and database. Enter the > > password at > > the prompt and viola a database. No muss, no fuss. And the best part is > > if > > you screw up you drop and start all over again. I used to use this > > technique > > all the time in web db app classes where the students started with data > > from > > some source that needed to be organized and then put into MySQL or > > PostgreSQL. > > > > Good Luck! > > Jack > > > > -- > > Jack Ungerleider > > jack at jacku.com > > http://www.jacku.com > > > > _______________________________________________ > > TCLUG Mailing List - Minneapolis/St. Paul, Minnesota > > tclug-list at mn-linux.org > > http://mailman.mn-linux.org/mailman/listinfo/tclug-list > > > > -------------- next part -------------- An HTML attachment was scrubbed... URL: http://mailman.mn-linux.org/pipermail/tclug-list/attachments/20060510/86c298dc/attachment.htm