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/80638875/attachment-0001.htm