My First Csv Migration

[zimmer101]

hi,

can import my csv file to an exsitng table fine if i 'import data from csv'

now i want to automate this import so that it runs every night.

i understand i have to use the migration toolkit but i get errors when i try and run it

please help

so,

1) i start a new migration session and choose my destination database

2) create a new system/user dsn

3) choose file data source and MS text driver and choose the save location

4) choose Finish and OK the ODBC text setup box

5) the data source screen has the name of the created dsn in the box – click next

6) choose 'copy table from data source' – click next

7) put a tick in the row which shows the source as data.csv

8) choose the destination table 'STOCK' from the dropdown

9) my csv has NO column headings so i think i have to map them?????

so i put the column names from the table in the destination boxes – all 50 of them!

10) in the source box it shows the data from the first row of the csv – is this correct????

11) In advanced i choose 'drop and recreate' because i want the table emptied of any

exisitng data

12) then click next – takes you to the 'on error' screen – click next

13) now comes the bit that runs/schedules the migration i choose run immediately and click next

now i get the following error

SQLyog Job Agent Version 5.16

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Job started at Thu Jun 14 12:04:56 2007

DBMS Information: TEXT

ERROR in SQL:

ERROR: [Microsoft][ODBC Driver Manager] Invalid string or buffer length

Check C:Program FilesSQLyog Enterprisesja.log for complete error details.

ERROR: Import aborted…

Check C:Program FilesSQLyog Enterprisesja.log for complete error details.

Total time taken – 1 sec(s)

snippet from sja.log

ERROR: [Microsoft][ODBC Driver Manager] Invalid string or buffer length

ERROR: Import aborted…Job started at Thu Jun 14 12:04:56 2007

ERROR in SQL:

ERROR: [Microsoft][ODBC Driver Manager] Invalid string or buffer length

ERROR: Import aborted…

[peterlaursen]

What you are doing is basically correct.

I think the issue is that the MySQL version you are using will not allow for ” . ” in table names (like “data.csv”).  Only the most recent versions do.  Try change from “data.csv” to “data”.

If not solved with this then please attach a sample .csv file to reproduce with!

[zimmer101]

hi – thanks for your reply

however if i rename my file data.csv to data

then the dsn driver will not recognize/find the file?

cant attach the file on this forum as it says 'upload failed – you are not permitted to upload this type of file'

however the file does import straight into my table if i do the import manually

[peterlaursen]

You shall not rename the file. The driver only recognizes certain file types! 

From inside the Migration Wizard you 'map' the source 'data.csv' to target 'data'

[zimmer101]

sorry – cant see how i map data.csv to data

can you please give me more indepth instruction?

is it in the select table screen

thanks peter

[zimmer101]

…another thing should the handler be innoDB or CSV?

by the way been using sqlyog for a few years – without it i would

have struggled – great app!

[zimmer101]

the destination is a table called stock

[peterlaursen]

will the attached picture help?

source is named 'book1.csv' .  I am importing to a database where the table 'stock' already exists, and it is available in the dropdown.

'table handler' tells the table type/engine to be used in MySQL.  With existing tables it displays the type of the existing table.'

If you want to add data to an existing table be careful to select that in the 'advanced' option!

[zimmer101]

hi my screen looks the same as yours! [apart from the csv file name]

[peterlaursen]

and you still get the error?

SQLyog Program version?? (please update if possible!)

MySQL version ??

Can you attach a .csv file with sample data to reproduce with?

[zimmer101]

hi peter,

sqlyog enterprise v5.16

mysql server 4.1

odbc connector 3.51

cant attach csv file so heres a few rows

[codebox]”AETV11439786″,”AETA31630″,”DA55OTW”,”10/1/2006″,”2006″,”55″,”VAUXHALL”,”CORSA”,”1.2i 16V SXi [80]”,”3″,”Hatchback”,”1229″,”Petrol”,”Manual”,”17693″,”Flame red”,”Insurance Group 3, ABS, Alloy Wheels, Audio Remote Control, Body Coloured Bumpers, Drivers Airbag, Electric Door Mirrors, Folding Rear Seats, Front Electric Windows, Front Fog Lights, Heated Door Mirrors, Height Adjustable Drivers Seat, Immobiliser, Passenger Airbag, Power Steering, Radio/CD, Remote Central Locking, Service Indicator, Sports Seats”,”7995″,””,”0″,”0″,”28929″,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

