data import always fails

5 posts / 0 new
Last post
deano
deano's picture
Offline
Last seen: 1 year 2 months ago
Joined: Sep 22 2011
Junior Boarder

Posts: 15

Dean Kamali
data import always fails

Hello everyone

I'm having a huge problem with importing data to my infinidb db

[code]/usr/local/Calpont/bin/cpimport my_prod_db table file.txt -s ',[/code]'

I keep getting

[code]Line number 2; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 3; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 4; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 5; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 6; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 7; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 8; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33
Line number 9; Error: Data contains wrong number of columns; num fields expected-36; num fields found-33[/code]

Please help!!

radams
radams's picture
Offline
Last seen: 13 hours 38 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: data import always fails

Hi,

The problem is the column count. The command that you are using indicates that you are using commas as the delimiter. You indicated that the file.txt was using a '|' ???

1 | 2 | 3334433 | 4564332322 | | 345543322

You can post a few lines of your file.txt if you want us to count the columns.

Thanks,

Robert
Calpont

deano
deano's picture
Offline
Last seen: 1 year 2 months ago
Joined: Sep 22 2011
Junior Boarder

Posts: 15

Dean Kamali
Re: data import always fails

I have 2 files with with commas and the other with pipes

here is an example...

[code]688294021|1331517605227|0|1331517614719|0|7182857273|7186524444|7183163012|1999669|718285727300|B|0|0|KeyAd|1101
688294042|1331517605657|1331517605657|1331517616480|0|3148778442||3146535550|0|314877844200|H|1|10|OnHold|2501[/code]

its true that the fields are less than 33 but it used to work fine on my infinidb database before I had to rebuild it.

as long as the data doesn't exceed schema fields it should's complain about it.

is there is a way to ignore errors? I'm really confiused on why I haven't seen such issue before, I had a shell script which runs every night, takes a dump or prod and archive it to infinifdb.

what I'm doing right now is an extra step

Quote: awk -F'|' -v OFS='|' '{for(i=NF+1;i<=36;i++)$i=""}1' file > file.csv

this way the awk script will count the fields and add extra if required so that that cpimport stop complaining about errors, but I really don't like that! but I guess I have to use it this way for now ....

deano
deano's picture
Offline
Last seen: 1 year 2 months ago
Joined: Sep 22 2011
Junior Boarder

Posts: 15

Dean Kamali
Re: data import always fails

Please guys ... can someone help me here ...

its true that my work around works, however mysqld by default doesn't complain if your row has values with all fields or not, it assumes if not value NULL should be assigned automatically.
when i do show create table against my table, I see that NULL Assigned default on all column.

doing awk or sed takes a very long time when you run it against 30GB csv file.

It wasn't doing that in older versions, its bad that i have upgraded it.

radams
radams's picture
Offline
Last seen: 13 hours 38 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: data import always fails

Hi,

You can use data mapping and create a job file that will either ignore fields or set up default columns.
Please review the Admin Guide section on Advanced Data Mapping.

http://infinidb.org/downloads/cat_view/39-documentation

Thanks,

Robert
Calpont