[Smeagle]

peterlaursen wrote on Oct 17 2008, 01:17 PM:

Not sure but I am afraid not. I do not think we can 'squeee' more info from the driver in this situation.

In our code we could possibly validate for this and popup some message (that will have to be coded in SQLyog) in this situation. Could you suggest such message?

“ODBC Driver delivered no rows”. I do think, there should be some kind of error message from odbc to sqlyog. Would be good to show it, too. As much info as possible. 😉

Thanks,

– Oliver

[Smeagle]

peterlaursen wrote on Oct 17 2008, 01:05 PM:

“Per default, SQLyog will produce no rn after each row when exporting fixed width text” .. You enter whatever you want for 'lines terminated by'.

It will remember next time you open.

Thanks. I noticed this. I just found it strange, that I cannot set this in ODCB Manager. But it works, nevertheless… Automatically 😉

Again, when I get this “no rows” error message it means, there is “some problem” within the odbc-driver / definition. Is it possoble to get a better error message at that point?

– Oliver

[Smeagle]

Hi,

it's exaclty the same with two exported/imported tables.

[attachment=1019:Test2.png]

So there must be a problem with my original field definition/schema.ini. The question remains: How can I debug? I double checked my definition several times and can find no error.

And I can really import the data, now. I overlooked I hat to remove “.txt” from the target table. “.txt” in a table name seems not to be valid? But no problem, this works now.

– Oliver

[Smeagle]

I just checked like you suggested.

Build a very easy table. Exported as fixed width. Defined source. Imported.

It's working better:

[attachment=1018:Test.png]

I now can select the table, see the defined fields and can (try to) start to import. But I still have a problem:

Code:

ERROR: 1103, Incorrect table name 'testimport.txt'

Table:testimport.txt

Sql:select * from `testimport.txt` where 1=0

ERROR: 1103, Incorrect table name 'testimport.txt'

Table:testimport.txt

Sql:DROP TABLE IF EXISTS `test`.`testimport.txt`

Not sure why.

But one thing I noticed is odd: Per default, SQLyog will produce no rn after each row when exporting fixed width text. Will this work? I don't think so. I tried the variants “r” , “rn” and “” (=empty). I get the same error every time.

What have you used for you test?

My original files have “rn”. And I see no way in the ODBC Manager to define/change, how rows are separated… Will it “autodetect”? No Idea how this works.

My next test will be, if it still works the same if there a 2 tables defined in ODBC Manager.

– Oliver

[Smeagle]

peterlaursen wrote on Oct 17 2008, 09:35 AM:

I also wonder if the images you copied into the folder will be a problem? At least a '.dat' filter should be defined.

The images are not in my data-folder. I included them in the archive for your information on the structure of the dat files.

– Oliver

[Smeagle]

peterlaursen wrote on Oct 16 2008, 09:29 PM:

You are the first user I remember ever asking questions about fixed width CSV files in the 4+ year time we had a Migration Tool in SQLyog. On the opposite we had 200+ questions related to variable width CSV files, I guess. This is no excuse, because as we claim to support both we should also reply better.

But in MySQL-context it seems practically nobody uses fixed width files. I think it is a 'speciality' invented by Microsoft!

Did you ever have to work with those big, old “Professional Enterpise” software packages? It's a real pain, as you have to deal with what you get, there is no way to work outside the standardized interfaces.

Two examples I have to deal with regularly: SAP and Docware. Both of witch really big players in the industry. And most of their export data is in fixed width format. Both of them are slowly shifting to XML now, but this will still take some time until it's usable.

This has really *nothing* to do with Microsoft. Only the “text files-odbc filter” is provided by Microsoft.

And btw: There is no “Fixed width CSV”. CSV is ALWAYS variable width 😉 as in “Character separated values”. See http://de.wikipedia.org/wiki/CSV.

peterlaursen wrote on Oct 16 2008, 09:29 PM:

2)

I can import a fixed width file with Migration Tool. Try to repeat like this

1: export a small table as fixed width CSV with SQLyog

2: copy that file into an empty folder

this I have not tried, will do.

peterlaursen wrote on Oct 16 2008, 09:29 PM:

3) With ODBC-manager create a DNS with the text-driver pointing to that folder

4) Now select the newly created DNS .. click configure .. define format