“AETV11884481″,”AETA31630″,”HD05LSN”,”28/7/2005″,”2005″,”05″,”VAUXHALL”,”ZAFIRA”,”1.6i Club”,”5″,”MPV”,”1598″,”Petrol”,”Manual”,”14625″,”Silver lightning”,”Insurance Group 5, ABS, Alloy Wheels, Audio Remote Control, Body Coloured Bumpers, Drivers Airbag, Electric Door Mirrors, Folding Rear Seats, Front Electric Windows, Heated Door Mirrors, Height Adjustable Drivers Seat, Immobiliser, Passenger Airbag, Power Steering, Radio/CD, Rear Electric Windows, Remote Central Locking, Roof Rails, Service Indicator, Side Airbags, Steering Wheel Rake Adjustment, Steering Wheel Reach Adjustment, Trip Computer”,”9495″,””,”0″,”0″,”30676″,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV11884481_1a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV11884481_1c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV11884481_1b.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV11884481_2a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV11884481_2c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV11884481_2b.jpg”,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

“AETV14307102″,”AETA31630″,”KS54BWB”,”14/12/2004″,”2004″,”54″,”VAUXHALL”,”VECTRA”,”3.2i V6 Elite Auto”,”5″,”Estate”,”3175″,”Petrol”,”Auto”,”39000″,”Oyster”,”Insurance Group 14, 2 Registered Keepers, ABS, Alarm, Alloy Wheels, Audio Remote Control, Body Coloured Bumpers, Climate Control, Cruise Control, Drivers Airbag, Electric Door Mirrors, Folding Rear Seats, Front Electric Windows, Front Fog Lights, Heated Door Mirrors, Heated Front Seat, Height Adjustable Drivers Seat, Immobiliser, Leather Seats, Passenger Airbag, Power Steering, Radio/CD Multichanger, Remote Central Locking, Service Indicator, Side Airbags, Steering Wheel Rake Adjustment, Steering Wheel Reach Adjustment, Trip Computer”,”9995″,””,”0″,”0″,”26308″,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_1a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_1c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_1b.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_2a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_2c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_2b.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_3a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_3c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14307102_3b.jpg”,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

“AETV14450939″,”AETA31630″,”HD06OJM”,”31/7/2006″,”2006″,”06″,”VAUXHALL”,”CORSA”,”1.0i 12V Active”,”3″,”Hatchback”,”998″,”Petrol”,”Manual”,”4500″,”SILVER”,”Insurance Group 3, ABS, Air Conditioning, Alloy Wheels, Audio Remote Control, Body Coloured Bumpers, Drivers Airbag, Folding Rear Seats, Front Electric Windows, Height Adjustable Drivers Seat, Immobiliser, Power Steering, Radio/CD, Remote Central Locking, Service Indicator”,”6295″,””,”0″,”0″,”26109″,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_1a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_1c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_1b.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_2a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_2c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_2b.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_3a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_3c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV14450939_3b.jpg”,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

“AETV15298209″,”AETA31630″,”SO55VXF”,”20/1/2006″,”2006″,”55″,”VAUXHALL”,”ASTRA”,”1.6i 16V Club”,”5″,”Hatchback”,”1598″,”Petrol”,”Manual”,”12124″,”BLUE”,”Insurance Group 6, 1 Registered Keeper, ABS, Air Conditioning, Alloy Wheels, Audio Remote Control, Drivers Airbag, Electric Door Mirrors, Folding Rear Seats, Front Electric Windows, Heated Door Mirrors, Height Adjustable Drivers Seat, Immobiliser, Passenger Airbag, Power Steering, Radio/CD, Remote Central Locking, Service Indicator, Side Airbags, Steering Wheel Rake Adjustment, Steering Wheel Reach Adjustment, Trip Computer”,”9495″,””,”0″,”0″,”27113″,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV15298209_1a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV15298209_1c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV15298209_1b.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV15298209_2a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV15298209_2c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV15298209_2b.jpg”,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

“AETV16035557″,”AETA31630″,”HJ56XGT”,”11/9/2006″,”2006″,”56″,”VAUXHALL”,”ASTRA”,”1.4i 16V SXi”,”3″,”Hatchback”,”1364″,”Petrol”,”Manual”,”6989″,”Panocotta”,”Insurance Group 5, ABS, Air Conditioning, Alloy Wheels, Drivers Airbag, Electric Door Mirrors, Folding Rear Seats, Front Electric Windows, Front Fog Lights, Heated Door Mirrors, Height Adjustable Drivers Seat, Immobiliser, Passenger Airbag, Power Steering, Radio/CD, Remote Central Locking, Service Indicator, Side Airbags, Sports Seats, Steering Wheel Rake Adjustment, Steering Wheel Reach Adjustment, Trip Computer”,”10995″,””,”0″,”0″,”30243″,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV16035557_1a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV16035557_1c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV16035557_1b.jpg”,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

