Loading data into mysql

Anon

Hello. Well, I thought I had a solution to the problem I'm having with
the connection between MS Access and MySQL. Since they aren't working for me, I thought I'd take the long route. As if it isn't frustrating
enough that I can't transfer data from Access, I can't load it into mysql
from a .txt file. Why can't I get this to work!? I exported from Access
as tab and comma delimited and neither worked. Then I tried
auto-formatting... no dice. I am positive that my columns in mysql and
access match. Can anyone help?

fleymingmasc

Are you using LOAD DATA INFILE? If so, what is the exact statement you're using?

Anon

LOAD DATA LOCAL INFILE "my filepath" INTO TABLE trips;

It's importing data, but it's not loading into the correct columns. I have 128 rows of data and it's importing 3,028 with many warnings. Most of the values are NULL.

fleymingmasc

Is there an auto_number column involved? That could cause a lot of problems.

Also, try explicitly stating which columns to load into:

LOAD DATA LOCAL INFILE '/filepath'
INTO TABLE trips
(column1, column2, column3 [...])

What are the warnings you're getting?

As a very last resort, you could write a script that would read each line from the file, split it by tabs, and do an INSERT... but on a large database this could take a lot of resources. Better to learn how to use LOAD DATA INFILE and know how to solve the problem next time.

Anon

There is an autonumber column. I need it though. It exists both in my .txt and in mysql.

I don't need to specify the columns because the .txt columns and the columns in mysql are identical.

fleymingmasc

You can't INSERT into an auto_number field. Provided that all your auto_numbers in the original database are sequential (you didn't do any deletes) you can export all the columns in the table except the autonumber (ordering by your auto_number field), and then LOAD DATA INFILE specifying all columns except the auto_number column. If there are no "skips" in the numbering scheme, the numbers should match from one table to the other.

To make sure you haven't deleted rows in Access, check that SELECT MAX(auto_number) equals SELECT COUNT (*) from your table.

If you did deletes in Access, you could be out of luck. Your only hope would be if you can alter the mySQL table so that the identity column is not auto_number, load your data, and then alter it back so that the identity column IS auto_number. I don't know if mySQL will let you do that... check the manual under ALTER TABLE.

Here's one final trick. If you can find all the auto-generated numbers that had been deleted from the Access table, you can alter the Access table so that this column is not auto_number. Add a row for each of these missing numbers, with one of the character fields containing some unique text like "###DELETEME###". When you have completed this, then MAX(auto_number) will equal COUNT(*), and you can use the first solution I outlined above; after you are finished loading the data into mySQL, you can run a DELETE statement where that text field = '###DELETEME###'.

Anon

That seems like it may work... however, I am having trouble exporting from Access to a .txt file without including all the fields. Do you know how to export only certain fields?

fleymingmasc

You could export to an Excel file, then delete the column in question from the Excel document. Then use Excel to export to a text file.

Or you could make a copy of the table and delete the column in question before exporting from Access to txt.

Anon

Thanks. I was hoping that Access would have an export option for certain fields but I am going to make a copy. Thank you for all your help.