LOAD DATA INFILE - I'm stumped

[amberdevo]

I am able to extract data from our library database with specialized tools developed by the vendor. I can extract to a tab delimited file, csv, etc. I extract titles, authors, purchase orders and that kind of stuff. The files are created using wordpad, notepad (windows system)

However, if I extract to a csv file, the last data field has a comma at the end. For example:

PO-123,QA 123,Learning MySQL, Miller, John,

I can't figure out how to load the file to a table and take into account the comma at the end.

If I create a tab delimited file, it appears a tab is placed at the end of the line but I can't actually see it, I'm assuming since a comma is placed at the end of a CSV file. I tried loading using various lines terminated by variations and nothing with no luck, except if I don't get a syntax error the data is whackie, and not in the correct place.

I have attempted to load the files about 22 thousand times now, with no luck.
My first field in the table is the primary key auto_increment. I added NULL to the front of every line, but it doesn't appear to work as expected.

I have spend many hours searching websites for the answer. I just don't understand how to load a file so it takes into account that the first column in the table is a primary key with auto_increment and the extra tab or comma at the end of each line.

Thanks.

[nomad]

I am able to extract data from our library database with specialized tools developed by the vendor. I can extract to a tab delimited file, csv, etc. I extract titles, authors, purchase orders and that kind of stuff. The files are created using wordpad, notepad (windows system)

However, if I extract to a csv file, the last data field has a comma at the end. For example:

PO-123,QA 123,Learning MySQL, Miller, John,

I can't figure out how to load the file to a table and take into account the comma at the end.

If I create a tab delimited file, it appears a tab is placed at the end of the line but I can't actually see it, I'm assuming since a comma is placed at the end of a CSV file. I tried loading using various lines terminated by variations and nothing with no luck, except if I don't get a syntax error the data is whackie, and not in the correct place.

I have attempted to load the files about 22 thousand times now, with no luck.
My first field in the table is the primary key auto_increment. I added NULL to the front of every line, but it doesn't appear to work as expected.

I have spend many hours searching websites for the answer. I just don't understand how to load a file so it takes into account that the first column in the table is a primary key with auto_increment and the extra tab or comma at the end of each line.

Thanks.

In Mysql I would first make a db. Make tables which has all fields that you want.
Once the db is created select your Table. From there Select the Insert tab. You should see all your fields for that Table. Notice the Import Tab on the top right. Select it
You should see a File to import. Select the Browser and find your CSV file.
Next you will see Format of imported Files.
Select CSV button.
A list of CSV options will appear.
Fields terminated by should be;
You might have to play with the settings to get it right. If the data is in the wrong place you will need to truncate the table. Then go back and try the other settings.

Note your CSV file the data will have to look like this
PO-123,QA 123,Learning MySQL, Miller, John;
Notice the ; this is the terminated field.
Hopefully your files are not to big. If you have Excel and know how to use it I would import all my data into Excel. Call the comma as col.
Replace all the comma and the end with ;.
Make a new row for your PK and leave it black for Null.
export the data to a CSV file.

good luck
nomad

[nomad]

In Mysql I would first make a db. Make a table which has all your fields that you need.
Once the db is created select your Table. From there Select the Insert tab. You should see all your fields for that Table. Notice the Import Tab on the top right. Select it
You should see a File to import. Select the Browser button and find your CSV file.
Next you will see Format of imported Files.
Select CSV button.
A list of CSV options will appear.
Fields terminated by should be ;
You might have to play with the settings to get it right You will need the comma , . If the data is in the wrong place you will need to truncate the table. Then go back and try the other settings.

Note your CSV file the data will have to look like this
PO-123,QA 123,Learning MySQL, Miller, John;
Notice the ; this is the terminated field.
Hopefully your files are note to big. If you have Excel and know how to use it I would import all my data into Excel. Call the comma as col.
Replace all the comma and the end with ;. export the data to a CSV file.

good luck
nomad

please disregard this one. I was updating the info in the other message. Made this one by mistake

[nomad]

Once you have your table I think you can get your PK set up. I will have to look that one up for you. Or you can set it up in Excell with a number.

nomad

[Atli]

You can choose the columns you want to insert the data into.
By that I mean.

Say I have this data in a file:
(Note that the line numbers are not a part of it!)

var11, var12, var13
var21, var22, var23
var31, var32, var33
 

And I vant to insert it into this database:

CREATE TABLE something
(
  ID SERIAL Primary key,
  Var1 VARCHAR(255) NOT NULL,
  Var2 VARCHAR(255) NOT NULL,
  Var3 VARCHAR(255) NOT NULL
)
 

I can use the LOAD DATA command like this:

LOAD DATA INFILE 'backup.txt'
INTO TABLE something
FIELDS TERMINATED BY ','
(Var1, Var2, Var3);
 

The ID column will be populated automaticly as it is an AUTO_INCREMENT field, as will all fields that have a default value and are not included.

As for the extra commas, you could write a simple script in most programming languages to remove that. It's just a matter of replacing all ',\n' accurances with '\n'.

Edit:
The reason why you can not write NULL in an AUTO_INCREMENT field is that NULL is not treated as a value. It can't even be used in boolean expressions.
The value 'NULL' which is imported from your file is treated as a string value, and can therefore not be converted into an integer.

You can, however, put the number 0 into an auto increment field.
That will be converted into the INT value 0, which is treated as NULL and a new value will be generated.

So my previous data could have been formatted like this, and I could have skipped listing the columns.

0, var11, va12, var13
0, var21, var22, var23
0, var31, var32, var33
 

[Atli]

P.S.
MySQL's "SELECT * INTO OUTFILE" statement does not add the extra comma to the end of the line.
If your application is adding extra delimiters you might want to contact who ever makes it and ask for a fix.

From where are you extracting this info btw?

[Atli]

Here is an idea, how you can fix the extra comma thing.

You can add a column to your table, which will take in the empty string the extra comma makes, and then delete it.

Like so:

ALTER TABLE myTbl
ADD COLUMN del VARCHAR(10) NOT NULL Default '';
 

Then add your data.

And finally remove the column

ALTER TABLE myTbl
DROP COLUMN del;