“AETV16396200″,”AETA31630″,”HK04ZZJ”,”31/5/2004″,”2004″,”04″,”VAUXHALL”,”CORSA”,”1.0i 12V Energy”,”3″,”Hatchback”,”998″,”Petrol”,”Manual”,”23033″,”SILVER”,”Insurance Group 3, 1 Registered Keeper, ABS, Air Conditioning, Alloy Wheels, Audio Remote Control, Body Coloured Bumpers, Drivers Airbag, Electric Door Mirrors, Folding Rear Seats, Front Electric Windows, Heated Door Mirrors, Height Adjustable Drivers Seat, Immobiliser, Power Steering, Radio/CD, Remote Central Locking, Service Indicator, Steering Wheel Rake Adjustment, Steering Wheel Reach Adjustment”,”5495″,””,”0″,”0″,”27214″,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV16396200_1a.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV16396200_1c.jpg”,”http://imageserver.autoexposure.co.uk/autoedit/AETA31630/AETV16396200_1b.jpg”,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””[/codebox]

heres the xml file aswell

[codebox]

server

root

password 3306

[default]

database

autoexp

no

CREATE TABLE `sewardcarsfast`.`stock`(

`VehicleID` varchar(255) NULL,

`AccountID` varchar(255) NULL,

`Registration` varchar(255) NULL,

`RegDate` varchar(255) NULL,

`RegYear` varchar(255) NULL,

`RegLetter` varchar(255) NULL,

`Make` varchar(255) NULL,

`Model` varchar(255) NULL,

`Variant` varchar(255) NULL,

`NoDoors` varchar(255) NULL,

`BodyStyle` varchar(255) NULL,

`EngineSize` varchar(255) NULL,

`FuelType` varchar(255) NULL,

`TransmissionType` varchar(255) NULL,

`Mileage` varchar(255) NULL,

`Colour` varchar(255) NULL,

`Description` text NULL,

`Price` varchar(255) NULL,

`PreviousPrice` varchar(255) NULL,

`PricePlusVAT` varchar(255) NULL,

`New` varchar(255) NULL,

`CapId` varchar(255) NULL,

`Photo1Small` varchar(255) NULL,

`Photo1Medium` varchar(255) NULL,

`Photo1Large` varchar(255) NULL,

`Photo2Small` varchar(255) NULL,

`Photo2Medium` varchar(255) NULL,

`Photo2Large` varchar(255) NULL,

`Photo3Small` varchar(255) NULL,

`Photo3Medium` varchar(255) NULL,

`Photo3Large` varchar(255) NULL,

`Photo4Small` varchar(255) NULL,

`Photo4Medium` varchar(255) NULL,

`Photo4Large` varchar(255) NULL,

`Photo5Small` varchar(255) NULL,

`Photo5Medium` varchar(255) NULL,

`Photo5Large` varchar(255) NULL,

`Photo6Small` varchar(255) NULL,

`Photo6Medium` varchar(255) NULL,

`Photo6Large` varchar(255) NULL,

`Photo7Small` varchar(255) NULL,

`Photo7Medium` varchar(255) NULL,

`Photo7Large` varchar(255) NULL,

`Photo8Small` varchar(255) NULL,

`Photo8Medium` varchar(255) NULL,

`Photo8Large` varchar(255) NULL,

`Photo9Small` varchar(255) NULL,

`Photo9Medium` varchar(255) NULL,

`Photo9Large` varchar(255) NULL,

`Photo10Small` varchar(255) NULL,

`Photo10Medium` varchar(255) NULL,

`Photo10Large` varchar(255) NULL

);

no

[/codebox]

[peterlaursen]

Please ..

1)

COMPLETE MySQL server version (not only 4.1 but 4.1.20 or whatever!)

2)

basically you should always upgrade SQlyog to latest version before reporting an issue

3)

And the error is now what?

[Manoj]

hi,

If you want to automate the CSV import through ODBC you need make sure that the csv file contains the column names as the first row. The Odbc driver expects the first row as the fieldnames. So your first data row is mapped as column name here.

[peterlaursen]

thanks manoj .. :huh:

[peterlaursen]

@zimmer

We created the table in advance by copying the CREATE statement from you jobfile.

After that data imported without any problems.

[zimmer101]

ok – thanks manoj, got the company who provides the csv to send me

one with the column names and now works fine

is this what the mapping feature is for? – it doesnt work

so, if anyone trying to use this feature without column headings in there csv file

is having probs, try with the headings

i asked for the column headings to removed because when i tried

importing csv maunally it was having probs with the headings??

thanks for your patience peter

have a good weekend

[peterlaursen]

well ..

1st issue: MySQL 4.1 does not support ” . ” in table names

2nd issue: Mapping from the GUI won't override the text-driver (with 'ordinary' drivers it does work)

good weekend for you too!

[ridwan_iut]

I have a query.

'zimmer101' wrote:

Here it deletes the table and recreates it with new data.But if i want not to delete table and update the new data,how can it be done? Of course duplicate data should not be present in the table.

[ashwin]

To update with new data(duplicate data will be replaced) and without drop & recreate table you should use the option Import Into Existing Table->Update With Source details which is under Advanced Options in Import External data. Please see attached screen-shot.

Refer Help file(Help Menu->Help(F1) for more information.

[Author]

Posts