FlowHeater - The Data Import / Export Specialist
See also the general examples available, which may answer your question: Hello World 1 / 2 / 3 , format numbers as character strings , simple If-Then-Else , complex If-Then-ElseIf , filtering and grouping data , consolidate grouped values

Changing SQLite to OleDB Adapter

  • Keith Weatherhead
  • Topic Author
  • Offline
  • New Member
  • New Member
More
4 years 5 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 or Create an account to join the conversation.

More
4 years 5 months ago - 4 years 5 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: 4 years 5 months ago by FlowHeater-Team.

Please Log in or Create an account to join the conversation.

Time to create page: 0.376 seconds

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

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

Follow us on

twitter  facebook

YouTube

 de en