5) The 'define format' dialogue opens (the rightmost dialogue in my screensshot). One by one define the type and length of colums and 'add' until every column has been defined.

6) Now close 3 times .. you will exit the ODBC manager.

7) Note that you will see that the driver created a schema.ini file in the folder with column definitions as you defined them in the dialogue.

8) Start migration tool with this DNS

9) It migrates for me without problems (only some specific datatypes may require additional mapping at the MySQL side as ODBC and MySQL datatypes are not fully compliant. But this you can do from the migration wizard)

This is exactly how I did it. I told you, the schema.ini file is automatically created in the process. Only differenc is, I have several files in the data directory and therfore my schema.ini contains definitions for many tables. I will try if it maybe works for only one table but not for several tables?

peterlaursen wrote on Oct 16 2008, 09:29 PM:

As I understand your example you have 2 data files and a single schema.ini. Is that valid?

That's what ODBC Manager created after I defined all the columns for the test-files I used.

peterlaursen wrote on Oct 16 2008, 09:29 PM:

Also the definition in your schema.ini does not seem to 'fit' the data file(s) (schema.ini has much more columns as I read it). I still do not understand how your schema.ini was created and if it is supposed to support both of the data files or only one. Maybe you can help with explaining this if you step through the example above?

Look a little harder. You will see, the files the definition is for is standing there in square brackets. f.e. [231.dat] following all the fields for the file “231.dat” and so on.

OK, I'll try with a single data file to check if that's the problem and get back to you. But that would not be a very good solution, as I have >100 *.dat files to import in one batch…

Could you please tell me: what happens, If you build in an “error” in you definitions? Maybe define a field too large for the data? Or import a text-field as numerical data. Will there be a meaningful error message? Or will you just get no data? I'm wondering if my definitiion is “wrong”. Maybe datatypes are a problem, can I import 9 characters as a numerical field f.e.?

Thanks,

– Oliver

[Smeagle]

Is it possible to import Excel files into mySQL via SQLyog? This would use ODBC, too. But will it work?

Or has SQLyog 7 better tools for import?

Thanks,

– Oliver

[Smeagle]

Mahesh wrote on Oct 14 2008, 01:34 PM:

HI,

Can you please try to migrate in MS excel ?Is data getting migrated properly ?

Yes, import into Excel is working without problem.

I there any chance for a solution for SQLyog? Or do I need to look for another way?

Thanks,

– Oliver

[Smeagle]

peterlaursen wrote on Oct 10 2008, 01:31 PM:

OK .. now I understand the *ODBC designer'.

Great. I'm sorry if my “bad wording” caused missunderstanding… English is not my nativ language… 😉

peterlaursen wrote on Oct 10 2008, 01:31 PM:

But it seems we do not support the reading of the table schema from this format (fixed width with structure in schema.ini). We will check this in detail. And also I found a problem when migrating a fixed width CSV generated with SQLog CSV-export – so also this should be checked!

Good. But it may come to late for my project, i need this finished till end of october…

peterlaursen wrote on Oct 10 2008, 01:31 PM:

You do no have the option to either

* connect directly with ODBC to the data source without using temporary/text files? (that is best option)

This will not work.

a) the data ist processed and compressed while been exported. And I need just this export-version, not access to the original version

😎 I even don't have physical or remote access to the database server / original database. I just get the export files.

peterlaursen wrote on Oct 10 2008, 01:31 PM:

* or generate data in a single variable lenght file with a header containing the column names? (but admittedly you will then have to do the mapping manually in the wizard first time)

I don't understand, how this will make a difference, but I will try and come back to you.

Thanks,

– Oliver

[Smeagle]

peterlaursen wrote on Oct 9 2008, 12:09 PM:

1)

This “What I call “ODBC Designer” is the tool which pops up if I define a new ODBC-Source.” I still do not understand. Nothing like this pops up on my system! See attached!

[attachment=1006:odbcconfig.jpg]

I think this is something non-standard installed with a specific application on your system? Please explain this! Attach a screenshot of this 'popup' please!

I use the “Microsoft Text Driver (*.txt; *.csv)” which is even visible in your screen shot. It's the ODBC Data-Manager in which you define the odbc-source. Part of this is the automatic creation of the Schema.ini.

