Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

Changing SQLite to OleDB Adapter

  • Keith Weatherhead
  • Topic Author
  • Offline
  • User
  • User
More
5 years 7 months ago #3466 by Keith Weatherhead
Changing SQLite to OleDB Adapter was created by Keith Weatherhead
Greetings,

The included the FHD file which was originally a TEXTFILE to SQLite3 transformation...
I tried changing the WRITE Adapter to OleDB in order to create a DBF file.

I get the error in the screenshot along with there configuration screenshots.
I have tried to enter a filename on the: Fields / Data Types tab ... I have left that blank...
no matter what I try it get the error shown....

It is almost like the Change of Adapter did not work or complete?

Keith
Attachments:

Please Log in to join the conversation.

More
5 years 7 months ago - 5 years 7 months ago #3470 by FlowHeater-Team
Replied by FlowHeater-Team on topic Changing SQLite to OleDB Adapter
Hi Keith,

I´m afraid at present only the SQLite Adapter has the possibility to create not exiting tables before importing. The OleDB or all other database Adapters are currently not able to create tables or add some missing fields.

Note: When changing the type of Adapter only general information such as field names / types and database connection settings etc. as applicable are retained, any settings that specifically apply to an Adapter type are lost.

However, you can use a little workaround :-)

It would be possible to create the desired dBASE table (uisng the OleDB Adapter ) with the help of the .NET Script Heater and a little C# script like below.

The Script checks whether the dBASE data file exist for the table (e.g. C:\Temp\dBase_test.dbf) and if not build a dynamically “SQL CREATE TABLE” statement with the fields information from the present definition. (see my remark in the script below)

You have just align two configuration settings. First in the script you have to define the path were your dBase DBF database files are located. Second, you have to enter in den WRITE Adapter properties the desired dBASE table name. (see screenshot below)

I´ve modified your definition with the script, I guess it should work for you. (see attachment)

C# script to create dynamically dBASE tables befor importing
Code:
// modify here the path were your dBASE files located string dbfiles = @"D:\CLC_Test"; public object DoWork() { // accessing the WRITE Adapter OleDBAdapter adapter = (OleDBAdapter)AdapterWrite; // optain the table name string tablename = adapter.SQL; if (File.Exists(Path.Combine(dbfiles, tablename + ".dbf"))) { // if the dBASE table file already exist no further action! return null; } // build the create table statement string sql = "create table [" + tablename + "] ("; int len = 0; int count = 0; foreach(Field f in adapter.Fields) { if (count > 0) sql += ", "; DatabaseAdapterField field = (DatabaseAdapterField)f; sql += "[" + field.Name + "] "; switch(field.DataType) { case DataType.String: len = field.Length; if (len == 0) len = 100; //SQL Datentyp for strings sql += "varchar(" + len.ToString() + ")"; break; case DataType.Date: case DataType.Time: case DataType.DateTime: //SQL Datentyp for datetime sql += "datetime"; break; case DataType.Integer: case DataType.Boolean: //SQL Datentyp for Int (integer) sql += "Integer"; break; case DataType.Double: case DataType.Decimal: case DataType.Currency: //SQL Datentyp für Double and Currency perhaps use numeric(18,2) ... sql += "Float"; break; } if (field.PrimaryKey) sql += "not null "; count++; } sql += " );"; // execute the SQL statement and create the table adapter.Execute(sql); // result not needed! return null; }



File Attachment:

File Name: ar_atb_jun...-dbf.zip
File Size:6 KB

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Attachments:
Last edit: 5 years 7 months ago by FlowHeater-Team.

Please Log in to join the conversation.

Time to create page: 0.302 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.