Importing from Excel

[soccerbum]

Hello,

This looks like a really great tool! Once I get past this import, I look forward to making big messes! 🙄

While importing from Excel 2000, I was getting errors parsing fields/columns with spaces in the title. I went ahead and modified the source to eliminate that, and now I'm getting an error that I can't figure out. I'm quite new at MySQL so this could be a very simple error.

“[Microsoft] [ODBC Excel Driver] Syntax Error in Query. Incomplete Query Clause”

The statement it's trying to execute is:

create table `assets`.`engineering` (

`Device` varchar (255) NULL ,

`Make` varchar (255) NULL ,

`Model` varchar (255) NULL ,

`Includes` varchar (255) NULL ,

`Serial` varchar (255) NULL ,

`DatePurchased` varchar (255) NULL ,

`Vendor` varchar (255) NULL ,

`Cost` numeric (19,4) NULL ,

`Invoice ` double (15,0) NULL ,

`Location` varchar (255) NULL

);

The MySQL server version is:

mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686)

What am I doing wrong? (btw, I edited the above statement to change the table name and to ignore the final field in the excel database. Hopefully that doesn't mess anything up.)

Any help appreciated!

Cheers,

Chris

[Shadow]

Are you sure that this is the sql statement that causes the error? It seems OK to me. Perhaps you could try to add a primary ket to the table manually.

[soccerbum]

Modifying my 'Edit MySQL' input with this:

create table `assets`.`engineering` (

`id` int (4) AUTO_INCREMENT,

`Device` varchar (255) NULL ,

`Make` varchar (255) NULL ,

`Model` varchar (255) NULL ,

`Includes` varchar (255) NULL ,

`Serial` varchar (255) NULL ,

`DatePurchased` varchar (255) NULL ,

`Vendor` varchar (255) NULL ,

`Cost` numeric (19,4) NULL ,

`Invoice ` double (15,0) NULL ,

`Location` varchar (255) NULL ,

`F11` varchar (255) NULL,

PRIMARY KEY (id)

);

I still get the same error. 🙁

Thanks for the suggestion though. I forgot about the primary key and would have had to enter it later had you not spotted it.

Oh! I had forgotten to add that when I hit the 'Run Immediately' button, I get a dialog box about losing connection to the database. However, every time I refresh anything it connects back up with no problem, so I was under the impression it's connecting (again) fine and wasn't part of the problem here. I just mention it here for thoroughness.

Anything else?

Cheers,

Chris

[vickle]

😡

Hey, I got the same problem here, but my case is with Access.

When I import a table from a odbc dns which links to an access mdb file, and some fields of the table have spaces in their names, the error occurs, exactly the same as mentioned above.

The error persists until I manually changed those those field names eliminating all the spaces therein.

Since both MySql and Access support field names with spaces, I think it is a bug in SQLyog.

Any suggest?

[Shadow]

Interesting, it works fine for me, at least from Access (MS Access 2000). But I cannot create any connection to Excel…

[peterlaursen]

There's a workaround …

Use Access as a go-in-between. From Access create new empty database and then open the .XLS-file. Then export from ACCESS to Mysql using ODBC. I don't know if you are doing right, but ODBC and EXCEL …

[Shadow]

On Win98 I can connect to Excel via ODBC without any problem, but I doesn't work on Win2K. Interesting. It seemst to be an ODBC issue to me.

[soccerbum]

Ok, I'll see if I can wrangle a copy of Access from somewhere and try that route.

If it's an ODBC thing, does that mean I'm SOL?

Is this important enough to get some personal verification from the SQLYog authors? What do ya think?

Thanks for the ideas! 😀

Chris

[peterlaursen]

I didn't try that myself, but OpenOffice ver 1.1 (now in beta) should have some database interface supporting MySQL. It might be a better solution than Access ..

[soccerbum]

Access 2000 was able to take the Excel 2000 files and import them without much of a problem.

From there, SQLyog was able to import the Access DB. My only problem now (being a SQL newbie) is taking the ID column I'd made for each table (workbook) in the spreadsheet and getting those IDs (int (4)) unique to be used as the primary key. Each workbook has 1-25, but of course if I'm putting them all as tables under a database (called, say, assets), they have to be unique. SQLyog pops up warnings for every single record, so I think my solution will be to make those columns a non-primary key before I import them into SQL, then use SQL to make the ID column in each table Primary (hopefully renumbering them for me in the process).

Which is a different problem and in my opinion a step forward! 😆

If the Excel ODBC worked properly would I need to fret over the similar ID column values? My brain locks up at that point. 😮

In any case, thanks for the Access workaround (and the OpenOffice one too!). I can stumble onward now…

Chris

[peterlaursen]

try —

export from Access (to MysQL) instead of import to MySQL (from Access).

U understand ??

Perform the Operation from the Acces user-interface …

Besides I've been looking at OpenOffice 1.1 brightly. It seems very promising using JDBC (gives more opportunities it seems than ODBC). It's all a little bit different than M$-products – and there is no installation program for the JDBC but it's really quite simple …

You should be able to open the .xls-file in OpenOffice Spreadsheet and run an update-query (You'll have to type it – there's only GUI for generating Select-queries). I'll try that out one day myself…

[Author]

Posts