No offense meant, but if you don't know ODBC, I don't think you can help me? I work with ODBC quiet often, I just cannot get this “Microsoft Text Driver” to work in conjunction with SQLyog. I just see the tables but SQLyog keeps telling me, they are empty. That's why I aksed if there is another way.

peterlaursen wrote on Oct 9 2008, 12:09 PM:

2)

Please provide a valid archive (with no images etc. inside). If you cannot expose these data/schema you will have to create a 'dummy' example. It will have to be complete and valid. Tell in detail how you are able to import it to any server with any kind of application (attach screenshots if required).

Sorry, I don't understand your problem. I gave you part of the actual database files (231.dat, 232.dat). I just shortened it, as I see no use to test an import with >500MB of Data.

I gave you the complete structure – but only gave you a screenshot of the documentation for the database, as I cannot give out the complete documentation (>1000pages). So I just gave you the interesting part, the structure of the database, as a screenshot.

I can import it in Excel with the same ODBC Driver without problem. I can import it into MSSQL with the built in Import-Tool of MS SQL Manager without problem. I just want to know, how/if I can use SQLyog to import to mySQL.

peterlaursen wrote on Oct 9 2008, 12:09 PM:

3)

Also explain in broad terms what is your 'project'. You want to import data to MySQL of course. But from where do those data come? Likely there is a much easier option than what you are trying now!

No problem. I am building a Web-App which shows data from a big ERP System. The ERP System has a “Export” Funktion which preprocesses the database and produces a somewhat compressed version in a lot of “*.dat” files – two of which I included in the Archive. I have complete documentation about the structure of these “*.dat” files. I simply want to have this data in mySQL to be able to process and display it with PHP on the website.

“fixed length text” is a very basic, easy format normally. I can't get it to work in cunjunction with SQLyog and just wanted to know, if it is my fault or if it is not possible with SQLyog.

Thank you,

– Oliver

[Smeagle]

peterlaursen wrote on Oct 8 2008, 10:32 AM:

…and the structure-dump of the table you import to!

Ah, forgot about that:

I just want to import the complete table as a new table, si I have no preset structure. From my experience with importing from MSSQL-ODBC-Sources this _should_ work, right?

Thanks,

– Oliver

[Smeagle]

Hi,

OK, I made a small package with 2 (shortened) files as examples.

*.dat : Data-Files

*.png : Field descriptions of Data-Files (screenshots, sorry I cannot give away the complete documentation)

schema.ini : description created by odbc-manager

I am using SQLyog 6.56

Thank you,

– Oliver

P.S.: Hmm, I cannot upload my .rar file!? I get “Upload failed. You are not permitted to upload this type of file”. Which archiver is permitted? Or do I have to upload all the single files?

P.P.S.: .zip is working.

[Smeagle]

Hi,

I'm not in the office today, will do so tomorrow!

Thanks,

– Oliver

[Smeagle]

Hi,

Thanks for the input.

The files are fixed width (Format=FixedLength) – so there is no delimiter but i give fixed lengths for every field (f.e.: Col1=RESERVIERT Char Width 22).

What I call “ODBC Designer” is the tool witch pops up if I define a new ODBC-Source. It's simple and lets me define everything just as needed but has no way to test the definition.. But then the odbc is not dfelivering any rows and I have no idea what's wrong.

Its the first time I try to use ODBC on text-files, so I may have done something wrong, but I see no way to debug this… I often used ODBC with MSSQL sources, no problem. But this text-file driver will not work for me.

Is there another way to do this? I just need a way to import a database in a textfile (fixed widsth) into mysql. And I have to do it every month, so it should invole not to many steps if possible 😉 I was thinking about importing it to MSSQL and after that into mysql from there – that would work, but I don't like to have to use that many steps (the database is quite big and has many tables)

Thanks again,

– Oliver

[Smeagle]

I used 3.71 on the same machine and the same configuration. That was working much faster. I use the ODBC-Drivers that came with MSSQL 2005. I could try reinstalling 3.71 on that machine, but I would prefer to do everything in one version. I looked at the ODBC-Connection and noticed, it uses TCP/IP. I think named pipes are faster (but I usesed TCP/IP with 3.71, too)? I'll test it with named pipes and turned off index-import and get back to you.

Thanks for the info for “import index” option. I would still like to set “defaults” for all the options so I don't have to go through all tables.

– Oliver

[Author]

Posts